Analysis of common operations of MySQL under cmd and python

This article mainly brings you a brief discussion of the common operations of MySQL under cmd and python. The editor thinks it’s pretty good, so I’ll share it with you now and give it as a reference. Let’s follow the editor to take a look, I hope it can help everyone.

Environment configuration 1: Install mysql, add the bin directory of mysql to the environment variable

Environment configuration 2: python install MySQL-Python

Please download and install according to your own operating system, otherwise C++ compile 9.0, import _mysql and other errors will be reported

windows10 64-bit operating system can go to http://www.lfd.uci.edu/~gohlke/pythonlibs/ to download and install the MySQL-Python package. As for For the installation method of whl and tar.gz under Windows and Linux, please see my previous article

1. Operation under cmd command:

Connect to mysql: mysql -u root -p

View all databases: show databases;

Create test database: create database test;

Delete database: drop database test;

Use (switch to )test database: use test;

View the tables under the current database: show tables;

Create UserInfo table: create table UserInfo(id int(5) NOT NULL auto_increment,username varchar(10) ),password varchar(20) NOT NULL,PRIMARY KEY(id));

Delete table: drop table UserInfo;

Judge whether the data exists: select * from UserInfo where name like 'elijahxb ';

Increase data: insert into UserInfo(username,password) value('eljiahxb','123456');

Check data: select * from UserInfo; select id from UserInfo; select username from UserInfo;

Change data: update UserInfo set username = 'Zus' where id=1; update UserInfo set username='Zus';

Delete data: delete from UserInfo; delete from UserInfo where id=1;

Disconnect: quit

2. Operation under python:

# -*- coding: utf-8 -*-
#!/usr/bin/env python

# @Time  : 2017/6/4 18:11
# @Author : Elijah
# @Site  : 
# @File  : sql_helper.py
# @Software: PyCharm Community Edition
import MySQLdb

class MySqlHelper(object):
  def __init__(self,**args):
    self.ip = args.get("IP")
    self.user = args.get("User")
    self.password = args.get("Password")
    self.tablename = args.get("Table")
    self.port = 3306
    self.conn = self.conn = MySQLdb.Connect(host=self.ip,user=self.user,passwd=self.password,port=self.port,connect_timeout=5,autocommit=True)
    self.cursor = self.conn.cursor()

  def Close(self):
    self.cursor.close()
    self.conn.close()
  def execute(self,sqlcmd):
    return self.cursor.execute(sqlcmd)
  def SetDatabase(self,database):
    return self.cursor.execute("use %s;"%database)
  def GetDatabasesCount(self):
    return self.cursor.execute("show databases;")
  def GetTablesCount(self):
    return self.cursor.execute("show tables;")
  def GetFetchone(self, table = None):
    if not table:
      table = self.tablename
    self.cursor.execute("select * from %s;"%table)
    return self.cursor.fetchone()
  def GetFetchmany(self,table=None,size=0):
    if not table:
      table = self.tablename
    count = self.cursor.execute("select * from %s;"%table)
    return self.cursor.fetchmany(size)
  def GetFetchall(self,table=None):
    '''
    :param table: 列表
    :return:
    '''
    if not table:
      table = self.tablename
    self.cursor.execute("select * from %s;"%table)
    return self.cursor.fetchall()
  def SetInsertdata(self,table=None,keyinfo=None,value=None):
    """
    :param table:
    :param keyinfo:可以不传此参数，但此时value每一条数据的字段数必须与数据库中的字段数一致。
            传此参数时，则表示只穿指定字段的字段值。
    :param value:类型必须为只有一组信息的元组，或者包含多条信息的元组组成的列表
    :return:
    """
    if not table:
      table = self.tablename
    slist = []
    if type(value)==tuple:
      valuelen = value
      execmany = False
    else:
      valuelen = value[0]
      execmany = True
    for each in range(len(valuelen)):
      slist.append("%s")
    valuecenter = ",".join(slist)
    if not keyinfo:
      sqlcmd = "insert into %s values(%s);"%(table,valuecenter)
    else:
      sqlcmd = "insert into %s%s values(%s);" % (table,keyinfo,valuecenter)
    print(sqlcmd)
    print(value)
    if execmany:
      return self.cursor.executemany(sqlcmd,value)
    else:
      return self.cursor.execute(sqlcmd, value)

Related recommendations:

How to use CMD to connect to the local mysql database

How to log in to mysql and how to connect to the mysql database with cmd?

How to execute cmd command in php

The above is the detailed content of Analysis of common operations of MySQL under cmd and python. For more information, please follow other related articles on the PHP Chinese website!