MySQL Common operations summary sharing under CMD and Python

Environment Configuration 1: Install MySQL, environment variable add MySQL Bin directory

Environment Configuration 2:python Installation Mysql-python

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

WINDOWS10 64-bit operating system can be downloaded to http://www.lfd.uci.edu/~gohlke/pythonlibs/to install Mysql-python package, For WHL and tar.gz installation methods under Windows and Linux see my previous post

One, the cmd command operation:

Connecting Mysql:mysql-u root-p

View all databases: show databases;

Creating the Test Database: Create DB test;

Delete database: Drop DB test;

using (switch to) test database: Use test;

View the table under the current database: show tables;

Create UserInfo table: Creation table UserInfo (ID int (5) NOT NULL Auto_increment,username varchar (), password varchar (.) NOT NULL, PRIMARY KEY (id));

Delete tables: drop table UserInfo;

Determine if the data exists: SELECT * from UserInfo where name is like ' ELIJAHXB ';

Add 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

Second, the operation under Python:

　　

 1 #-*-Coding:utf-8-*-2 #!/usr/bin/env python 3 4 # @Time: 2017/6/4 18:11 5 # @Author: Elijah 6 # @Site:     7 # @File: sql_helper.py 8 # @Software: Pycharm Community Edition 9 Import MYSQLDB10 one class Mysqlhelper (object): 12 def __init__ (Self,**args): Self.ip = args.get ("IP") self.user = Args.get ("user") Self.pas  Sword = args.get ("Password") Self.tablename = Args.get ("Table") Self.port = 330618 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 () 23 Self.conn.close () def execute (self,sqlcmd): Return Self.cursor.execute (sqlcmd) + def setdatabase (s Elf,database): Self.cursor.execute return ("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:34 table = self.tablename35 self.cu Rsor.execute ("select * from%s;")             %table) Self.cursor.fetchone () Notoginseng def getfetchmany (self,table=none,size=0): If not table:39 Table = Self.tablename40 count = Self.cursor.execute ("select * from%s;" %table) Self.cursor.fetchmany (size)-def getfetchall (self,table=none): ":p Ara M table: List 45:return:46 "if not table:48 table = self.tablename49 SELF.C Ursor.execute ("select * from%s;")         %table) Self.cursor.fetchall () Setinsertdata (Self,table=none,keyinfo=none,value=none): 52 "" ":p Aram table:54:p Aram KeyInfo: You can not pass this parameter, but at this point the value of each data field must match the number of fields in the database. 55 When this parameter is passed, it indicates that only the field values of the specified field are worn. 56         :p Aram Value: The type must be a tuple of only one set of information, or a list of tuples that contain multiple messages 57:return:58 "" "If not table:60             Table = Self.tablename61 Slist = []62 if type (value) ==tuple:63 Valuelen = value64 Execmany = False65 else:66 Valuelen = value[0]67 Execmany = True68 for each in              Range (len (valuelen)): Slist.append ("%s") Valuecenter = ",". Join (Slist) if not keyinfo:72 sqlcmd = "INSERT into%s values (%s);"% (         Table,valuecenter) else:74 sqlcmd = "INSERT into%s%s values (%s);"% (Table,keyinfo,valuecenter) 75 Print (sqlcmd) print (value), if execmany:78 return Self.cursor.executemany (sqlcmd,val UE) ELSE:80 return Self.cursor.execute (sqlcmd, value)

mysqlhelper