//兼容2、3，使用Mysql,sqlite，gadfly
image.png
image.png
image.png
image.png
image.png
image.png
image.png
image.png
//py2版本
!/usr/bin/env python
import os from random import randrange as rand COLSIZ = 10 FIELDS = ('login', 'userid', 'projid') RDBMSs = {'s': 'sqlite', 'm': 'mysql', 'g': 'gadfly'} DBNAME = 'test' DBUSER = 'root' DB_EXC = None NAMELEN = 16 tformat = lambda s: str(s).title().ljust(COLSIZ) cformat = lambda s: s.upper().ljust(COLSIZ) def setup(): return RDBMSs[raw_input(''' Choose a database system: (M)ySQL (G)adfly (S)QLite Enter choice: ''').strip().lower()[0]] def connect(db): global DB_EXC dbDir = '%s_%s' % (db, DBNAME) if db == 'sqlite': try: import sqlite3 except ImportError: try: from pysqlite2 import dbapi2 as sqlite3 except ImportError: return None DB_EXC = sqlite3 if not os.path.isdir(dbDir): 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: return None try: cxn = MySQLdb.connect(db=DBNAME) except DB_EXC.OperationalError: try: cxn = MySQLdb.connect(user=DBUSER) cxn.query('CREATE DATABASE %s' % DBNAME) cxn.commit() cxn.close() cxn = MySQLdb.connect(db=DBNAME) except DB_EXC.OperationalError: return None elif db == 'gadfly': try: from gadfly import gadfly DB_EXC = gadfly except ImportError: return None try: cxn = gadfly(DBNAME, dbDir) except IOError: cxn = gadfly() if not os.path.isdir(dbDir): os.mkdir(dbDir) cxn.startup(DBNAME, dbDir) else: return None return cxn def create(cur): try: cur.execute(''' CREATE TABLE users ( login VARCHAR(%d), userid INTEGER, projid INTEGER) ''' % NAMELEN) except DB_EXC.OperationalError: 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), ('mona', 7404), ('jennifer', 7608), ) def randName(): pick = set(NAMES) while pick: yield pick.pop() def insert(cur, db): if db == 'sqlite': cur.executemany("INSERT INTO users VALUES(?, ?, ?)", [(who, uid, rand(1,5)) for who, uid in randName()]) elif db == 'gadfly': for who, uid in randName(): cur.execute("INSERT INTO users VALUES(?, ?, ?)", (who, uid, rand(1,5))) elif db == 'mysql': cur.executemany("INSERT INTO users VALUES(%s, %s, %s)", [(who, uid, rand(1,5)) for who, uid in randName()]) getRC = lambda cur: cur.rowcount if hasattr(cur, 'rowcount') else -1 def update(cur): fr = rand(1,5) to = rand(1,5) cur.execute("UPDATE users SET projid=%d WHERE projid=%d" % (to, fr)) return fr, to, getRC(cur) def delete(cur): rm = rand(1,5) cur.execute('DELETE FROM users WHERE projid=%d' % rm) return rm, getRC(cur) def dbDump(cur): cur.execute('SELECT * FROM users') print 'n%s' % ''.join(map(cformat, FIELDS)) for data in cur.fetchall(): print ''.join(map(tformat, data)) def main(): db = setup() print '*** Connect to %r database' % db cxn = connect(db) if not cxn: print 'ERROR: %r not supported or unreachable, exiting' % db return cur = cxn.cursor() print 'n*** Create users table (drop old one if appl.)' create(cur) print 'n*** Insert names into table' insert(cur, db) dbDump(cur) print 'n*** Move users to a random group' fr, to, num = update(cur) print 't(%d users moved) from (%d) to (%d)' % (num, fr, to) dbDump(cur) print 'n*** Randomly delete group' rm, num = delete(cur) print 't(group #%d; %d users removed)' % (rm, num) dbDump(cur) print 'n*** Drop users table' drop(cur) print 'n*** Close cxns' cur.close() cxn.commit() cxn.close() if name == 'main': main()
