Python Database Objects (PDO) 1.2.0 Released

[Bryan J Gudorf]

PDO, an open source python module for interfacing with RDBMS (SQL
databases), has now reached 1.2.0! PDO provides an object oriented API,
similar to that of ADO or JDBC, to python developers. PDO features column
access by name. This new release adds support for the cx_Oracle, DB2 and
adodbapi modules, allowing users to use PDO with a variety of database
systems. 9 different DBAPI modules are now supported, allowing for PDO to
be used with almost any RDBMS. Additional features, such as query
parameters, have also been added in this release, as well as the usual
bug-fixes.

PDO is released under a BSD style license.

PDO supports the following DBAPI python modules and databases:
MySQLdb (supports MySQL)
PySQLite (SQLite)
pgdb (PostgreSQL)
kinterbasdb (Firebird, Interbase)
mxODBC (Many)
pyDB2 (IBM's DB/2)
cx_Oracle (Oracle)
adodbapi (windows only - any RDBMS with an OLE DB provider or ODBC
driver)

As we move forward in this series we will be adding further
functionality
and support. Community support and feedback is appreciated so that we can
make
PDO as useful as possible.
Downloads for Python Database Objects are available on SourceForge.Net
or for more information please visit pdo.neurokode.com.

Bryan J Gudorf
~NeuroKode Labs

 

[Rene Pijlman]

Bryan J Gudorf:

PDO, an open source python module for interfacing with RDBMS (SQL
databases), has now reached 1.2.0!

I just browsed the documentation, but I don't quite understand what
advantages PDO offers over using DBAPI directly. A resultset object looks
very similar to a cursor.

What are the advantages IYO?

 

[Jon Franz]

I'll reply to this, if that's ok. Keep in mind I can't sleep, so
typos may abound.

I guess what PDO comes down to is (an attempt at) ease-of-learning
and ease-of-use without sacrificing power. PDO is built on the
DBAPI, but just provides a (hopefully) higher-level interface.
Having said that, lets dive into the differences between a DBAPI
cursor and a PDO Resultset.

A Resultset is actually very different from a cursor - The first and
most blatant difference being that with a Resultset, you get column
access by name.

With a DBAPI cursor, you create the cursor first, then perform an
execute on the cursor, then do a fetchXXX (depending upon
if you want one row at a time or many, or all). The data returned
is separate from the cursor, and is a sequence of sequences.
If you do a fetchmany or fetchall, iteration over the results is
your responsibility.
Also, data about the result columns is stored separately
from the columns themselves, in a .description field of the cursor.

PDO Resultset objects are created with the same statement
in which you perform your query, no manual cursor creation required
beforehand.
PDO Resultsets also allow for easy transversal of the results, in a
forwards or backwards or random manner, via the .move(x), .next(),
..prev(), .moveto(n) and other methods. Each method will return 0 if
the destination is out-of-bounds.
Data about the columns is accessed as if it were intrinsic to the
columns - thus Resultset columns in PDO are objects with member
variables.

Here's a quick example of a simple query and loop-over results, first
via DBAPI, then via PDO.

DBAPI:
import MySQLdb

mycon = MySQLdb(user='test', passwd='foobar', db='sample')
mycursor = mycon.cursor()
mycursor.execute("SELECT * FROM Customers")
results = mycursor.fetchall()
for row in range(0, len(results)):
print "Name: " + row[1]
print "Address: " + row[3]
print "Size of 'Name' column in the db: " +
str(mycursor.description[1][3])

------------
PDO:
import pdo

mycon = pdo.connect("module=MySQLdb;user=test;passwd=foobar;db=sample")
results = mycon.open("SELECT * FROM Customers")
while results.next():
print "Name: " + results['Name'].value
print "Address: " + results['Address'].value
print "Size of 'Name' column in the db: " + str(results['Name'].length)

--------------------------------
~Jon Franz
NeuroKode Labs, LLC

----- Original Message -----
From: "Rene Pijlman" <[email protected]>
To: <[email protected]>
Sent: Monday, November 17, 2003 2:27 AM
Subject: Re: Python Database Objects (PDO) 1.2.0 Released

 

[Rene Pijlman]

Jon Franz:

A Resultset is actually very different from a cursor - The first and
most blatant difference being that with a Resultset, you get column
access by name.

Thanks Jon, that clarifies it.

 

[Geoff Howland]

A Resultset is actually very different from a cursor - The first and
most blatant difference being that with a Resultset, you get column
access by name.

With a DBAPI cursor, you create the cursor first, then perform an
execute on the cursor, then do a fetchXXX (depending upon
if you want one row at a time or many, or all). The data returned
is separate from the cursor, and is a sequence of sequences.

I havent looked at the other DBAPI implementations lately, but with
MySQLdb you can set the cursor type to DictCursor, and then you get
back a sequence of dictionaries with the field names as the dict keys.


-Geoff Howland
http://ludumdare.com/

 

[Jon Franz]

I havent looked at the other DBAPI implementations lately, but with

MySQLdb you can set the cursor type to DictCursor, and then you get
back a sequence of dictionaries with the field names as the dict keys.

This is true, but unfortunately it isn't standard behavior according to the
DBAPI 2.0 spec (pep 249).
Since it isn't standard, it seems everyone that does provide it
in the module provides it a different way.- if they provide it at all.

With PDO you get this functionality everytime, with no need to change
your code when you switch databases and DBAPI modules to get it.
Plus, PDO's implementation of acccess-by-name uses less memory
than a sequence of mapping objects - excluding the case of very small
(1-3 record) results, that is. I can exapnd upon how/why if people
are curious.

cheers.

~Jon Franz
NeuroKode Labs, LLC

 

[Geoff Howland]

This is true, but unfortunately it isn't standard behavior according to the
DBAPI 2.0 spec (pep 249).
Since it isn't standard, it seems everyone that does provide it
in the module provides it a different way.- if they provide it at all.

With PDO you get this functionality everytime, with no need to change
your code when you switch databases and DBAPI modules to get it.
Plus, PDO's implementation of acccess-by-name uses less memory
than a sequence of mapping objects - excluding the case of very small
(1-3 record) results, that is. I can exapnd upon how/why if people
are curious.

Sounds good, if you can update/insert back into the DB by the same
dictionary then I'm sold. I'll go check it out.


-Geoff Howland
http://ludumdare.com/

 

[Serge Orlov]

[Jon Franz]

PDO:
import pdo

mycon = pdo.connect("module=MySQLdb;user=test;passwd=foobar;db=sample")
results = mycon.open("SELECT * FROM Customers")
while results.next():
print "Name: " + results['Name'].value
print "Address: " + results['Address'].value
print "Size of 'Name' column in the db: " + str(results['Name'].length)

Why don't you use iterators for that?
for result in mycon.open("SELECT * FROM Customers"):
print ...

Besides you're even "abusing" .next() method which you use to provide
iterations in a different way. Confusing...

-- Serge Orlov.

 

[Jon Franz]

while results.next():

print "Name: " + results['Name'].value
print "Address: " + results['Address'].value
print "Size of 'Name' column in the db: " +

str(results['Name'].length)

Why don't you use iterators for that?
for result in mycon.open("SELECT * FROM Customers"):
print ...

Individual rows are not objects in PDO.

Your idea for an iterator would call for something to be returned from
the .open(), such as a sequence of dictionaries, which would remove
the coupling of column values from the column description data.
Alternately, it would cause a repetition of the data (or at least many
extra references to it).
Keeping the descriptive info with the data (at least as far as how it's
accessed) was a design goal.

Besides you're even "abusing" .next() method which you use to provide
iterations in a different way. Confusing...

How is it abusive to use it the way it was intended?

It may help to quit thinking of a Resultset as a sequence of dictionaries -
PDO explicitly avoids that. The call to .next() is updating an internal
counter within the Resultset object, and returning a value based upon
whether the new position was in-bounds or not.


cheers.

~Jon Franz
NeuroKode Labs, LLC

 

[Serge Orlov]

while results.next():
print "Name: " + results['Name'].value
print "Address: " + results['Address'].value
print "Size of 'Name' column in the db: " +

str(results['Name'].length)

Why don't you use iterators for that?
for result in mycon.open("SELECT * FROM Customers"):
print ...

Individual rows are not objects in PDO.

Your idea for an iterator would call for something to be returned from
the .open(), such as a sequence of dictionaries, which would remove
the coupling of column values from the column description data.
Alternately, it would cause a repetition of the data (or at least many
extra references to it).
Keeping the descriptive info with the data (at least as far as how it's
accessed) was a design goal.

Yes, if the .open() is an generator then it must return a sequence of items
but only one at a time. If the loop body doesn't keep the result object
it will be garbage collected pretty soon. You don't need to return
a dictionary you can return a special "coupler" object that will bind
the column description data (created only one time) with the column
values. Of course, it means one more allocation per row and extra
references, but I don't really think it's very expensive. After all it is
idiomatic iteration over a sequence. Without hard data to prove
that it's really expensive I don't think it's right to say it's expensive.

How is it abusive to use it the way it was intended?

Sorry about my wording, you're using it as inteded of course, but when
I see any method with the name .next() used for iteration I immediately
think about python iterators. Then I realized I was wrong.

It may help to quit thinking of a Resultset as a sequence of dictionaries -
PDO explicitly avoids that.

Isn't it premature optimization?

-- Serge Orlov.

 

[Wilk]

while results.next():
print "Name: " + results['Name'].value
print "Address: " + results['Address'].value
print "Size of 'Name' column in the db: " +

str(results['Name'].length)

Why don't you use iterators for that?
for result in mycon.open("SELECT * FROM Customers"):
print ...

Individual rows are not objects in PDO.

Why did'nt you choose to make row and field objects ?