Databases: Getting values by column name

[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