For our application we are not using the ORM features of the DAL (see
P.S. explanation). All of our non-auth queries use db.executesql(sql).
It seems like executesql() should support the optional mapping-or-
tuple argument that the underlying DB API 2.0 cursor supports. For
example,
cursor.execute("select first, last from names where id=%(id)s;",
{'id': id})
cursor.execute("select first, last from names where id=%s;",
(id,))
The db adapter will then handle escaping the values you're subbing
into your query string. With executesql(), you need to resort to
escaping the values manually and using string interpolation. In our
case, that would be something like
db.executesql("select first, last from names where id = %s;" %
psycopg.Binary(id))
My first thought was to submit a patch to allow a second argument to
executesql to be passed through to the underlying cursor.execute(). A
better solution, however, might be to add a method or attribute that
exposes the underlying cursor object so we'd be free to use the usual
DB API methods (execute(), fetchone(), fetchall(), etc.). The
executesql() behavior of doing a fetchall is not always ideal either.
Does anybody have any thoughts about accessing cursor methods directly
or adding a second arg to executesql()? Would you be interested in
patch for executesql()?
--David
P.S. The main reason we're not using the DAL ORM features is lack of
support for PostgreSQL schemas. Schemas in PostgreSQL are a namespace
above the level of tables that allow you to group tables and prevent
table name collisions between schemas. The default schema in
PostgreSQL is "public," so queries for a table called "people" would,
more explicitly, be "public"."people". So, for example, the tables
schema1.people and schema2.people would be distinct tables in
different schemas. The DAL dotted attribute access doesn't give us a
way to distinguish these tables. I'm not suggesting the DAL should
support schemas -- it is a PostgreSQL-specific extension and we're not
the normal use case. But it is a limitation for us.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
