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

Why Python Needs Perl's use strict

Having been playing with Python again after a couple of years away from it I had a good bunch of people to teach it too, and they had some good questions.  One of which came up during exceptions.

Now we all know that Python allows you to create variables on the fly and to declare them you must assign to them first;
myvar="hello world"

But imagine the following scenario where you have declared your variable such as a file handle to open a file, but you have used an exception to capture the fact that the file does not exist and in your except section have a typo for the file handle object.



try:
        fh=open("usestrict")
        for x in fh:
                print x,"\n"
except IOError:
        print "File could not be opened"
        ofh.close()

ofh.close()

So the Python Guy had a post at the following;

http://pythonguy.wordpress.com/2008/09/18/perl-really-sucks-and-they-dont-even-realize-it/

Well, clearly from the above code I would not get a compile error and my exception would happen and even if I didn't get the exception my my code would run and when it came to closing the file I'd get a NameError exception from Python.

Are you sure Python doesn't need a use strict?  Are Python developers that confident that they don't make typos?