第二十章、 数据库编程
环境设置
1).安装MySQL-python
http://www.lfd.uci.edu/~gohlke/pythonlibs/
MySQL-python-1.2.3.win32-py2.7.exe
1)    使用数据库接口

import MySQLdb  

cxn = MySQLdb.Connect(host = '127.0.0.1', user = 'root', passwd = 'root') 

cur = cxn.cursor()  

try: 

    cur.execute("DROP DATABASE test610") 

except Exception, e: 

    print e.args; 

finally: 

    pass  

cur.execute("CREATE DATABASE test610") 

cur.execute("USE test610") 

cur.execute("CREATE TABLE users (id INT, name VARCHAR(8))") 

cur.execute("INSERT INTO users VALUES(10, 'tao')") 

cur.execute("INSERT INTO users VALUES(20, 'jin')") 

cur.execute("INSERT INTO users VALUES(31, 'dan')") 

cur.execute("UPDATE users SET name = 'jim' WHERE id = 20") 

cur.execute("SELECT * FROM users") 

for row in cur.fetchall(): 

    print '%s\t%s' %row  

cur.close() 

cxn.commit() 

cxn.close() 

2)    使用ORM_SQLalchemy
环境设置
安装SQLalchemy(SQLAlchemy-0.7.1.tar.gz)
下载http://www.sqlalchemy.org/download.html
解压放到python安装目录下的lib目录里

D:\Python27\Lib\SQLAlchemy-0.7.1>python setup.py install 

from sqlalchemy import *  

##Precondition：database 'test0615' do exist! 

engine = create_engine('mysql://root:root@localhost/test0615')  

##Define and Create Tables 

metadata = MetaData() 

users = Table('users', metadata, 

        Column('id', Integer, primary_key=True), 

        Column('name', String(10)), 

        Column('fullname', String(20)), 

        ) 

address = Table('address', metadata, 

        Column('id', Integer, primary_key=True), 

        Column('user_id', None, ForeignKey('users.id')), 

        Column('email', String(20), nullable=False) 

        ) 

metadata.create_all(engine, checkfirst = True)  

##Insert Expressions 

#method 1 

ins = users.insert().values(name='Jim', fullname='Jim T') 

conn = engine.connect() 

result = conn.execute(ins) 

#method 2 

result = engine.execute(users.insert(), name='fred', fullname="Fred F") 

#method 3 

metadata.bind = engine 

result = users.insert().execute(name="mary", fullname="Mary C") 

metadata.bind = None 

#method 4 

conn.execute(address.insert(), [ 

    {'user_id': 1, 'email' : 'jack@yao.com'}, 

    {'user_id': 2, 'email' : 'wedy@aol.com'}, 

    ])  

##Selecting 

s = select([users]) 

result = conn.execute(s) 

#method 1 

for row in result: 

    print row 

#method 2 

s = select([users, address], users.c.id==address.c.user_id) 

for row in conn.execute(s): 

    print row  

##Updates 

conn.execute(users.update(). 

    where(users.c.name == 'jack'). 

    values(name = 'ed') 

    )  

##Deletes 

conn.execute(address.delete().where(address.c.id > 20)) 

conn.execute(users.delete().where(users.c.name > 'm'))  

##遗留问题,无法关闭数据库连接 

##metadata.drop_all(engine, [users, address], checkfirst = False) 

3)    使用ORM_SQLObject
环境设置
安装FormEncode (FormEncode-1.2.4.tar.gz)
下载http://pypi.python.org/pypi/FormEncode
解压放到python安装目录下的lib目录里

D:\Python27\Lib\FormEncode-1.2.4>python setup.py install

安装SQLObject (SQLObject-1.0.1.tar.gz)
下载http://pypi.python.org/pypi/SQLObject
解压放到python安装目录下的lib目录里

D:\Python27\Lib\SQLObject-1.0.1>python setup.py install

#!/usr/bin/env python  

import os 

import MySQLdb 

import _mysql_exceptions 

from sqlobject import *  

DBNAME = 'database0615' 

url = 'mysql://root:root@localhost/%s' % DBNAME 

COLSIZ = 10 

FIELDS = ('firstName', 'middleInitial', 'lastName')  

cxn1 = sqlhub.processConnection = connectionForURI(url) 

cxn1.query("DROP DATABASE %s" % DBNAME) 

cxn1.query("CREATE DATABASE %s" % DBNAME) 

cxn1.close()  

try: 

    class Person(SQLObject): 

        firstName = StringCol() 

        middleInitial = StringCol(length=1, default=None) 

        lastName = StringCol() 

    Person.createTable() 

except NameError, e: 

    pass  

#make SQLObject print out the SQL it executes 

Person._connection.debug = False  

#Insert 

p1 = Person(firstName = "John", lastName = "Doe") 

p2 = Person(firstName = "Jin", lastName = "Tao") 

p3 = Person(firstName = "Dan", lastName = "Tao") 

p4 = Person(firstName = "Joan", lastName = "Wu")  

#Select 

print p2.lastName, p2.firstName 

print list(Person.select(AND(Person.q.lastName == "Tao", 

           Person.q.firstName == "Jin")))  

#Update 

p1.middleInitial = 'S' 

p3.middleInitial = 'T' 

p2.lastName = 'Hu'  

#Delete 

Person.delete(p1.id) 

for row in Person.select(): 

    print '%s%s%s' % (tuple([str(getattr(row, 

        field)).title().ljust(COLSIZ) for field in FIELDS])) 

Person.deleteBy()