使用Python操作MySQL之基础篇

使用Python操作MySQL
首先下载MySQLdb，地址：http://sourceforge.net/projects/mysql-python/

1. 使用
import MySQLdb
1.1. 连接
conn = MySQLdb.Connection(host, user, password, dbname)
1.2. 选择数据库，如果上面没有指定数据库，则使用此方法指定！
conn.select_db(’database name’)
1.3. 获得cursor
cur = conn.cursor()
1.4. cursor位置设定
cur.scroll(int, mode)
mode可为相对位置或者绝对位置，分别为relative和absolute。
1.5. select
cur.execute(‘select clause’)
例如
cur.execute(‘select * from mytable’)

row = cur.fetchall()
或者：
row1 = cur.fetchone()
1.6. insert
cur.execute(‘inset clause’)
例如
cur.execute("insert into user (Name, Password) values ('maggie','12345')")
conn.commit()

1.7. update
cur.execute(‘update clause’)
例如
cur.execute("update user set Name = 'eric chau' where id = 1")
conn.commit()

1.8. delete
cur.execute(‘delete clause’)
例如
cur.execute("delete from user where id = 1")
conn.commit()

完整代码：

from MySQLdb import Connect

def conn():
#conn = Connect('localhost','root','root')
#conn.select_db('eric')
conn = Connect('localhost','root','root','eric')
cur = conn.cursor()
cur.execute('select * from user')
cur.scroll(0)
row1 = cur.fetchone()
print 'id:', row1[0]
print 'name:', row1[1]
print 'password:', row1[2]
#cur.execute("insert into user (Name, Password) values ('maggie','12345')")
#cur.execute("update user set Name = 'eric chau' where id = 1")
cur.execute("delete from user where id = 11")
conn.commit()
if __name__=='__main__':
conn()

由于python的数据库模块有专门的数据库模块的规范，所以，其实不管使用哪种数据库的方法都大同小异的，这里就给出一段示范的代码：

#-*- encoding: gb2312 -*-
import os, sys, string
import MySQLdb

# 连接数据库　
try:
conn = MySQLdb.connect(host='localhost',user='root',passwd='xxxx',db='test1')
except Exception, e:
print e
sys.exit()

# 获取cursor对象来进行操作

cursor = conn.cursor()
# 创建表
sql = "create table if not exists test1(name varchar(128) primary key, age int(4))"
cursor.execute(sql)
# 插入数据
sql = "insert into test1(name, age) values ('%s', %d)" % ("zhaowei", 23)
try:
cursor.execute(sql)
except Exception, e:
print e

sql = "insert into test1(name, age) values ('%s', %d)" % ("张三", 21)
try:
cursor.execute(sql)
except Exception, e:
print e
# 插入多条

sql = "insert into test1(name, age) values (%s, %s)"
val = (("李四", 24), ("王五", 25), ("洪六", 26))
try:
cursor.executemany(sql, val)
except Exception, e:
print e

#查询出数据
sql = "select * from test1"
cursor.execute(sql)
alldata = cursor.fetchall()
# 如果有数据返回，就循环输出, alldata是有个二维的列表
if alldata:
for rec in alldata:
print rec[0], rec[1]

cursor.close()

conn.close()

转自：http://hi.baidu.com/cheneychen/blog/item/064f9d2b1b5d50fde6cd40dd.html