Python Learning ==> operation MySQL

First, Introduction:

MySQL is a relational database, and other relational databases include Oracle, DB2, SQL Server, and so on. Python operation MySQL requires the use of the PYMSYQL module, PIP installation can be.

Second, the operation of the MySQL step

1. Connect database (IP, port number, user name, password, database name)

2. Creating Cursors

3. Execute SQL

4. Get Results

5. Close the cursor

6. Close the connection

1 ImportPymysql2conn =Pymysql.connect (3host='192.168.1.112',4User='Test',5Passwd='111111',6port=3306,#port must be of type int7db='Test',8charset='UTF8'      #CharSet must write UTF8, cannot write Utf-89 )TenSqla ='select * from Stu limit;' OneSQLB ='insert INTO Stu (id,name,sex) VALUE (10000, "Zhang Flow", "female");' Acur = conn.cursor ()#creates a cursor that does not specify a two-dimensional tuple returned by the cursor type -cur = conn.cursor (cursor=pymysql.cursors.dictcursor)#creates a cursor that specifies that the cursor type returns a dictionary -Cur.execute (Sqla)#Executive Sqla theCur.execute (SQLB)#Executive Sqlb -Conn.commit ()#Execute INSERT, DELETE, UPDATE statement must commit -res = Cur.fetchall ()#executes all returned results, Fetchall returns a two-dimensional array -res = Cur.fetchone ()#executes all returned results, Fetchone returns the first row +res = Cur.fetchmany (2)#executes all returned results, fetchmany the number of data returned -res = Cur.description#returns the information for each field in the table, and description returns a two-dimensional array + Print(RES) ACur.close ()#Close Cursors atConn.close ()#Close Connection

cursor Type:

The cursor type is not specified, i.e.: cur = conn.cursor (), the result is: ((5, ' Ben ', Male '), (6, ' Lily ', female '), is a two-dimensional tuple

Specifying the Curson type, that is: cur = conn.cursor (cursor=pymysql.cursors.dictcursor), the result is:

[{' id ': 5, ' name ': ' Ben ', ' sex ': ' Male '}, {' id ': 6, ' name ': ' Lily ', ' sex ': ' Female '}]

The difference between fetchall () and Fetchone ():

Fetchall (): Gets the full result of this SQL execution, which puts each row of data in a database table into a tuple or a dictionary

Fetchone (): Gets a result of this SQL execution, which returns just one piece of data

If the result of the SQL statement execution is multiple data, then use Fetchall (), if you can determine the results of SQL execution only one, then use Fetchone ()

Third, the function of the package operation MySQL Database

1 defmy_db (sql,port=3306,charset='UTF8'):2     ImportPymysql3Host,user,passwd,db ='192.168.1.112','Test','111111','Test'  #Defining Variables4conn = Pymysql.connect (host=Host,5User=User,6Passwd=passwd,7port=Port,8db=DB,9charset=CharSet)Tencur = conn.cursor (cursor=pymysql.cursors.dictcursor)#creates a cursor that specifies that the cursor type returns a dictionary OneCur.execute (SQL)#EXECUTE Statement A     ifSql.strip (). Split () [0].upper () = ='SELECT':#determine if the SQL statement starts with select -res =Cur.fetchall () -     Else: the Conn.commit () -res ='OK' -Cur.close ()#Close Cursors -Conn.close ()#Close Connection +     returnRes

Iv. Practice

Pass in a table name, export all data, write to Excel file

1 defExport_excel (table_name):2     ImportPYMYSQL,XLWT3conn =Pymysql.connect (4host='118.24.3.40',5User='Jxz',6Passwd='123456',7port=3306,8db='Jxz',9charset='UTF8')Tensql ='select * from%s;'%table_name Onecur = conn.cursor ()#creates a cursor that does not specify a two-dimensional tuple returned by the cursor type ACur.execute (SQL)#Execute SQL -All_data = Cur.fetchall ()#get all the data in a table -Fileds = [Filed[0] forFiledinchCur.description]#get all the fields of the table into a list theBook = XLWT. Workbook ()#Create a new Excel -Sheet = Book.add_sheet ('Sheet1')#Add Sheet page -      forCol,filedinchEnumerate (fileds): -Sheet.write (0,col,filed)#writes a header to the first row in an Excel file +row = 1#define number of rows -      forDatainchAll_data:#Control Line +          forCol,filedinchEnumerate (data):#Control Columns A sheet.write (row,col,filed) atrow = row + 1#add 1 for each line you finish writing -Book.save ('%s.xls'%table_name)

Python Learning ==> operation MySQL