Syntax for placeholders in executesql

[Chris]

Hey all,

I was trying to use executesql with placeholders, based on the
documentation in source and the mailing list [1]. The syntax on the
site seems to suggest using question marks, for example:

db.executesql("select * from auth_user where id = ?", [4])

I was banging my head against this for a while until I realized that
the syntax that works is actually:

db.executesql("select * from auth_user where id = %s", [4])

(not sure if this is in postgres or everywhere in general.) Is this
correct, and if so can it be added to the documentation?

Thanks!


[1]
http://groups.google.com/group/web2py/browse_thread/thread/dd01da67ca713421/baf2e0b206892be1?lnk=gst&q=postgresql+placeholders#baf2e0b206892be1

[Richard G]

As I understand Web2py passes this off to the database adapter that is
in use (likely psycopg2 in this case).

Psycopg2 accepts arguments in a couple different forms, using the '%s'
as you identify below, as well as named references with '%(name)s'.

There are a few caveats when using query parameters that are specific
to psycopg2. More information (with better explanation then I can
provide) is available at:
http://initd.org/psycopg/docs/usage.html

Hope this helps

On Dec 1, 1:29 am, Chris <partyonais...@gmail.com> wrote:
> Hey all,
>
> I was trying to use executesql with placeholders, based on the
> documentation in source and the mailing list [1]. The syntax on the
> site seems to suggest using question marks, for example:
>
> db.executesql("select * from auth_user where id = ?", [4])
>
> I was banging my head against this for a while until I realized that
> the syntax that works is actually:
>
> db.executesql("select * from auth_user where id = %s", [4])
>
> (not sure if this is in postgres or everywhere in general.) Is this
> correct, and if so can it be added to the documentation?
>
> Thanks!
>

> [1]http://groups.google.com/group/web2py/browse_thread/thread/dd01da67ca...

[Brian M]

Yes, web2py's executesql when used with placeholders will simply pass
the provided query & placeholders on to execute() method of whichever
database driver is being used, thus you must use a syntax supported by
your driver. In the case of SQLite & MS SQL that syntax is question
marks (?) for Postgres it is apparently %s or %(name)s and I think
MySQL is the same (I haven't tried yet).

~Brian

[Ty oc]

Well, if this is so specific, why it isn't documented with the current simple equivalent examples for each supported driver??


I mean

placeholders is an optional sequence of values to be substituted in or, if supported by the DB driver, a dictionary with keys matching named placeholders in your SQL.

Says almost nothing at all.

[Ty oc]

By the way, what case would be the xintaxis for oracle as database?

[Niphlod]

apparently it's :1, :2, etc etc etc

see the end of http://www.oracle.com/technetwork/articles/dsl/prez-python-queries-101587.html

[Anthony]

On Tuesday, December 6, 2016 at 12:46:34 PM UTC-5, Ty oc wrote:

Well, if this is so specific, why it isn't documented with the current simple equivalent examples for each supported driver??

Note, you can make a pull request if you'd like to see a change in the documentation: https://github.com/web2py/web2py-book.
 

I mean

placeholders is an optional sequence of values to be substituted in or, if supported by the DB driver, a dictionary with keys matching named placeholders in your SQL.

Says almost nothing at all.

The above is an exact description of the "placeholders" argument. There isn't much more to say about it. What is missing is documentation regarding the syntax used by each driver for inserting placeholders into raw SQL. I'm not sure we should try to document it for every driver, but maybe at least SQLite, Postgres, and MySQL, with a mention that further documentation can be found with the individual drivers.

Anthony