Databases: Getting values by column name

[Robert Ferber]

Hi,

I can't find any good documentation about the Python-database module, all I
found was this rudimentary (no examples, no references) piece here:

http://www.python.org/peps/pep-0249.html

Anyway, this and also this tutorial here:

http://www.devshed.com/c/a/Python/MySQL-Connectivity-With-Python/2/

only use database rows as sequences, ie as arrays with numerical index.
This gives you loads of problems when using "select *" and also bad code
readability when selecting specific columns.

Is there a way to use them as dictionaries, ie with Index-Strings (=column
names) as indexes?

BTW, is there any good searchable reference to Python's functions, with
examples and cross-references to similar functions?

Thanks a lot,

Robert

 

[Skip Montanaro]

Robert> http://www.devshed.com/c/a/Python/MySQL-Connectivity-With-Python/2/

Robert> only use database rows as sequences, ie as arrays with numerical
Robert> index. This gives you loads of problems when using "select *"
Robert> and also bad code readability when selecting specific columns.

Robert> Is there a way to use them as dictionaries, ie with
Robert> Index-Strings (=column names) as indexes?

Yes, for MySQLdb import the MySQLdb.cursors module then set the cursorclass
arg when you create a connection, like so:

import MySQLdb
import MySQLdb.cursors
...
conn = MySQLdb.Connection(host=...,
user=...,
passwd=...,
db=...,
cursorclass=MySQLdb.cursors.DictCursorNW,
...)

I think the reason this sort of behavior isn't spelled out in PEP 249 is
because it varies too much from one database to another. Psycopg (a
PostgreSQL adaptor) does it differently. Instead of specifying the cursor
type when the connection is created, you instantiate a different cursor
class:

conn = psycopg.connect("...")
...
cursor = conn.dictcursor()

I imagine there are some databases that either don't support name-based
retrieval very well (or at all), or make it difficult to get at. You might
get a more solid response on the reasons for this omission from PEP 249 from
the db-sig folks:

http://www.python.org/sigs/db-sig/

Skip

 

[Robert Ferber]

Yes, for MySQLdb import the MySQLdb.cursors module then set the
cursorclass arg when you create a connection, like so:

import MySQLdb
import MySQLdb.cursors
...
conn = MySQLdb.Connection(host=...,
user=...,
passwd=...,
db=...,
cursorclass=MySQLdb.cursors.DictCursorNW,
...)

You mean MySQLdb.connect()?

I think the reason this sort of behavior isn't spelled out in PEP 249 is
because it varies too much from one database to another. Psycopg (a
PostgreSQL adaptor) does it differently. Instead of specifying the cursor
type when the connection is created, you instantiate a different cursor
class:

conn = psycopg.connect("...")

NameError: name 'psycopg' is not defined

Also wouldn't that overwrite the above conn variable?

...
cursor = conn.dictcursor()

AttributeError: Connection instance has no attribute 'dictcursor'

(When I use the conn from MySQLdb.connect)

I imagine there are some databases that either don't support name-based
retrieval very well (or at all), or make it difficult to get at. You
might get a more solid response on the reasons for this omission from PEP
249 from the db-sig folks:

http://www.python.org/sigs/db-sig/

Isn't there any documentation with a simple example out there?

I already know the stuff on that page, it's really slowing you down :-(

Robert

 

[Istvan Albert]

Robert Ferber wrote:

I already know the stuff on that page, it's really slowing you down :-(

Your response to Skip Montaro's post demonstrates that
you zipped trough it without paying much attention to what
he said, so I think slowing a bit down would actually help
you here.

As other pointed out you have everything you need, using dtuple.py at:

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/81252

or db_row at:

http://opensource.theopalgroup.com/

Istvan.

 

[Robert Ferber]

Robert Ferber wrote:



Your response to Skip Montaro's post demonstrates that
you zipped trough it without paying much attention to what
he said, so I think slowing a bit down would actually help
you here.

I am already looking for that solution for several days already, I'm quite a
bit frustrated by now, sorry for my inpoliteness.

However Skip's solution almost works, at least the import statements and the
connect statement with cursorclass=MySQLdb.cursors.DictCursorNW don't
report an error.

As other pointed out you have everything you need, using dtuple.py at:

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/81252

Of course I looked at that webpage, but if I'm not mistaken, mx.ODBC.Windows
is only available on Windows which makes it useless for me, I can not find
a ODBC Package for my Linux distribution and the webpage doesn't give the
slightest hint what the package name is (which probably would make
searching for it too easy) or where to get it.

My distribution (SUSE) comes with dozens of Python-packages, but none have
"mx" or "odbc" in their name.

So after wasting another 20 minutes I discarded that webpage as useless and
responded to the only post that looked promising which was Skip's.

or db_row at:

http://opensource.theopalgroup.com/

http://opensource.theopalgroup.com/files/ADOdb.html
Purpose: Python DB-API 2.0 interface to Microsoft ADO objects
Compatibility: Python 2.0
Requires: Python Win32 extensions, ADO 2.5+, mx.DateTime
Revision: Version 0.10 - Alpha quality code

I didn't know that the Python-community was infested by so much
Microsoft-only code. Both of your hints seem to be Win-only. And it's
"Alpha quality".

Robert

 

[Skip Montanaro]

conn = MySQLdb.Connection(host=...,

Robert> You mean MySQLdb.connect()?

MySQLdb has Connect, connect and Connection, all the same function.

Robert> NameError: name 'psycopg' is not defined

Yes, you'd have to import it.

Robert> Also wouldn't that overwrite the above conn variable?

Robert> AttributeError: Connection instance has no attribute 'dictcursor'

Like I said, psycopg is an adapter for the PostgreSQL database. MySQLdb is
an adapter for the MySQL database. There are others as well. I thought
showing how the two I'm most familiar with in this arena differ would be
sufficient.

Robert> I already know the stuff on that page, it's really slowing you
Robert> down :-(

I don't understand what you're referring to here. What do you mean by
"slowing you down"?

Skip

 

[Istvan Albert]

Robert Ferber wrote:

Of course I looked at that webpage, but if I'm not mistaken, mx.ODBC.Windows
is only available on Windows which makes it useless for me, I can not find
a ODBC Package for my Linux distribution and the webpage doesn't give the
slightest hint what the package name is (which probably would make
searching for it too easy) or where to get it.

The dtuple.py module is database independent and will work with just
about all database connectors.

The fact that the example uses mx.ODBC.Windows is completely
irrelevant it will work the same way with every other connector.

> I didn't know that the Python-community was infested by so much
> Microsoft-only code. Both of your hints seem to be Win-only. And it's
>" Alpha quality".

Heh, while I would agree that the formatting on the page
is both slightly misleading and does not visually separate the
different downloads well, you'd save yourself a lot of aggravation
just by spending a little more time on each of these pages.

The module that I (and others) talked about is called db_row and
is located right above the on one you talk about (the windows
specific database *driver*).

Istvan.

 

[Sibylle Koczian]

Of course I looked at that webpage, but if I'm not mistaken, mx.ODBC.Windows
is only available on Windows which makes it useless for me, I can not find
a ODBC Package for my Linux distribution and the webpage doesn't give the
slightest hint what the package name is (which probably would make
searching for it too easy) or where to get it.

I can see no reason why the recipe shouldn't work just as well with
MySQLdb instead of mx.ODBC. And the docstrings for the dtuple classes
state clearly that they work with results "from a DB-API fetch*()
method". No mention of a specific package here.

In fact, the docstring for TupleDescriptor even says "Note: the term
database tuple is rather specific; in actuality the tuple may have come
from non-database sources and/or generated by a process wholly unrelated
to databases."

My distribution (SUSE) comes with dozens of Python-packages, but none have
"mx" or "odbc" in their name.

But it does come with MySQLdb IIRC.

HTH
Koczian