I didn't know about the placeholder argument, though the documentation says that it may not works for all the backend, is your database support it?
Documentation : In this case, the return values are not parsed or transformed by the DAL, and the format depends on the specific database driver. This usage with selects is normally not needed, but it is more common with indexes. executesql takes four optional arguments: placeholders, as_dict, fields and colnames. 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. On Wed, Nov 18, 2015 at 6:46 AM, Pierre <[email protected]> wrote: > Hi everyone, > > I cannot run an executesql command with the placeholders argument. Is > there a robust example somewhere to use as a reference ? > I need to pass string and float variables via placeholders > > I use a postgresql database > > test code is > > def test(): > tablename = 'tablex' > query = 'select * from %s' > result = db.executesql(query,placeholders=(tablename,)) > return locals() > > and the traceback: > > <class 'gluon.contrib.pg8000.ProgrammingError'> ('ERROR', '42601', 'syntax > error at or near "$1"') Version web2py™ Version > 2.12.3-stable+timestamp.2015.08.19.00.18.03 Traceback > > 1. > 2. > 3. > 4. > 5. > 6. > 7. > 8. > 9. > 10. > 11. > 12. > 13. > 14. > 15. > 16. > 17. > 18. > 19. > 20. > 21. > 22. > 23. > 24. > > Traceback (most recent call last): > File "/opt/web2py/gluon/restricted.py", line 227, in restricted > exec ccode in environment > File "/opt/web2py/applications/proxima/controllers/default.py" > <http://127.0.0.1:8000/admin/default/edit/proxima/controllers/default.py>, > line 89, in <module> > File "/opt/web2py/gluon/globals.py", line 412, in <lambda> > self._caller = lambda f: f() > File "/opt/web2py/applications/proxima/controllers/default.py" > <http://127.0.0.1:8000/admin/default/edit/proxima/controllers/default.py>, > line 41, in test > result = db.executesql(query,placeholders=(tablename,)) > File "/opt/web2py/gluon/packages/dal/pydal/base.py", line 998, in executesql > adapter.execute(query, placeholders) > File "/opt/web2py/gluon/packages/dal/pydal/adapters/postgres.py", line 360, > in execute > return BaseAdapter.execute(self, *a, **b) > File "/opt/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1378, in > execute > return self.log_execute(*a, **b) > File "/opt/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1372, in > log_execute > ret = self.cursor.execute(command, *a[1:], **b) > File "/opt/web2py/gluon/contrib/pg8000/core.py", line 573, in execute > self._c.execute(self, operation, args) > File "/opt/web2py/gluon/contrib/pg8000/core.py", line 1626, in execute > self.handle_messages(cursor) > File "/opt/web2py/gluon/contrib/pg8000/core.py", line 1774, in > handle_messages > raise self.error > ProgrammingError: ('ERROR', '42601', 'syntax error at or near "$1"') > > > > -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > You received this message because you are subscribed to the Google Groups > "web2py-users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
