How to manipulate MYSLQ in Python __python

instance 1, get the version of MySQL

To install the MySQL module in the Windows environment for Python development, see my other article:

Mysql-python Windows exe installation file download

#-*-Coding:utf-8-*-

#安装MYSQL DB for Python
import mysqldb as mdb

con = None

try:
    #连接mysql的方法: Conne CT (' IP ', ' user ', ' password ', ' dbname ')
    con = mdb.connect (' localhost ', ' root ',
        ' root ', ' test ');

    #所有的查询, all of the
    cur = con.cursor ()

    #执行一个查询
    cur.execute ("Select VERSION ()")

    that is running above the cursor of a module connected to con To get the result of the last query, is a single result of
    data = Cur.fetchone ()
    print "Database version:%s"% data
finally:
    if con:
        # Anyway, connect remember to close
        Con.close ()

Execution results:

Database version:5.5.25

instance 2, creating a table and inserting data

Mainly in the cursor above execute Execute method to proceed, see Source code:

#-*-Coding:utf-8-*-
# from Www.crazyant.net summary collation
import mysqldb as MDB
import sys

#将con设定为全局连接
con = Mdb.connect (' localhost ', ' root ', ' root ', ' test ');

With con:

    #获取连接的cursor, we can do all kinds of operations
    cur = con.cursor ()
    #创建一个数据表 writers (id,name)
    if we get cursor. Cur.execute ("CREATE TABLE IF not EXISTS \
        writers (Id INT PRIMARY KEY auto_increment, Name VARCHAR)")
    #以下插入了 5 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 ') ")

Run results (see in phpMyAdmin, see results on MySQL command line):

Instance 3, Python uses slect to get MySQL data and iterate through

This is probably the most used, please read the code quickly:

#-*-Coding:utf-8-*-
# Source: Www.crazyant.net Collation Summary
import MYSQLDB as MDB
import sys

#连接mysql to get connected objects
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ');

With con:
    #仍然是, the first step is to get the cursor object for the connection, which is used to execute the query
    cur = con.cursor ()
    #类似于其他语言的query函数, execute is the execution query function in Python
    Cur.execute ("SELECT * from writers")

    #使用fetchall函数, the result set (multidimensional Group) is deposited in rows
    = Cur.fetchall ()

    Iterate through the result set, discovering that each element is a record in the table, with a tuple to display for
    row in rows:
        print row

Run Result:

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

The code above is used to take all the results out, but when printing is a meta ancestor print per line, now we use the method to remove one of the individual data:

#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog www.crazyant.net summary collation

import mysqldb as MDB
import sys

# Get MySQL link object
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ');

With con:
    #获取执行查询的对象
    cur = con.cursor ()

    #执行那个查询, this is a SELECT statement
    cur.execute ("SELECT * From writers")

    #使用cur. RowCount gets the number of
    numrows = Int (cur.rowcount) for the result set

    #循环numrows次 each time one row of data is fetched for the
    I in range ( NumRows):
        #每次取出一行, put in row, this is a tuple (id,name)
        row = Cur.fetchone ()
        #直接输出两个元素
        print row[0], row[1]

Run Result:

1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

numrows = Int (cur.rowcount) is used to get the number of result sets row = Cur.fetchone () fetch one row of data at a time, and the pointer to the recordset executes the next row instance 4, Use dictionary cursor to get result sets (You can use table field names to access values)

#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog www.crazyant.net summary collation

import mysqldb as MDB
import sys

# Get MySQL Query link object
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ') with

con:
    #获取连接上的字典cursor, pay attention to the method of obtaining,
    #每一个cursor其实都是cursor的子类
    cur = con.cursor (mdb.cursors.DictCursor)

    #执行语句不变
    cur.execute ("SELECT * From writers ")

    #获取数据方法不变
    rows = Cur.fetchall ()

    #遍历数据也不变 (a bit more directly than the previous one)
    for row in rows:
        #这里, You can use the method of a key-value pair by using the key name to get the data
        print "%s%s"% (row["Id"], row["name")

instance 5, how to get field names and information for a single table

#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog www.crazyant.net summary collation

import mysqldb as MDB
import sys

#获取数据库的链接对象 C4/>con = Mdb.connect (' localhost ', ' root ', ' root ', ' test ') with

con:
    #获取普通的查询cursor
    cur = con.cursor ()
    Cur.execute ("SELECT * from writers")

    rows = Cur.fetchall ()

    #获取连接对象的描述信息
    desc = cur.description
    print ' cur.description: ', desc

    #打印表头, is the field name
    print '%s%3s '% (desc[0][0], desc[1][0]) for

    row in Rows:
        #打印结果
        print "%2s%3s"% row

Run Result:

Cur.description: (' IDs ', 3, 1, one, 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 instance 6, execute query using prepared statements (more secure and convenient)

#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog www.crazyant.net summary collation
import mysqldb as MDB
import sys

con = mdb.conn ECT (' localhost ', ' root ', ' root ', ' test ') with

con:    

    cur = con.cursor ()
    #我们看到, 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")
    #使用cur. RowCount gets how many rows
    are affected Print "Number of rows updated:%d"% cur.rowcount

Results:

Number of rows Updated:1 instance 7, use binary to save pictures in MySQL

Some people like to store pictures in MySQL (This approach seems very rare), I see most of the program, pictures are stored in the server files, the database is only the image of the address, but MySQL is supporting the image into the database, but also corresponding to a special field BLOB (Binary Large object), that is, large binary object fields, see the following program, note that the test picture yourself to find one, address to be correct:

First, create a table in the database that holds the picture:

CREATE TABLE Images (Id INT PRIMARY KEY auto_increment, Data Mediumblob);

Then run the following Python code:

#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog www.crazyant.net summary collation
import mysqldb as MDB
import sys

try:
    # Open the picture in read file mode
    fin = open (... /web.jpg ")
    #将文本读入img对象中
    img = fin.read ()
    #关闭文件
    fin.close ()

except IOError, E:
    #如果出错, Print error message Print
    "error%d:%s"% (E.args[0],e.args[1])
    sys.exit (1)

try:
    #链接mysql, get object
    conn = Mdb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' test ')
    #获取执行cursor
    cursor = conn.cursor ()
    #直接将数据作为字符串, insert the database
    cursor.execute (insert into Images SET data= '%s '% mdb.escape_string (img))

    #提交数据
    conn.commit ()

    #提交之后, and then close the cursor and link
    cursor.close ()
    conn.close ()

except MDB. Error, E:
    #若出现异常, printing information print
    "Error%d:%s"% (E.args[0],e.args[1])
    sys.exit (1)

Results:

The Escape_string function escapes the string that is inserted into the database, which refers to an attack instance of SQL injection 8, read the picture from the database

#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog www.crazyant.net summary collation
import mysqldb as MDB
import sys

try:
    # Connect MySQL, Get connected object
    conn = mdb.connect (' localhost ', ' root ', ' root ', ' test ');

    cursor = conn.cursor ()

    #执行查询该图片字段的SQL
    cursor.execute ("SELECT Data from Images LIMIT 1")

    #使用二进制写文件的方法, Open a picture file, if it does not exist automatically create
    fout = open (' Image.png ', ' WB ')
    #直接将数据如文件
    fout.write (Cursor.fetchone () [0])
    #关闭写入的文件
    fout.close ()

    #释放查询数据的资源
    cursor.close ()
    conn.close ()

except IOError, E:
    # Catch IO exception, mainly file write error
    print "Error%d:%s"% (E.args[0],e.args[1])
    sys.exit (1)

instance 9, using transaction as a transaction (manual commit, automatic rollback)

#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog www.crazyant.net summary collation
import mysqldb as MDB
import sys

try:
    # Connect MySQL, Get connected object
    conn = mdb.connect (' localhost ', ' root ', ' root ', ' test ');

    cursor = conn.cursor ()
    #如果某个数据库支持事务, automatically opens the
    #这里用的是MYSQL, so the transaction is automatically opened (if the Myism engine does not)
    Cursor.execute ("UPDATE Writers Set name =%s where Id =%s,
        ("Leo Tolstoy", "1"))
    Cursor.execute ("UPDATE writers SET name =%s where I D =%s ",
        (" Boris Pasternak "," 2 "))
    cursor.execute (" UPDATE Writer SET Name =%s WHERE Id =%s ",
        (" Leonid Leo Nov "," 3 ")   

    #事务的特性1, Atomic Manual Submit Conn.commit ()

    cursor.close () conn.close ()

except MDB. Error, E:
    #如果出现了错误, you can roll back the three statements above either execute, or do not execute
    conn.rollback ()
    print "Error%d:%s"% (E.args[0], E.ARGS[1])

Results: 1 Because there is no writer table (SQL third statement), an error occurs:

Error 1146:table ' test.writer ' doesn ' t exist

2, error, departure exception handling, the first two of the 3 statements will automatically become not executed, the result is unchanged

3, if this code into a MyISAM engine table, the first two sentences will be executed, the third sentence will not be, if the INNDB engine, then will not be executed.