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 -~----------~----~----~----~------~----~------~--~---