SQlite数据库操作

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 python


import os
from random import randrange as rrange


COLSIZ = 10
DB_EXC = None


def 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 cxn


def 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 -1


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)


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(）