Python 使用 MySQL 数据库

#!/bin/env python
# -*- coding: utf-8 -*-

import MySQLdb

def printRow(res):
    for prop in res:
        print prop,
    print

conn = MySQLdb.connect(host="localhost",user="root",passwd="root",db="employees",port=3306)
cur = conn.cursor()

# fetchone()
# 获取一条结果
print '='*5 + 'fetchone' + '='*5
rows = cur.execute("select count(*) from employees")
result = cur.fetchone()
print "rows:%d, result:%d" % (rows,result[0])

# fetchmany()
# 获取多条结果
print '='*5 + 'fetchmany' + '='*5
rows = cur.execute("select * from employees limit 10")
print "rows:%d" % rows
result = cur.fetchmany(5)
print "first 5:"
for res in result:
    printRow(res)
print "last 5:"
result = cur.fetchmany(5)
for res in result:
    for prop in res:
        print prop,
    print

# fetchall()
# 获取所有结果
print '='*5 + 'fetchall' + '='*5
rows = cur.execute("select * from employees limit 10")
print "rows:%d" % rows
result = cur.fetchall()
print "all 10:"
for res in result:
    printRow(res)

# scroll()
# 控制游标的移动
print '='*5 + 'scroll' + '='*5
rows = cur.execute("select * from employees limit 10")
print "rows:%d" % rows
print "first row:"
first_row = cur.fetchone()
printRow(first_row)
# absolute 从第 0 条位置向下移动 2 条
print "third row:"
cur.scroll(2,mode='absolute')
third_row = cur.fetchone()
printRow(third_row)
# relative 从当前移动 3 条
print "seventh row:"
cur.scroll(3,mode='relative')
seventh_row = cur.fetchone()
printRow(seventh_row)

# executemany()
# 用多个参数执行同一条语句,参数是所有支持迭代的对象
print '='*5 + 'executemany' + '='*5
genders = ['F','M']
rows = cur.executemany('select * from employees where gender=%s limit 1', genders)
result = cur.fetchall()
print rows
print result
# 执行结果表明每一条语句执行过后游标 cursor 都会改变。
# 因此不适合多参数的查询,而适合多参数的插入

# conn.commit()
# 用来执行事务
print '='*5 + 'commit' + '='*5
try:
    cur.execute("insert into employees values(0,date(now()),'Jack','Sparrow','M',date(now()))")
    cur.execute("insert into employees values(1,date(now()),'Jack','Sparrow','M',date(now()))")
    raise MySQLdb.Error
    conn.commit()
except MySQLdb.Error,e:
    # cur.execute("delete from employees where emp_no in (0,1)")
    print "Error Message: %s" % str(e.args)

cur.close()
conn.close()

发表评论