1 打开数据库连接
conn = MySQLdb.connect( host='x.x.x.x',
user='xxx',
passwd='xxx',
db='xxx',
port=3306)
sql = 'select * from table limit 10'
只有port字段类型为int
2 使用cursor()方法获取操作游标
cursor = db.cursor()
2.1 fetchall返回所有的结果
2.1.1 正常输出
with conn:
cur = conn.cursor()
cur.execute(sql)
rs = cur.fetchall()
for info in rs:
print info
输出：
(6104592L, 4L)
(214339L, 7L)
(7516522L, 7L)
(7744948L, 32L)
(9820259L, 33L)
(1420524L, 35L)
(8153528L, 38L)
(6422477L, 41L)
(3883549L, 42L)
(5919834L, 43L)
2.1.2 字典输出
with conn:
dict_cnf = MySQLdb.cursors.DictCursor # 增加字典输出的配置项
cur = conn.cursor(dict_cnf)
cur.execute(sql)
rs = cur.fetchall()
for info in rs:
print "id: %s , k:%s " %(info['id'],info['k'])
输出
id: 6104592 , k:4
id: 214339 , k:7
id: 7516522 , k:7
id: 7744948 , k:32
id: 9820259 , k:33
id: 1420524 , k:35
id: 8153528 , k:38
id: 6422477 , k:41
id: 3883549 , k:42
id: 5919834 , k:43
2.2 fetchone结果集的下一行
2.2.1 简单输出
with conn:
cur = conn.cursor()
cur.execute(sql)
rs = cur.fetchone()
print rs
输出
(6104592L, 4L)
2.2.2 测试description参数的作用（不是很理解）
with conn:
cur = conn.cursor()
cnt = cur.execute(sql)
rs = cur.fetchone()
desc = cur.description
print desc
print cnt # 输出查询值的数量
输出
(('id', 3, 7, 10, 10, 0, 0), ('k', 3, 2, 10, 10, 0, 0))
10
2.2.3 使用description参数配置输出，字典，跟（2.1.2 字典输出）类似
with conn:
cur = conn.cursor()
cur.execute(sql)
rs = cur.fetchone()
desc = cur.description
cols = [col[0] for col in desc]
rs = dict(zip(cols, rs))
print 'id: %s ,k:%s' %(rs['id'],rs['k'])
输出
id: 6104592 ,k:4
3 使用execute方法执行SQL语句
cursor.execute(sql)
4 关闭数据库连接
db.close()
