Python中实现连接mysql数据库

# -*- coding: utf-8 -*-
# Created on 2012-10-25
# @author: pingzheng
# $Id$
import MySQLdb
from MySQLdb.cursors import DictCursor
from website.utils.pinyin import Hanzi2Pinyin

conn=MySQLdb.connect(host="localhost",user="root",passwd="sa",db="mytable",charset="utf8")
提供的connect方法用来和数据库建立连接,接收数个参数,返回连接对象.
比较常用的参数包括
host:数据库主机名.默认是用本地主机.
user:数据库登陆名.默认是当前用户.
passwd:数据库登陆的秘密.默认为空.
db:要使用的数据库名.没有默认值.
port:MySQL服务使用的TCP端口.默认是3306.
charset:数据库编码.
db = MySQLdb.connect(host="localhost",user="root",passwd="sa",db="mytable",charset="utf8")
db.set_character_set('utf8')


def get_all_author():
    global db
    cursor = db.cursor(DictCursor) #DictCursor可以实现以字典方式访问数据
    cursor.execute("SELECT id,name FROM book_author WHERE pyindex = 0")
    data = cursor.fetchall()
    cursor.close()
    return data


if __name__ == '__main__':
    p = Hanzi2Pinyin()
    i = list('-ABCDEFGHIJKLMNOPQRSTUVWXYZ*')
    for a in get_all_author():
        c = db.cursor()
        pyindex = p.convert(a['name'].decode('utf-8'))[0][0].upper()
        pyindex = i.index(pyindex)
        c.execute('UPDATE book_author SET pyindex = %s WHERE id = %s', (pyindex, a['id']))
        c.close()