The usual way to make a request to a database is to write a string with the SQL syntax, then execute this request and get the result as a list with cursor.fetchall() or cursor.fetchone()
Python has list comprehensions to select items in an iterable if a certain condition is true ; this is very similar to database requests
This recipe wraps a table of a DB-API compliant database in a class that implements the iteration protocol, so that you can use the for ... in ... if ... syntax
| Python |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | """A wrapper around DBAPI-compliant databases to support iteration
and list comprehension syntax for requests, instead of SQL
To get an iterator, initialize a connection to the database, then
tbl = Table(connection,table_name)
returns an iterator that yields records (instances of the generic
class Record) whose attributes match the fields in the database
You can also choose to return a dictionary or a list with the
method set_return_type()
Example of use with sqlite :
from pysqlite2 import dbapi2 as sqlite
conn = sqlite.connect('planes')
plane_tbl = Table(conn,'plane')
country_tbl = Table(conn,'countries')
# simple requests
print [ r.name for r in plane_tbl if r.country == 'France' ]
print [ r.country for r in country_tbl if r.continent == 'Europe']
# request on two tables
print [r.name for r in plane_tbl for c in country_tbl
if r.country == c.country and c.continent == 'Europe']
"""
class Record(object):
"""A generic class for database records"""
pass
class Table:
def __init__(self,conn,table):
self.table = table
self.cursor = conn.cursor()
self._iterating = False
# to initialize cursor.description, make a select request
self.sql = "SELECT * FROM %s" %self.table
self.cursor.execute(self.sql)
self.names = [ d[0] for d in self.cursor.description ]
self.return_type = object
def set_return_type(self,rt):
if not rt in [object,list,dict]:
raise TypeError,"Invalid return type %s" %rt
else:
self.return_type = rt
def __iter__(self):
return self
def next(self):
if not self._iterating:
# begin iteration
self.cursor.execute(self.sql)
self._iterating = True
row = self.cursor.fetchone()
if row is not None:
if self.return_type == object:
# transform list into instance of Record
rec = Record()
rec.__dict__ = dict(zip(self.names,row))
return rec
elif self.return_type == dict:
return dict(zip(self.names,row))
elif self.return_type == list:
return row
self._iterating = False
raise StopIteration
|
Discussion
The requests using this syntax are much slower than with the raw SQL statement, mostly because building an instance of a user-defined class takes some time
You can set the return type to dict (a little faster than an object) or to list (much faster, but still slower than raw SQL)
Comments
I also have noted the similarity between comprehensions and SQL queries... and I plain dislike SQL syntax, and writing ugly SQL queries in a clean python program. :)
I've wondered if there was some way of getting back to the data contained in a generator expression, such that something like
could be translated into the corresponding SQL query by the 'query' constructor without performance penalty. Unfortunately, I couldn't find a way to inspect the objects generated by generator expressions... could anybody help in that?
I don't think you can inspect generators the way you have in mind, but check out SQLObject (http://sqlobject.org/SQLObject.html). It can express sql queries almost as clean as generators and with the same lazy evaluation property.
generators won't work. I don't think it's possible. Using the SQLObject approach would be something like
where TABLE implemented a getattr which tracked how the plane_tbl was used. Problem is, there's no way at the object level to distinguish the above vs.
nor, because of short circuiting, is it possible to get all of the possible routes through a complicated 'if' test. Eg, consider
The SQLObject approach seems to be the best. One alternative, btw, is to allow introspection of a "well-formed" function.
You can then inspect the byte code or parse tree to figure out what the function is doing and construct a query with the same results. Not for the faint-hearted. :)
This is a title! It sounds like what you want is a language where you can reprogram how the compiler interprets the syntax. That's what you'd be doing if you were reading the contents of a generator expression -- your input would be the generator expression's parse tree, and your output would be a new chunk of code. Essentially, a tree-transform... and that's what compilers do. :)
And what's cool is that this is actually possible now. There's an interesting language called "boo" that's basically python-with-more-neat-stuff (and static typing). It's written for the CLI (Mono/.NET), but don't hold that against it -- it's still pretty spiffy
http://boo.codehaus.org/
Now if only someone would invent a language for writing new languages, so we can trade syntax modules instead of code modules.
Done in recipe 442447. I have used this method in another recipe, #442447 : get the source code of the generator expression, then parse it with the compiler module, then build the SQL statement
Sign in to comment