Python database access questions

[Limey]

Hi All,

I want to write an application that will talk to a RDBMS.

The application needs to be platform neutral from both an operating
system and database backend point of view.

I have decided to create a prototype in Python, the RDBMS will be
PostgreSQL running on a Linux box.

I am from a Microsoft background and I now take for granted components
such as ADO (Activex Data Objects) that make database access simple
and portable.

I have been looking at the Python DB-API modules and this seems to
make it relatively simple to access a RDBMS but it seems to be at a
lower level than say ADO and I feel this may potentially cause me more
work to prevent potential problems that I may encounter.

Let me explain...

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.

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.

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.

What are your views on this, it may just be me being naive as I only
know of the Microsoft way of doing things ;-), you may find
technologies such as ADO cumbersome and/or restrictive ?

Any views on this matter would be greatly appreciated :-)

[David M. Cook]

Re: Python database access questions

In article <aa18a5ab.04031 20319.9b76dc1@p osting.google.c om>, Limey wrote:
[color=blue]
> 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.[/color]

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.descript ion]
dict_results = [dict(zip(col_na mes, row)) for row in cursor.fetchall ()]
[color=blue]
> 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.[/color]

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.)
[color=blue]
> 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.[/color]

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