Python3 using MySQL connector to operate the MySQL database

Python can use a variety of methods to operate the MySQL database, such as MySQLdb, Pymysql, MySQL Connector, etc., where MySQL Connector is the official MySQL database driver, we can in the MySQL Official web site, and it is easy to use it on the website.

MySQL Connector for C, C + +, ODBC, PHP, Python and other drivers, we only need to pay attention to the Python driver, the website URL is as follows:

https://dev.mysql.com/doc/connector-python/en/

Currently, there are two MySQL drivers:

Mysql-connector-python: MySQL is the official pure Python drive;

Mysql-python: is a python driver that encapsulates the MySQL C drive.

I generally use mysql-connector-python, of course, the driver can download the installation on the official website, you can also use the PIP to install.

Pip install mysql-connector-python          pip install MySQL-python

The use of the method is also very simple, according to the usual database connection can, of course, if there are other applications, such as stored procedures, such as the wording, you can refer to the official website of the document.

In general, reading and writing to a database through Python has the following steps: Connect to a database, get a cursor, run an SQL statement, get results, close a database.

To connect to a database:

ImportMysql.connectordefconnectdb ():Print('connect to MySQL server ...')    #Open a database connection    #user name: root, Password: 123456, username and password need to be changed to your own MySQL username and password, and to create a database TestDB. db = Mysql.connector.connect (user="Root", passwd="123456.", database="TESTDB", use_unicode=True)Print('It's connected!')    returnDb

Of course, we can also use the dictionary to save the connection parameters and use the TRY......EXCEPT statement block to catch the error.

ImportMysql.connector fromMysql.connectorImportErrorCode#Connection ParametersConfig = {    'User':'Root',    'Password':'123456',    'Database':'TESTDB',    'Host':'127.0.0.1',}defconnectdb ():Try:        Print('connect MySQL database ...')        #Open a database connectiondb = Mysql.connector.connect (* *config)#back to database connection        returnDBexceptMysql.connector.Error as err:ifErr.errno = =ErrorCode. Er_access_denied_error:Print("incorrect user name or password")        elifErr.errno = =ErrorCode. Er_bad_db_error:Print("database does not exist")        Else:            Print(ERR) DB= Connectdb ()

After connecting the database, we can do the relevant increase, deletion, change, check action.

To create a table:

def createtable (db):     #     cursor = db.cursor ()    #  If Table sutdent is present, delete Cursor.execute first    ("  DROP table IF EXISTS Student")    " ""CREATE table Student ( C18/>id Char (Ten) not NULL,            Name char (8),            Grade INT)"" "    #  Create sutdent table /c24>    cursor.execute (SQL)

Insert data:

definsertdb (db):#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL INSERT statementsql ="""INSERT into Student VALUES (' 001 ', ' czq ', ' + '), (' 002 ', ' LHQ ', '), (' 003 ', ' MQ ',                (' 004 ', ' WH ', '), (' 005 ', ' HP ', '), (' 006 ', ' YF ', 66), (' 007 ', ' TEST ', +)"""    #sql = "INSERT into Student (ID, Name, Grade) \    #VALUES ('%s ', '%s ', '%d ') '% \    #(' 001 ', ' HP ', ')    Try:        #Execute SQL statementcursor.execute (SQL)#commit to database executionDb.commit ()except:        #Rollback In case there was any error        Print 'failed to insert data!'Db.rollback ()

Query data:

defquerydb (db):#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL Query Statements    #sql = "SELECT * from Student \    #WHERE Grade > '%d ' "% (a)sql ="SELECT * from Student"    Try:        #Execute SQL statementcursor.execute (SQL)#get list of all recordsResults =Cursor.fetchall () forRowinchResults:id=Row[0] Name= Row[1] Grade= Row[2]            #Print Results            Print "ID:%s, Name:%s, Grade:%d"%(ID, Name, Grade)except:        Print "error:unable to FECTH data"

Delete data:

defdeletedb (db):#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL DELETE statementsql ="DELETE from Student WHERE Grade = '%d '"% (100)    Try:       #Execute SQL statementcursor.execute (SQL)#Submit ChangesDb.commit ()except:        Print 'failed to delete data!'        #Roll Back when an error occursDb.rollback ()

Update data:

defupdatedb (db):#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL UPDATE statementsql ="UPDATE Student SET Grade = Grade + 3 WHERE ID = '%s '"% ('003')    Try:        #Execute SQL statementcursor.execute (SQL)#commit to database executionDb.commit ()except:        Print 'failed to update data!'        #Roll Back when an error occursDb.rollback ()

To close a database connection:

def closedb (db):    db.close ()

Then in main main program, you can apply the following:

defMain (): DB= Connectdb ()#connect to MySQL databasecreatetable (db)#Create a tableINSERTDB (DB)#Inserting Data    Print '\ n After inserting data:'querydb (db) deletedb (db)#Delete Data    Print '\ n After deleting data:'querydb (db) updatedb (db)#Update Data    Print 'After you update the data:'querydb (db) closedb (db)#Close the databaseif __name__=='__main__': Main ()

Python3 using MySQL connector to operate the MySQL database