[DB-SIG] Python db programming conventions

[Vernon Cole]

On Sun, Nov 21, 2010 at 8:42 PM, Andy Dustman <farcepest at gmail.com> wrote:

>
>
> You never actually set sql anywhere, so you'll always get a NameError
> instead of IOError. It would probably be better to not catch the
> exception at all in this function.
> --
> Question the answers
> _______________________________________________
>

Thank you, Andy!

I missed that. That's why open source code is a good thing. More eyes
looking finds problems.
This time I actually entered the code into a file and executed it.  A
working version follows:

<code>
from __future__ import print_function
import sqlite3
import sys
def createdb(db):
    try:
        con = sqlite3.connect(db)
        cur = con.cursor()
        sql = '''
           CREATE TABLE t1
           (
               kid INTEGER PRIMARY KEY,
               c1 TEXT,
               c2 TEXT
           )'''
        cur.execute(sql)

        sql = '''
           CREATE TABLE t2
           (
               kid INTEGER PRIMARY KEY,
               c1 TEXT,
               c2 TEXT
           )'''
        cur.execute(sql)

        sql =  '''
           CREATE TABLE t3
           (
               kid INTEGER PRIMARY KEY,
               c1 TEXT,
               c2 TEXT
           )'''
        cur.execute(sql)

        con.commit()
    except sqlite3.Error:
        print("ERROR: createdb did not commit.", file=sys.stderr)
        print("tried this sql:", file=sys.stderr)
        print(sql, file=sys.stderr)
        raise
    finally:
        cur.close()
        con.close()

if __name__ == '__main__':
    createdb('myDbName')
</code>

This script will run once without errors.
If you run it a second time, you get:

<console output>
ERROR: createdb did not commit.
tried this sql:

           CREATE TABLE t1
           (
               kid INTEGER PRIMARY KEY,
               c1 TEXT,
               c2 TEXT
           )
Traceback (most recent call last):
  File "x.py", line 44, in <module>
    createdb('myDbName')
  File "x.py", line 14, in createdb
    cur.execute(sql)
sqlite3.OperationalError: table t1 already exists
</console output>

Which is exactly what you should expect.  Note that using the naked "raise"
statement results in a higher quality traceback with error text pinpointing
the problem.

I used print as a function, so this example should work in python versions
from 2.6 to 3.2.
--
Vernon
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/db-sig/attachments/20101122/bbd048ea/attachment.html>