第10章 Python数据库编程

本章的知识点：

1、ODBC的连接方式；
2、DAO的连接方式；
3、ADO的连接方式；
4、访问Oracle数据库的各种方法；
5、MySQL数据库的访问；
6、SQLite数据库的使用；

10.1 Python环境下的数据库编程

10.1.1 通过ODBC访问数据库

import odbc, dbi
import time
db = odbc.odbc("addresses/sctt/tiger")
curser = db.cursor()
curser.execute("select * from address order by id desc")
for col in curser.description:
    print (col[0], col[1])
result = curser.fetchall()
for row in result:
    print (row)
    print (row[1], row[2])
    timeTuple = time.localtime(row[3])
    print (time.strftime("%Y/%m/%d",timeTuple))

10.1.2 使用DAO对象访问数据库

import win32com.client
engine = win32com.client.Dispatch("DAO.DBEngine.36")
db = engine.OpenDatabase("addresses.mdb")
rs = db.OpenRecordset("address")
rs = db.OpenRecordset("select * from address")
# 插入数据
db.Execute("""
insert into address(name, address, createtime)
values('赵涛','上海虹口', '2008-3-25')
""")
while not rs.EOF:
    print ((rs.Fields("address").Value).encode('gb2312'))
    rs.MoveNext

10.1.3 使用ActiveX Data Object访问数据库

import win32com.client
conn = win32com.client.Dispatch('ADODB.Connection')
dsn = 'Provider=OraOLEDB.Oracle;PLSQLRSet=1;Password=tiger;UaerID=scott;DataSource=ORCL'
conn.Open(dsn)
rs = win32com.client.Dispatch('ADODB.Recordset')
sql = """
      from emp a,emp b,dept c where a.sal > 2500
      and a.mgr = b.empno
      and a.mgr = b.empno(+)
      order by a.sal"""
rs.Open(sql, conn)
rs.MoveFirst()
li = list()
while notrs.EOF:
    d = dict()
    for x in range(rs.Fields.Count):
        key = rs.Fields.Item(x).Name
        value = rs.Fields.Item(x).Value
        d.setdefault(key, value)
    li.append(d)
    print (rs.Fields("薪水").Value, rs.Fields("工作").Value,\
           rs.Fields("部门").Value, rs.Fields("姓名").Value, rs.Fields("管理者").Value)
    rs.MoveFirst()
for d in li:
    for key in d.keys():
        print (key.encode("gb2312"), d.get(key),)
    print ()
conn.Close()

10.1.4 Python连接数据库的专业模块

1) cx_Oracle模块

import cx_Oracle
connection = cx_Oracle.connect("scott", "tiger", "ORCL")
cursor = connection.cursor()
sql = """select a.ename 姓名,a.job 工作, a.sal 薪水, c.dname 部门，b.ename 管理者
        from emp a,emp b,dept cwhere a.sal > 2500
        and a.mgr = b.empno
        and a.mgr = b.empno(+)
        order by a.sal"""
cursor.execute(sql)
for x in cursor.fetchall():
    for value in x:
        print (value,)
        print ()
        cursor.close()
        connection.close()

 

2) MySQLdb模块

import os,sys
import MySQLdb
# 连接数据库
try:
    conn = MySQLdb.connect(host='localhost', user='root', passwd='',db='ADDRESSBOOKDB')
except Exception,e:
    print (e)
    sys.exit()
cursor = conn.cursor()
sql = "insert into address(name, address) value (%s, %s)"
values = (("张三","北京海淀区"),("李四","北京海淀区"), ("王五","北京海淀区"))
try:
    cursor.executemany(sql, values)
except Exception,e:
    print (e)
sql = "select * from address"
cursor.execute(sql)
data = cursor.fetchall()
if data:
    for x in data:
        print (x[0],x[1])
cursor.close()
conn.close()

10.2 使用Python的持久化模块读写数据

import shelve
addresses = shelve.open('addresses')
addresses['1'] = ['TOM', 'Beijing road', '2008-01-03']
addresses['2'] = ['Jerry', 'Shanghai road','2008-03-03']
if addresses.has_key('2'):
    del addresses['2']
print (addresses)
addresses.close()

10.3 嵌入式数据库SQLite

10.3.1 SQLite的命令行工具

10.3.2 使用sqlite3模块访问SQLite数据库

import sqlite3
# 连接数据库
conn = sqlite3.connect("D:\Python\21天学通Python")
# 创建表
conn.execute("create table if not exists address(id integer primary key autoincrement,name varchar(128),address varchar(128))")
# 插入数据
conn.execute("insert into address(name, address) values('Tom','Beijing road')")
conn.execute("insert into address(name, address) values('Jerry','Shanghai road')")
# 手动提交数据
conn.commit()
# 获取游标对象
cur = conn.execute()
# 使用游标查询数据
cur.execute("select * from address")
# 获取所有结果
res = cur.fetchall()
print ("address:", res)
for line in res:
    for f in line:
        print (f,)
    print ()
# 关闭连接
cur.close()
conn.close()

10.5 习题

1) 根据自己的需求，练习相应的使用Python连接数据库进行操作的方法。
2) 创建一个Access数据库，然后练习使用Python读取出里面的数据。