MySQLdb使用

yosemite系统用brew安装mysql可以参考： http://segmentfault.com/a/1190000000438233

安装mysqldb: pip install mysql-python

简单例子

import MySQLdb as mdb
import sys

try:
    con = mdb.connect(‘localhost‘, ‘root‘, ‘password‘, ‘dbname‘)

    cur = con.cursor()
    cur.execute("select version()")
    ver = cur.fetchone()

except mdb.Error, e:
    print "error %d: %s" % (e.args[0], e.args[1])
    sys.exit(1)

finally:
    if con:
        con.close()

数据库的操作很容易出现异常，最好用exception来处理，使用完后关闭连接con.close()

使用with可以简化代码，而且提供异常处理

con = mdb.connect(...)

with con:
    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS Writers")
    cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT,                  Name VARCHAR(25))")
    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‘)")

查询数据

with con:
    cur = con.cursor()
    cur.execute("select * from Writers")
    rows = cur.fetchall()
    
    for row in rows:
        print row

with con:
    cur = con.cursor()
    cur.execute("select * from Writers")
    for i in range(cur.rowcount):
        row = cur.fetchone()
        print row[0], row[1]

上面是两种查询的方式，一种fetchall()获取整个表格，一种每次fetchone(), cur.fetchall()返回的rows是一个tuple，并且每个元素row也是一个tuple，里面有表格的一行数据

dict cursor

如果希望以字典的形式返回结果，可以用dict cursor

con = mdb.connect(...)

with con:
    cur = con.cursor(mdb.cursors.DictCursor)
    cur.execute("select * from Writers")

    rows = cur.fetchall()
    for row in rows:
        print row["id"], row["name"]

获取表格列名字

with con:
    cur = con.cursor()
    cur.execute("select * from Writers")

    desc = cur.description

print "%s %3s" % (desc[0][0], desc[1][0])    

输出((‘Id‘, 3, 1, 11, 11, 0, 0), (‘Name‘, 253, 17, 75, 75, 0, 1))

print desc[0][0], desc[1][0]就可以得到列名

插入，读取图片

mysql提供了插入图片数据的方法，图片是二进制数据，mysql中提供一种特殊的数据类型来存放二进制数据，BLOB (binary large obj),包括TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB

def read_images():
    fin = open("togglebg.png")
    img = fin.read()
    return img

with con:
    cur = con.cursor()
    cur.execute("create table images(id int primary key, data mediumblob)")

    data = read_images()
    cur.execute("insert into images values(1, %s)", (data, ))

def read_images():
    fin = open("togglebg.png")
    img = fin.read()
    return img

def writeImages(data):
    fout = open(‘hello.jpg‘, ‘wb‘)
    with fout:
        fout.write(data)

with con:
    cur = con.cursor()
    cur.execute("select data from images where id = 1")
    data = cur.fetchone()[0]
    writeImages(data)

参考：http://zetcode.com/db/mysqlpython/