Common operations for MySQL 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 python3 4 #@Time: 2017/6/4 18:115 #@Author: Elijah6 #@Site:7 #@File: sql_helper.py8 #@Software: pycharm Community Edition9 ImportMySQLdbTen  One classMysqlhelper (object): A     def __init__(self,**args): -Self.ip = Args.get ("IP") -Self.user = Args.get ("User") theSelf.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 () -  +     defClose (self): A self.cursor.close () at self.conn.close () -     defExecute (self,sqlcmd): -         returnSelf.cursor.execute (sqlcmd) -     defsetdatabase (self,database): -         returnSelf.cursor.execute ("Use %s;"%database) -     defGetdatabasescount (self): in         returnSelf.cursor.execute ("show databases;") -     defGettablescount (self): to         returnSelf.cursor.execute ("show tables;") +     defGetfetchone (self, table =None): -         if  nottable: theTable =Self.tablename *Self.cursor.execute ("select * from%s;"%table) $         returnSelf.cursor.fetchone ()Panax Notoginseng     defGetfetchmany (self,table=none,size=0): -         if  nottable: theTable =Self.tablename +Count = Self.cursor.execute ("select * from%s;"%table) A         returnself.cursor.fetchmany (size) the     defGetfetchall (self,table=None): +         " " - :p Aram table: List $ : return: $         " " -         if  nottable: -Table =Self.tablename theSelf.cursor.execute ("select * from%s;"%table) -         returnSelf.cursor.fetchall ()Wuyi     defSetinsertdata (self,table=none,keyinfo=none,value=None): the         """ - :p Aram Table: Wu :p Aram KeyInfo: You can not pass this parameter, but at this point the number of fields for each data in value must match the number of fields in the database.  - When this parameter is passed, it means that only the field value of the specified field is worn.  About :p Aram Value: The type must be a tuple of only one set of information, or a list of tuples that contain multiple information $ : return: -         """ -         if  nottable: -Table =Self.tablename ASlist = [] +         ifType (value) = =Tuple: theValuelen =value -Execmany =False $         Else: theValuelen =Value[0] theExecmany =True the          foreachinchRange (len (valuelen)): theSlist.append ("%s") -Valuecenter =",". Join (slist) in         if  notKeyInfo: thesqlcmd ="insert into%s values (%s);"%(Table,valuecenter) the         Else: Aboutsqlcmd ="INSERT into%s%s values (%s);"%(Table,keyinfo,valuecenter) the         Print(sqlcmd) the         Print(value) the         ifExecmany: +             returnSelf.cursor.executemany (Sqlcmd,value) -         Else: the             returnSelf.cursor.execute (sqlcmd, value)

Mysqlhelper

Common operations for MySQL under CMD and python