Python DB Abstraction

Whilst teaching Python this week on the 29 Apr - 2 May 2013, I decided to show those on the course how to make a script that would work between different databases, whilst keeping the amount of code in the main program to a minimum.

The program makes use of conditional import through Python's try/except capability, and uses the alias of module import to ensure that the abstract layer for the database calls are called by the same name.

Main Program

#!/usr/bin/python

import sys

if len(sys.argv) < 1:
  sys.stderr.write("Usage error\n")
  sys.stderr.write("Usage: "+sys.argv[0]+" dbtype sqliteFile\n")

# User chooses mysql
if sys.argv[1] == 'mysql':
  try:
    import MySQLdb
    import mysqlcond as actions  # Ensure that the module namespace is actions
    pyconn = MySQLdb.connect(host="localhost",user="root",db="abc")
  except:
    print "MySQLdb does not exist"
    sys.exit(1)
else:
  try:
    import sqlite3
    import sqlitecond as actions

    pyconn = sqlite3.connect(sys.argv[2])
  except:
    sys.stderr.write("SQLite does not exist\n")
    sys.exit(1)

pycur=pyconn.cursor()
myList=[2,'Shil','Steve','abc@xyz.com','blah blah']
actions.build(pycur)
actions.insert(pycur,myList)
actions.list(pycur)
pyconn.commit()
pyconn.close()


SQLite DB code layer "sqlitecond.py"
def build(pycur):
  sqlstmnt='''
        CREATE TABLE abook (
                id int primary key,
                surname varchar(50),
                firstname varchar(50),
                email varchar(150),
                notes text
        )
'''
  pycur.execute(sqlstmnt)

def insert(pycur,values):
  sql="INSERT INTO abook VALUES(?,?,?,?,?)"
  pycur.execute(sql,values);

def list(pycur,table=None,fields=None,values=None):
  sql="SELECT * FROM ",table
  pycur.execute(sql)
  return pycur.fetchall()  



MySQL DB code layer "mysqlcond.py"
def build(pycur):
  sqlstmnt='''
        CREATE TABLE abook (
                id int primary key,
                surname varchar(50),
                firstname varchar(50),
                email varchar(150),
                notes text
        ) ENGINE = INNODB;
'''
  pycur.execute(sqlstmnt)

def insert(pycur,values):
  sql="INSERT INTO abook (id,surname,firstname,email) VALUES(?,?,?,?)"
  pycur.execute(sql,values);

def list(pycur,table=None,fields=None,values=None):
  sql="SELECT * FROM ",table
  pycur.execute(sql)
  return pycur.fetchall()