Python下的SQlite数据库
Python2.5集成了pysqlite数据库接口程序（sqlite3 模块），这是Python标准库第一次将一个数据库接口程序纳入标准库。SQLite操作的例子：
>>> import sqlite3
>>> cxn = sqlite3.connect('sqlite.db')
>>> cur = cxn.cursor()
>>> cur.execute('CREATE TABLE users(login VARCHAR(8), uid INTEGER)')
<sqlite3.Cursor object at 0x7f176e186710>
>>> cur.execute('INSERT INTO users VALUES("john", 100)')
<sqlite3.Cursor object at 0x7f176e186710>
>>> cur.execute('INSERT INTO users VALUES("jane", 110)')
<sqlite3.Cursor object at 0x7f176e186710>
>>> cur.execute('SELECT * FROM users')
<sqlite3.Cursor object at 0x7f176e186710>
>>> for eachUser in cur.fetchall():
... print eachUser
...
(u'john', 100)
(u'jane', 110)
>>> cur.execute('DROP TABLE users')
<sqlite3.Cursor object at 0x7f176e186710>
>>> cur.close()
>>> cxn.commit()
>>> cxn.close()
#!/usr/bin/env pythonimport osfrom random import randrange as rrangeCOLSIZ = 10DB_EXC = Nonedef connect(dbDir, dbName): global DB_EXC try: import sqlite3 except ImportError, e: try: from pysqlite2 import dbapi2 as sqlite3 except ImportError, e: return None DB_EXC = sqlite3 if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn = sqlite3.connect(os.path.join(dbDir, dbName)) return cxndef create(cur): try: 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 = ( ('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) while len(pick) > 0: yield pick.pop(rrange(len(pick)))def insert(cur): cur.executemany("INSERT INTO users VALUES(?, ?, ?)", [(who, uid, rrange(1,5)) for who, uid in randName()]) getRC = lambda cur: cur.rowcount if hasattr(cur, 'rowcount') else -1def 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)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 '%s%s%s' % ('LOGIN'.ljust(COLSIZ), 'USERID'.ljust(COLSIZ), 'PROJ#'.ljust(COLSIZ)) for data in cur.fetchall(): print '%s%s%s' % tuple([str(s).title().ljust(COLSIZ) \ for s in data])def main(): print '*** Connecting to sqlite database' cxn = connect('sqlitedir', 'test.db') if not cxn: print 'ERROR: %r not supported, exiting' % db return cur = cxn.cursor() print '*** Creating users table' create(cur) print '*** Inserting names into table' insert(cur) dbDump(cur) print '*** 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 '*** Randomly choosing group', rm, num = delete(cur) print '(%d) to delete' % rm print '\t(%d users removed)' % num dbDump(cur) print '*** Dropping users table' drop(cur) cur.close() cxn.commit() cxn.close()if __name__ == '__main__': main(）