Python database access questions

[David M. Cook]

In article <aa18a5ab.0403120319.9b76dc1 at posting.google.com>, Limey wrote:

> ADO allows you to reference field objects when manipulating records
> retrieved from the database, this makes your code less likely to be
> broken by reordering of the columns of say a "select * from table"
> query, it also allows you to get information on the datatypes of the
> fields and so on.

Some of the dbapi compliant modules (e.g. pyPgSQL) return row objects that
can be queried by position or field name.  Some (psycopg) provide dictfetch*
methods that return rows as dictionaries instead of tuples.  And converting
the rows to dictionaries is trivial anyway:

cursor.execute(blah)
col_names = [tup[0] for tup in cursor.description]
dict_results = [dict(zip(col_names, row)) for row in cursor.fetchall()]

> ADO also provides a safer way to pass information when using parameter
> queries or stored procedures, you have a parameter object that you can
> assign parameter values to and any problems such as "' " /" within the
> string are then handled by the objects to prevent breakage of the sql
> statement passed to the RDBMS.

All the postgres dbapi modules can interpolate input using pyformat:

data = {'name' : 'Dobbs, Bob', 'phone_no' : '555-1212'}
cursor.execute("""INSERT INTO contact (name, phone_no) 
                  VALUES (%(name)s, %(phone_no)s)""", data)

(You can have more keys in your dict than in the query, they will be ignored,
but not too few, obviously.)

> ADO allows you to create a "recordset" that allwos you to iterate
> through the records and columns and also then assign values to the
> field objects within and update these changes with method calls, it
> seems all this type of stuff needs to be manually done with DB-API
> i.e. you would have to create all the DML statements for this.

There are various modules that sit on top of the DBAPI that add similar
features.  I wrote my own. Python string handling makes it fairly easy.

You might want to investigate SQLObject or other object-relational mappers:

http://www.thinkware.se/cgi-bin/thinki.cgi/ObjectRelationalMappersForPython

Dave Cook