Detailed explanation of the use of common functions in python database operations

Example 1. Obtain the MYSQL version

The code is as follows:

# -*- coding: UTF-8 -*-
#Install MYSQL DB for python
import MySQLdb as mdb
con = None
try:
​ #Connect to mysql Method: connect('ip','user','password','dbname')
con = mdb.connect('localhost', 'root',
'root', 'test');
#All queries , all run on a module cursor connected to con
Cur = con.cursor()
#Execute a query
cur.execute("SELECT VERSION()")
#Get the result of the previous query, which is a single result
data = cur.fetchone()
                                                                                                                                               data = cur. 25

Example 2. Create a table and insert data

Copy code The code is as follows:

# -*- coding: UTF-8 -*-

import MySQLdb as mdb

import sys

#Set con as global Connection

con = mdb.connect('localhost', 'root', 'root', 'test');

with con:

​ #Get the cursor of the connection. Only by obtaining the cursor can we perform various operations
​​ cur = 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(25))")
    #The following is inserted 5 pieces of data
  cur.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

Copy code The code is as follows:


# -*- coding: UTF-8 -*-

import MySQLdb as mdb

import sys

#Connect to mysql and get the connected object

con = mdb.connect('localhost', 'root', 'root', 'test');

with con:

#Still, the first step is to get The connected cursor object is used to execute the query
Cur = con.cursor()
#Similar to the query function in other languages, execute is the execution query function in python
cur.execute("SELECT * FROM Writers")
#Use The fetchall function stores the result set (multidimensional tuple) into rows
rows = cur.fetchall()
#Traverse the result set in sequence and find that each element is a record in the table, displayed as a tuple
for ROW in ROWS:
Print Row

Copy code code as follows:


execute results:
(1L, 'Jack London')
(2L, 'Honore de Balzac')

(3L, 'Lion Feuchtwanger')

( 4L, 'Emile Zola')

(5L, 'Truman Capote')

Example 4. Use dictionary cursor to obtain the result set (you can use table field names to access values)

Copy code The code is as follows:


# -*- coding: UTF-8 -*-

# Source: Crazy Ant’s blog www.server110.com Summary and compilation

import MySQLdb as mdb

import sys

#Get the link object of mysql query

con = mdb.connect('localhost' , 'root', 'root', 'test')

with con:
#Get the dictionary cursor on the connection, pay attention to the acquisition method,
#Each cursor is actually a subclass of cursor
cur = con.cursor( mdb.cursors.DictCursor)
      #The execution statement remains unchanged
    cur.execute("SELECT * FROM Writers")
   #The method of obtaining data remains unchanged
     rows = cur.fetchall()
                                                                       More directly)
for row in rows:
#Here, you can use the key-value pair method to obtain data by key name
])

Example 5, method of obtaining field names and information of a single table

Copy code The code is as follows:

# -*- coding: UTF-8 -*-
# Source: Crazy Ant’s blog www.server110.com Summary and compilation
import MySQLdb as mdb
import sys
#Get the link object of the database
con = mdb. connect('localhost', 'root', 'root', 'test')
with con:
     #Get the normal query cursor
     cur = con.cursor()
      cur.execute("SELECT * FROM Writers")
rows = cur.fetchall()
  #Get the description information of the connection object
  desc = cur.description
  print 'cur.description:',desc
  #Print the header, which is the field name
  print "%s %3s" % ( desc[0][0], desc[1][0])
for row in rows:
.description: (('Id', 3, 1, 11, 11, 0, 0), ('Name', 253, 17, 25, 25, 0, 1))
Id Name

1 Jack London

2 Honore de Balzac

3 Lion Feuchtwanger

4 Emile Zola
5 Truman Capote


Example 6. Use Prepared statements to execute queries (more secure and convenient)

Copy code The code is as follows:

# -*- coding: UTF-8 - *-
# Source: Crazy Ant’s blog www.server110.com Summary and compilation

import MySQLdb as mdb

import sys

con = mdb.connect('localhost', 'root', 'root', 'test')

with con:
Cur = con.cursor()
#We see that this can be done 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


Result:


Copy code The code is as follows :

Number of rows updated: 1