mysql.py

#!/usr/bin/env python


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:
              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))
  
      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',passwd ='y123456r')
          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)
          cxn.commit()
          cxn.close()
          cxn = MySQLdb.connect(db=dbName)
  
      elif db == 'gadfly':
          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
  
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, db):
     if db == 'sqlite':
         cur.executemany("INSERT INTO users VALUES(?, ?, ?)",
                 [(who, uid, rrange(1, 5)) for who, uid in randName()])
     elif db == 'gadfly':
         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

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 login, uid, prid FROM users')
     print '\n%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():
     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 on 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*** Drop users table'
     drop(cur)
     cur.close()
     cxn.commit()
     cxn.close()
 
if __name__ == '__main__':
     main()