Python database operations frequently used features (CREATE TABLE/Insert data/Get Data)

Example 1, get MySQL version

#-*-coding:utf-8-*-#installing MySQL DB for PythonImportMySQLdb as Mdbcon=NoneTry:    #ways to connect to MySQL: Connect ( host = ‘localhost‘ ,user = ‘root‘ ,passwd = ‘root‘ ,db = ‘test‘ ,port = 3306 )con = Mdb.connect ('localhost','Root',        'Root','Test'); #all queries that run on the cursor of a module connected to conCur =con.cursor ()#Execute a queryCur.execute ("SELECT VERSION ()")    #Gets the result of the last query, which is a single resultdata =Cur.fetchone ()Print "Database version:%s"%Datafinally:    ifCon:#Anyway, connect remember to closeCon.close ()

Execution Result:

Database version:5.5.25

Example 2, creating a table and inserting data

#-*-coding:utf-8-*-ImportMySQLdb as MDBImportSYS#To set con as a global connectioncon = Mdb.connect ('localhost','Root','Root','Test'); with con:#get the cursor of the connection, we can do all kinds of things only if we get the cursorCur =con.cursor ()#Create a data table writers (Id,name)Cur.execute ("CREATE TABLE IF not EXISTS writers (Id INT PRIMARY KEY auto_increment, Name VARCHAR )")    #5 data are inserted belowCur.execute ("INSERT into writers (Name) VALUES (' Jack London ')") Cur.execute ("INSERT into writers (Name) VALUES (' Honore de Balzac ')") Cur.execute ("INSERT into writers (Name) VALUES (' Lion feuchtwanger ')") Cur.execute ("INSERT into writers (Name) VALUES (' Emile Zola ')") Cur.execute ("INSERT into writers (Name) VALUES (' Truman Capote ')")

Example 3, Python uses slect to get MySQL data and traverse

#-*-coding:utf-8-*-ImportMySQLdb as MDBImportSYS#connect to MySQL, Get connected objectscon = Mdb.connect ('localhost','Root','Root','Test'); with con:#Still, the first step is to get the connected cursor object, which is used to execute the queryCur =con.cursor ()#similar to the query function in other languages, execute is the Execute function in PythonCur.execute ("SELECT * from writers")    #Use the Fetchall function to store the result set (multidimensional tuples) in rowsrows =Cur.fetchall ()#iterate through the result set and find each element, which is a record in the table, with a tuple to display     forRowinchrows:PrintRow

Execution Result:
(1L, ' Jack London ')
(2L, ' Honore de Balzac ')
(3L, ' Lion feuchtwanger ')
(4L, ' Emile Zola ')
(5L, ' Truman Capote ')

Example 4, using the dictionary cursor to get the result set (you can use the table field name to access the value)

#-*-coding:utf-8-*-ImportMySQLdb as MDBImportSYS#get a link object for a MySQL querycon = Mdb.connect ('localhost','Root','Root','Test') with con:#get the dictionary cursor on the connection, note the method obtained,    #each cursor is actually a subclass of the cursorCur =con.cursor (mdb.cursors.DictCursor)#The execution statement does not changeCur.execute ("SELECT * from writers")    #Get Data method unchangedrows =Cur.fetchall ()#traverse data also unchanged (a bit more directly than the previous one)     forRowinchrows:#here, you can use the key-value pair method, the key name to get the data        Print "%s%s"% (row["Id"], row["Name"]) 

Example 5, methods for obtaining field names and information for a single table

#-*-coding:utf-8-*-ImportMySQLdb as MDBImportSYS#gets the linked object of the databasecon = Mdb.connect ('localhost','Root','Root','Test') with con:#get the normal query cursorCur =con.cursor () Cur.execute ("SELECT * from writers") Rows=Cur.fetchall ()#Gets the description information for the connection objectdesc =cur.descriptionPrint 'cur.description:', desc#print the header, which is the field name    Print "%s%3s"% (Desc[0][0], desc[1][0]) forRowinchrows:#Print Results        Print "%2s%3s"%Row

Run Result: cur.description: (' Id ', 3, 1, one, one, 0, 0), (' Name ', 253, +, 0, 1 , +))
Id Name
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

Example 6, using prepared statements to execute the query (more secure and convenient)

#-*-coding:utf-8-*-ImportMySQLdb as MDBImportSyscon= Mdb.connect ('localhost','Root','Root','Test') with Con:cur=con.cursor ()#we see that we can do this by writing a SQL statement that can be assembled.Cur.execute ("UPDATE writers SET Name =%s WHERE Id =%s",        ("Guy de maupasant","4"))    #use Cur.rowcount to get how many rows are affected    Print "Number of rows updated:%d"% Cur.rowcount

Results:

Number of rows updated:1

Python database operations frequently used features (CREATE TABLE/Insert data/Get Data)