Can I combine python's `for` statement with SQL like this: `for id, name, ctime in db.select('table_name', where='...')`

Question

So I'm accessing MySQL from python 2.7 scripts, now I can do this:

for id, name, area in db.select('SELECT id, name, area'
                     ' FROM some_table'
                     ' WHERE area IS NOT NULL'):
    print id, name, area

But the duplicate variables id, name, area of the for statement and those in the select statement are eating me up. Assuming the db column names can be used as variable names, I want something like this:

for id, name, area in db.select(from='sometable', where='area IS NOT NULL'):
    print id, name, area

Of course the variables in the for statement must be dynamically passed into db.select, so that I can change it without changing db.select.

If you don't want to do SQL, have you looked at e.g. SQLAlchemy? — Fredrik Håård, Jul 15 '13 at 09:30
And how is the `db.select()` function (which is executed first) supposed to know what variable names you are going to assign the values *to*? The target names are completely separate. — Martijn Pieters, Jul 15 '13 at 09:30
Why not use a dictionary row factory instead? See [Python: use mysqldb to import a MySQL table as a dictionary?](http://stackoverflow.com/q/2180226) — Martijn Pieters, Jul 15 '13 at 09:32
@MartijnPieters That seems very close. Yes, if I knew a way to make `db.select` somehow know those variables, I would be able to do the rest myself. — xiaofeng.li, Jul 15 '13 at 09:38

score 1 · Accepted Answer · answered Jul 15 '13 at 10:49

The one solution in the comment, suggesting to use a dictionary row factory, seems very close to what you want.

Even closer (as easier to write) seems to me a namedtuple. For this purpose, I once wrote this:

def namtupiter(c):
    from collections import namedtuple
    fields = tuple(i[0] for i in c.description)
    Row = namedtuple('Row', fields)
    # make Row a tuple and a "dict" (well, kind of...) at the same time.
    # Don't lose tuple property, so only process strings and pass everything
    # other to super().
    Row.__getitem__ = lambda self, item: getattr(self, item) if isinstance(item, basestring) else super(Row, self).__getitem__(item)
    for i in c:
        try:
            # try to access i as a dict
            yield Row(*(i[f] for f in fields))
        except TypeError:
            # it is no dict -> try tuple
            yield Row(*i)

class CursorNTRowsMixIn(object):
    _fetch_type = 0 # tuples
    def _do_get_result(self):
        super(CursorNTRowsMixIn, self)._do_get_result()
        # create a named tuple class
        from collections import namedtuple
        if self.description:
            self.RowClass = namedtuple('Row', tuple(i[0] for i in self.description))
    def _fetch_row(self, size=1):
        rows = super(CursorNTRowsMixIn, self)._fetch_row(size)
        # turn every row into a Row().
        return tuple(self.RowClass(*i) for i in rows)

class NTCursor(CursorStoreResultMixIn, CursorNTRowsMixIn,
               BaseCursor):
    pass

class SSNTCursor(CursorUseResultMixIn, CursorNTRowsMixIn,
               BaseCursor):
    pass

With the namtupiter(), you can iterate over a cursor containing a resultset and receive NamedTuples with the DB fields contained as attributes.

So you can do

for r in namtupiter(db.select(fields=('id', 'name', 'area', _from='sometable', where='area IS NOT NULL')):
    print r.id, r.name, r.area

Another way is the (SS)NTCursor which can be seen as an alternative to the existing cursors which provide tuples or dicts. These new cursors as well provide the rows as named tuples with the name information extracted from the result set.

score 0 · Answer 2 · answered Jul 16 '13 at 09:27

The following example is not using namedtuple. I'm using mysql.connector, but can be done with whatever database driver:

import mysql.connector

CONFIG = {
    'database': 'test',
    'user': 'root',
}

class MySQLStackOverflowConnect(mysql.connector.MySQLConnection):
    def select(self, table, where, fields=None):
        if isinstance(fields, (list, tuple)):
            select_fields = ', '.join(fields)
        else:
            select_fields = '*'
        query = "SELECT {fields} FROM `{table}` WHERE {where}".format(
            table=table, where=where, fields=select_fields)
        cur = self.cursor(buffered=True)
        cur.execute(query)
        return cur

cnx = MySQLStackOverflowConnect(**CONFIG)
for userid, username in cnx.select(table='users', where='id=1',
                                   fields=('id', 'username')):
    print userid, username
cnx.close()

(Careful for SQL-injection with passing WHERE-clauses like that)

With Python 3, you would be able to do the last for-loop, provided the table has id and username as first fields, like this:

for userid, username, *rest in cnx.select(table='auth_user', where='id=1'):
    print(userid, username)

Can I combine python's `for` statement with SQL like this: `for id, name, ctime in db.select('table_name', where='...')`

2 Answers2