The problem is that not all adaptors support the same API and some are
undocumented. You can use
db._cursor.execute("select first, last from names where id=%(id)s;",
{'id': id})
db._cursor.fetchall()
On Mar 31, 10:54 pm, David Niergarth <[email protected]> wrote:
> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
