Python database operations commonly use the details of creating tables and inserting and retrieving data

Example 1, get the MYSQL version

# -*- coding: UTF-8 -*-
# The installation MYSQL DB for python
import MySQLdb as mdb
con = None
try:
    # The connection mysql Method: connect('ip','user','password','dbname')
    con = mdb.connect('localhost', 'root',
        'root', 'test');
    # All queries are connected con A module of cursor up-running 
    cur = con.cursor()
    # Execute a query 
    cur.execute("SELECT VERSION()")
    # The result of the previous query is a single result 
    data = cur.fetchone()
    print "Database version : %s " % data
finally:
    if con:
        # Anyway, remember to close the connection 
        con.close()

Execution results:
The Database version: 5.5.25

Instance 2, create a table and insert data

# -*- coding: UTF-8 -*-
import MySQLdb as mdb
import sys
# will con Set to global connection 
con = mdb.connect('localhost', 'root', 'root', 'test');
with con:
    # Get connected cursor Only to obtain cursor Before we can do anything 
    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 The 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 retrieve mysql data and traverse it

# -*- coding: UTF-8 -*-
import MySQLdb as mdb
import sys
# The connection mysql , gets the connected object 
con = mdb.connect('localhost', 'root', 'root', 'test');
with con:
    # Again, the first step is to get the connection cursor Object to execute a query 
    cur = con.cursor()
    # Similar to other languages query The function, execute is python Execute the query function in 
    cur.execute("SELECT * FROM Writers")
    # use fetchall Function to store the result set (a multidimensional tuple) rows inside 
    rows = cur.fetchall()
    # Walking through the result set in turn, each element, a record in the table, is displayed as a tuple 
    for row in rows:
        print row

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

Instance 4. Use dictionary cursor to get the result set (values can be accessed using table field names)

# -*- coding: UTF-8 -*-
#  Source: crazy ants blog www.server110.com Conclusion finishing 
import MySQLdb as mdb
import sys
# To obtain mysql The linked object of the query 
con = mdb.connect('localhost', 'root', 'root', 'test')
with con:
    # Gets the dictionary on the connection cursor , notice the method of getting, 
    # each cursor Actually all is cursor A subclass of 
    cur = con.cursor(mdb.cursors.DictCursor)
    # Execute statement unchanged 
    cur.execute("SELECT * FROM Writers")
    # The method of getting data is unchanged 
    rows = cur.fetchall()
    # Traversing the data is also the same (a little more direct than the previous one) 
    for row in rows:
        # Here, you can use the key-value pair method to get the data by the key name 
        print "%s %s" % (row["Id"], row["Name"])

Example 5. Method to get the field name and information of a single table

# -*- coding: UTF-8 -*-
#  Source: crazy ants blog www.server110.com Conclusion finishing 
import MySQLdb as mdb
import sys
# Gets the linked object for the database 
con = mdb.connect('localhost', 'root', 'root', 'test')
with con:
    # Gets a normal query cursor
    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")
    rows = cur.fetchall()
    # Gets the description of the connection object 
    desc = cur.description
    print 'cur.description:',desc
    # Print the header, which is the name of the field 
    print "%s %3s" % (desc[0][0], desc[1][0])
    for row in rows:
        # Print the result 
        print "%2s %3s" % row

 Operation results:  cur.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: using Prepared statements to execute queries (more secure and convenient)

# -*- coding: UTF-8 -*-
#  Source: crazy ants blog www.server110.com Conclusion finishing 
import MySQLdb as mdb
import sys
con = mdb.connect('localhost', 'root', 'root', 'test')
with con:
    cur = con.cursor()
    # And we see that we can do this by writing an assembly sql Statement to proceed 
    cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
        ("Guy de Maupasant", "4"))
    # use cur.rowcount Gets how many rows are affected 
    print "Number of rows updated: %d" % cur.rowcount

Results:

Number of rows updated: 1