Questions to DB-API 2

[Steve Holden]

"Jakob Simon-Gaarde" <jsgaarde at tdcspace.dk> wrote ...
> Quiestions to DB-API 2
>
> 1. In DB-API 1 using the odbc module I was able to use question-signs
> to format my SQL, thus things like pings around strings and date
> formats was automatically taken care of. I would write something like
> this:
>
> cur.execute("insert into table (text,number,date) values (?,?,?)",
> (strvar,numvar,datevar))
>
> Unfortunately this doesen't seem to work in DB-API 2 but since I don't
> expect that it is a degrade compared to DB-API 1 I guess there must be
> some other way.

The format for parameter substitution varies with the specific DB-API module
you use, which in turn often depends on the underlying database engine. In
the case of an ODBC driver I'm surprised that format isn't supported. The
mxODBC module supports question-mark parameterization, for example.

Each package sets a "paramstyle" attribute that tells you how to
parameterize. So far you've used paramstyle "qmark". The other values are
"numeric" (:1, :2, etc), "named" (:fn, :ln, etc), "format" (%s - MySQLdb
uses this one) and "pyformat" (%(fn)s, %(ln)s, etc).

> Can anybody help please. Is there a tutorial to db-api 2 with usage
> examples, because the description on python.org/topics/db isn't very
> informative in the respects of usage.
>
Well, short of reading Chapters 10 and 11 of "Python Web Programming" I'm
not sure. I wrote those chapters because there wasn't that much information
around, but the more you use the code the less perplexing it becomes. And
comp.lang.python can help (as I hope this answer demonstrates).

> 2. For debug reasons I would really like if I could print the full
> SQL-statement when a database exception occures, is this possible? I
> can't see any attributes with on the exception classes.
>
The best way to do this is to write a wrapper function to trap errors at the
correct level. E.g. (untested):

def execute(cur, sql, *args):
    try:
        cur.execute(sql, args)
    except yourmoduledbexception, msg:
        print "The SQL statement", sql, "gave the following error:", msg

Not exactly wonderful, but I hope it gives you the idea. Much easier than
trying to read the entrails of an unknown DP-API module after it's barfed on
your SQL.

regards
--
Steve Holden                                  http://www.holdenweb.com/
Python Web Programming                 http://pydish.holdenweb.com/pwp/
Register for PyCon now!            http://www.python.org/pycon/reg.html