Three ways to manipulate MySQL database in Python

This article mainly introduces three kinds of Python operation MySQL database method, small series feel very good, now share to everyone, also give you a reference. Let's take a look at it with a little knitting.

1. Use of MYSQLDB

(1) What is mysqldb?

  MySQLdb is the interface for python to connect mysql database, it implements the Python database API specification V2.0, based on the MySQL C API Built on the.

(2) Source code installation MySQLdb: Https://pypi.python.org/pypi/MySQL-python

$ tar zxvf mysql-python-*.tar.gz$ cd mysql-python-*$ python setup.py build$ python setup.py install

(3) Use of mysqldb :

#!/usr/bin/env python# coding=utf-8import mysqldbdef connectdb (): Print (' Connect to MySQL server ... ') # Open database Connection # user name: HP, Password: Hp12345., username and password need to be changed to your own MySQL user name and password, and to create database TestDB, and create a good table in TestDB database student db = MySQLdb.connect ("localhost", "HP        "," Hp12345. "," TESTDB ") print (' Connected! ') return db def createtable (db): # Use the cursor () method to get the cursor = Db.cursor () # If the table sutdent is present, delete the cursor first. Execute ("DROP TABLE IF EXISTS Student") sql = "" "CREATE TABLE Student (ID CHAR () not NULL, Nam     e CHAR (8), Grade INT) "" "# Create Sutdent table Cursor.execute (SQL) def insertdb (db): # Get an action cursor using the cursor () method                cursor = db.cursor () # SQL INSERT statement sql = "" "INSERT into Student VALUES (' 001 ', ' czq ', 70),  (' 002 ', ' LHQ ', '), (' 003 ', ' MQ ', '), (' 004 ', ' WH ', '), (' 005 ', ' HP ', (' 006 ', ' YF ', "), (' 007 ', ' TEST ', +) '" "#sql =" INSERT into STudent (ID, Name, Grade) \ # VALUES ('%s ', '%s ', '%d ') '% \ # (' 001 ', ' HP ', #) Try: # Execute SQL statement Cursor.execute (SQL) # Commit to Database Execution Db.commit () except: # Rollback in case there was any error p        Rint ' inserting data failed! ' Db.rollback () def querydb (db): # Use the cursor () method to get the cursor cursor = db.cursor () # SQL query Statement #sql = "SELECT * FROM Student \ # WHERE Grade > '%d '% (sql = "SELECT * from Student" Try: # Execute SQL statement C            Ursor.execute (SQL) # gets all the list of records results = Cursor.fetchall () for row in results: id = row[0] Name = row[1] Grade = row[2] # print result "ID:%s, Name:%s, G Rade:%d "% \ (ID, Name, Grade) except:print" error:unable to FECTH data "Def deletedb ( db): # using the cursor () method to get an operation cursor = Db.cursor () # SQL DELETE statement sql = "Delete from Student WHERE Grade = '%d '"% (1 00Try: # Execute SQL statement cursor.execute (SQL) # Commit modify Db.commit () Except:print ' delete data failed! '  # rollback Db.rollback () def updatedb (db): # Use the cursor () method to get an operation cursor = Db.cursor () # SQL UPDATE statement sql =        "UPDATE Student SET Grade = Grade + 3 WHERE ID = '%s '"% (' 003 ') Try: # Execute SQL statement cursor.execute (SQL)        # Commit to Database Execution Db.commit () except:print ' Update data failed! ' # Rollback On Error Db.rollback () def closedb (db): Db.close () def main (): db = Connectdb () # Connect MySQL database createtable (DB) # CREATE TABLE Insertdb (db) # Insert data print ' \ n after inserting data: ' Querydb (db) Deletedb (db) # Delete data print ' \ n After deleting data: ' Querydb (db) UpdateDB (db) # Update data print ' \ n Update data: ' Querydb (db) Closedb (DB) # Close database if __name__ = = ' __main__ ': Main ()

Operation Result:

2. Use of Pymysql

(1) What is pymysql?

  Pymysql is a library used in python to connect to the MySQL server, which follows the Python database API specification V2.0 and contains Pure-python M Ysql Client library.

(2) Installation Pymysql:

Pip Install Pymysql

(3) using pymysql:

#!/usr/bin/env python# coding=utf-8import pymysqldef connectdb (): Print (' Connect to MySQL server ... ') # Open database Connection # user name: HP, Password: Hp12345., username and password need to be changed to your own MySQL user name and password, and to create database TestDB, and create a good table in TestDB database student db = Pymysql.connect ("localhost", "HP", "    Hp12345. "," TESTDB ") print (' Connected! ') return dbdef createtable (db): # Use the cursor () method to get the cursor cursor = db.cursor () # If there is a table sutdent first delete cursor.execute ("DRO            P table IF EXISTS Student ") sql =" "" CREATE TABLE Student (ID char () not NULL, Name char (8), Grade INT) "" "# Create Sutdent table Cursor.execute (SQL) def insertdb (db): # Get an action cursor using the cursor () method cursors = DB.C Ursor () # SQL INSERT statement sql = "" "INSERT into Student VALUES (' 001 ', ' Czq ',"), (' 002 ', ' LHQ ', 80) , (' 003 ', ' MQ ', ' + '), (' 004 ', ' WH ', '), (' 005 ', ' HP ', 70), (' 0  (' 007 ', ' TEST ', YF) ' "" #sql = "INSERT into Student (ID, Name, Grade) \ #  VALUES ('%s ', '%s ', '%d ') '% \ # (' 001 ', ' HP ', #) Try: # Execute SQL statement cursor.execute (SQL) # Commit        To database execution Db.commit () except: # Rollback in case there was any error print ' Insert data failed! ' Db.rollback () def querydb (db): # Use the cursor () method to get the cursor cursor = db.cursor () # SQL query Statement #sql = "SELECT * FROM Stud ENT \ # WHERE Grade > '%d ' "% sql =" SELECT * from Student "Try: # Execute SQL statement cursor.execut E (SQL) # get all records list results = Cursor.fetchall () for row in Results:id = Row[0] N                ame = row[1] Grade = row[2] # printing result print "ID:%s, Name:%s, Grade:%d"% \ (ID, Name, Grade) except:print "error:unable to FECTH data" Def deletedb (db): # Get an operation cursor using the cursor () method curs or = Db.cursor () # SQL DELETE statement sql = "Delete from Student WHERE Grade = '%d '"% "try: # Execute SQL statement cur     Sor.execute (SQL) # Commit Changes  Db.commit () except:print ' delete data failed! ' # rollback Db.rollback () def updatedb (db): # Use the cursor () method to get an operation cursor = Db.cursor () # SQL UPDATE statement sql = "U        PDATE Student SET Grade = Grade + 3 WHERE ID = '%s ' "% (' 003 ') Try: # Execute SQL statement cursor.execute (SQL)        # Commit to Database Execution Db.commit () except:print ' Update data failed! ' # Rollback On Error Db.rollback () def closedb (db): Db.close () def main (): db = Connectdb () # Connect MySQL database createtable (DB) # CREATE TABLE Insertdb (db) # Insert data print ' \ n after inserting data: ' Querydb (db) Deletedb (db) # Delete data print ' \ n After deleting data: ' Querydb (db) UpdateDB (db) # Update data print ' \ n Update data: ' Querydb (db) Closedb (DB) # Close database if __name__ = = ' __main__ ': Main ()

Operation Result:

3. Use of Mysql.connector

(1) What is mysql.connector?

Because the MySQL server runs in a separate process and serves the network externally, it is necessary to support the MySQL driver for Python to connect to the MySQL server.

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.

(2) Installation Mysql.connector:

Pip Install Mysql-connector-pythonpip Install Mysql-python

(3) using mysql.connector:

#!/usr/bin/env python# coding=utf-8import mysql.connectordef connectdb (): Print (' Connect to MySQL server ... ') # Open database Connection # user name : HP, Password: Hp12345., username and password need to be changed to your own MySQL user name and password, and to create a database TestDB, and create a good table in TestDB database student db = Mysql.connector.connect (    User= "HP", passwd= "Hp12345", database= "TESTDB", use_unicode=true) print (' Connected! ') return dbdef createtable (db): # Use the cursor () method to get the cursor cursor = db.cursor () # If there is a table sutdent first delete cursor.execute ("DRO            P table IF EXISTS Student ") sql =" "" CREATE TABLE Student (ID char () not NULL, Name char (8), Grade INT) "" "# Create Sutdent table Cursor.execute (SQL) def insertdb (db): # Get an action cursor using the cursor () method cursors = DB.C Ursor () # SQL INSERT statement sql = "" "INSERT into Student VALUES (' 001 ', ' Czq ',"), (' 002 ', ' LHQ ', 80) , (' 003 ', ' MQ ', ' + '), (' 004 ', ' WH ', '), (' 005 ', ' HP ', 70), (' 0 (' 007 ', ' TEST ', YF), ' "" #sql = "INSERT into Student (ID, Name, Grade) \ # VALUES ('%s ', '%s ', '%d ') '% \ # (' 001 ', ' HP ', ') Try: # Row SQL statement cursor.execute (SQL) # Commit to Database Execution Db.commit () except: # Rollback in case there was any E        Rror print ' Insert data failed! ' Db.rollback () def querydb (db): # Use the cursor () method to get the cursor cursor = db.cursor () # SQL query Statement #sql = "SELECT * FROM Stud ENT \ # WHERE Grade > '%d ' "% sql =" SELECT * from Student "Try: # Execute SQL statement cursor.execut E (SQL) # get all records list results = Cursor.fetchall () for row in Results:id = Row[0] N                ame = row[1] Grade = row[2] # printing result print "ID:%s, Name:%s, Grade:%d"% \ (ID, Name, Grade) except:print "error:unable to FECTH data" Def deletedb (db): # Get an operation cursor using the cursor () method curs or = Db.cursor () # SQL DELETE statement sql = "Delete from Student WHERE Grade = '%d '"% (in) Try: # Execute SQLSentence cursor.execute (SQL) # Commit modification Db.commit () except:print ' delete data failed! ' # rollback Db.rollback () def updatedb (db): # Use the cursor () method to get an operation cursor = Db.cursor () # SQL UPDATE statement sql = "U        PDATE Student SET Grade = Grade + 3 WHERE ID = '%s ' "% (' 003 ') Try: # Execute SQL statement cursor.execute (SQL)        # Commit to Database Execution Db.commit () except:print ' Update data failed! ' # Rollback On Error Db.rollback () def closedb (db): Db.close () def main (): db = Connectdb () # Connect MySQL database createtable (DB) # CREATE TABLE Insertdb (db) # Insert data print ' \ n after inserting data: ' Querydb (db) Deletedb (db) # Delete data print ' \ n After deleting data: ' Querydb (db) UpdateDB (db) # Update data print ' \ n Update data: ' Querydb (db) Closedb (DB) # Close database if __name__ = = ' __main__ ': Main ()

Operation Result: