python核心编程学习笔记-2016-09-24-01-数据库编程（四）

        21.2.7 使用数据库接口程序示例（只有MySQL运行的比较顺利，其他两个PostgreSQL和SQLite出各种问题）

        主要过程正如书中所说，创建数据库、创建表和使用表。

>>> import MySQLdb
>>> cxn = MySQLdb.connect(user='root')
>>> cxn.query('DROP DATABASE test')
Traceback (most recent call last):
      File "<stdin>", line 1, in ?
     _mysql_exceptions.OperationalError: (1008, "Cant't drop database 'test'; database doesn't exist")
>>> cxn.query('CREATE DATABASE test')
>>> cxn.query("GRANT ALL ON test.* to ''@'localhost'")
>>> cxn.commit()
>>> cxn.close()
# 创建表
>>> cxn = MySQLdb.connect(db='test')
>>> cur = cxn.cursor()
>>> cur.execute('CREATE TABLE users (login VARCHAR(8), uid INT)')
0L
# 插入几行数据
>>> cur.execute("INSERT INTO users VALUES('john', 7000)")
1L
>>> cur.execute("INSERT INTO users
VALUES('jane', 7001)")
1L
>>> cur.execute("INSERT INTO users
VALUES('bob', 7200)")
>>> cur.execute("SELECT * FROM users WHERE login LIKE 'j%'")
2L
>>> for data in cur.fetchall():
...    print '%s\t%s' % data
...
john 7000
jane 7001
# 更新表
>>> cur.execute("UPDATE usrs SET uid=7100 WHERE uid=7001")
1L
>>> cur.execute("SELECT * FROM users")
3L
>>> for data in cur.fetchall():
...    print '%s\t%s' % data
...
john     7000
jane     7100
bob      7200
>>> cur.execute('DELETE FROM users WHERE login="bob"')
1L
>>> cur.execute('DROP TABLE users')
0L
>>> cur.close()
>>> cxn.commit()
>>> cxn.close()

         这部分内容并没有什么太大的难度。

         书中的例21.1

#-*-coding: utf-8-*-

import os
from random import randrange as rrange

COLSIZ = 10 # ？
RDBMSs = {'s': 'sqlite', 'm': 'mysql', 'g': 'gadfly'}
DB_EXC = None # 代表数据库异常

# 选择数据库
def setup():
    return RDBMSs[raw_input('''
Choose a database system:

(M)ySQL
(G)adfly
(S)QLite

Enter choice: ''').strip().lower()[0]]

# 依据不同的数据库，创建连接对象
def connect(db, dbName):
    global DB_EXC
    dbDir = '%s_%s' % (db, dbName)

    if db == 'sqlite':
        try: # 尝试载入需要的数据库模型，如果找不到合适的模块，None值被返回，下同。
            import sqlite3 # 尝试载入标准库模块sqlite3
        except ImportError, e:
            try:
                from pysqlite2 import dbapi2 as sqlite3 # 载入sqlite3失败，则选择第三方模块pysqlite2载入
            except ImportError:
                return None

        DB_EXC = sqlite3
        if not os.path.isdir(dbDir): # SQLite是基于文件的数据库系统，需要确认数据库文件所在目录是否存在
            os.mkdir(dbDir) # ？
        cxn = sqlite3.connect(os.path.join(dbDir, dbName))

    elif db == 'mysql':
        try:
            import MySQLdb
            import _mysql_exceptions as DB_EXC
        except ImportError, e:
            return None
       
        try:
            cxn = MySQLdb.connect(db=dbName)
        except _mysql_exceptions.OperationalError, e:
            cxn = MySQLdb.connect(user='root')
        try:
            cxn.query('DROP DATABASE %s' % dbName)
        except DB_EXC.OperationalError, e:
            pass
        cxn.query('CREATE DATABASE %s' % dbName)
        # cxn.query("GRANT ALL ON %s.* to ''@'localhost'" % dbName) # 不注解这一句，会报错MySQL:Access denied for user ''@'localhost' to Database mysql，不解为何
        cxn.commit()
        cxn.close()
        cxn = MySQLdb.connect(db=dbName)
    
    elif db == 'gadfly': # 选这个会报错，具体是gadfly下的bindings.py的syntax error，未解决。
        try:
            from gadfly import gadfly
            DB_EXC = gadfly
        except ImportError, e:
            return None

        try:
            cxn = gadfly(dbName, dbDir)
        except IOError, e:
            cxn = gadfly()
            if not os.path.isdir(dbDir):
                os.mkdir(dbDir)
            cxn.startup(dbName, dbDir)
    else:
        return None
    return cxn

# 创建新的user表
def create(cur):
    try:
        cur.execute('''
CREATE TABLE users (
login VARCHAR(8),
uid INTEGER,
prid INTEGER)
''') # 为啥要用多行字符串，单行感觉也没问题呀。
        # cur.execute('CREATE TABLE users (login VARCHAR(8), uid INTEGER, prid INTEGER)')
    except DB_EXC.OperationalError, e:
        drop(cur) # 删除表
        create(cur) # 递归，重新创建表，但创建失败，会陷入死循环

drop = lambda cur: cur.execute('DROP TABLE users')

# 将NAMES设计成元组，使得能多次利用该元组生成列表供下面的生成器函数
NAMES = (
    ('aaron', 8312), ('angela', 7603), ('dave', 7306),
    ('davina', 7902), ('elliot', 7911), ('ernie', 7410),
    ('jess', 7912), ('jim', 7512), ('larry', 7311),
    ('leslie', 7808), ('melissa', 8602), ('pat', 7711),
    ('serena', 7003), ('stan', 7607), ('faye', 6812),
    ('amy', 7209),
)

def randName(): # 生成器函数
    pick = list(NAMES) # 将NAMES转化成pick，
    while len(pick) > 0:
        yield pick.pop(rrange(len(pick))) # 随机移除pick中的一项

# 在数据库中插一行数据，
def insert(cur, db):
    if db == 'sqlite':
        cur.executemany("INSERT INTO users VALUES(?, ?, ?)", [(who, uid, rrange(1, 5)) for who, uid in randName()])
    elif db == 'gadfly': # gadfly没有executemany方法，只能一行一行地插入数据
        for who, uid in randName():
            cur.execute("INSERT INTO users VALUES(?, ?, ?)", (who, uid, rrange(1, 5)))
    elif db == 'mysql':
        cur.executemany("INSERT INTO users VALUES(%s, %s, %s)", [(who, uid, rrange(1, 5)) for who, uid in randName()])

getRC = lambda cur: cur.rowcount if hasattr(cur, 'rowcount') else -1 # 条件表达式，如果有rowcount属性，则返回最后一次execute操作返回或影响的行数

# 在user表中选几条记录，更改prid字段
def update(cur):
    fr = rrange(1, 5)
    to = rrange(1, 5)
    cur.execute("UPDATE users SET prid=%d WHERE prid=%d" % (to, fr))
    return fr, to, getRC(cur)

# 在user表中选prid字段等于某个值的记录，将它们删除
def delete(cur):
    rm = rrange(1, 5)
    cur.execute("DELETE FROM users WHERE prid=%d" % rm)
    return rm, getRC(cur)

# 从数据库中读取数据，并将数据进行格式化，然后打印
def dbDump(cur):
    cur.execute("SELECT * FROM users") # 读取所有数据
    print '\n%s%s%s' % ('LOGIN'.ljust(COLSIZ), 'USERID'.ljust(COLSIZ), 'PROJ#'.ljust(COLSIZ)) # ljust是将字符串左对齐，COLSIZ是预定的宽度
    for data in cur.fetchall(): # 
        print '%s%s%s' % tuple([str(s).title().ljust(COLSIZ) for s in data]) # 首先是将三列数据转换成字符串，再首字母大写，最后左对齐

def main():
    db = setup()
    print '*** Connecting to %r database' % db
    cxn = connect(db, 'test')
    if not cxn:
        print 'ERROR: %r not supported, exiting' % db
        return
    cur = cxn.cursor()

    print '\n*** Creating users table'
    create(cur)

    print '\n*** Inserting names into table'
    insert(cur, db)
    dbDump(cur)

    print '\n*** Randomly moving folks',
    fr, to, num = update(cur)
    print 'from one group (%d) to another (%d)' % (fr, to)
    print '\t(%d users moved)' % num
    dbDump(cur)

    print '\n*** Randomly choosing group',
    rm, num = delete(cur)
    print '(%d) to delete' % rm
    print '\t(%d users removed)' % num
    dbDump(cur)

    print '\n*** Dropping users table'
    drop(cur)
    cur.close()
    cxn.commit()
    cxn.close()

if __name__ == "__main__":
    main()