On Jan 24, 6:54 pm, Chris Nelson <[email protected]> wrote:
> Clearly neither of these can work on both MySQL and PostgreSQL. But
> http://trac-hacks.org/ticket/7115#comment:5 says:
>
> An even simple alternative for the example above is just to pass it
> as argument, and Trac will do whatever needed for quotes and types
> depending on backend:
>
> cursor.execute("SELECT * FROM myTable WHERE foo=%s", ('bar',))
>
> So I try:
>
> timetable_cursor.execute('SELECT ondate, username, availability '
> 'FROM %s '
> 'WHERE ondate >= %s AND ondate <= %s '
> 'GROUP BY ondate, username,
> availability',
> (self.table_name,
> from_date.isoformat(),
> to_date.isoformat(),))
>
> That is, unquote the date strings and replace the '%' format operator
> with a comma. With this change, the Team Calendar page shows:
>
> Traceback (most recent call last):
>
> [snip]
>
> ProgrammingError: syntax error at or near "E'team_availability'"
> LINE 1: SELECT ondate, username, availability FROM E'team_availabili...
>
> Where's that 'E' come from? More usefully, what am I doing wrong? How
> can I pass strings to cursor.execute() and have it do the right thing?
Clearly better than doing argument string interpolation which is
clearly frowned upon, because it is a) a security risk, and b) a
painful experience.
However, what you are doing is mixing two concepts. The 'fixed'
content of your SQL such as tables and column names cannot be replaced
by dynamic variables, so for them you would need to do string
interpolation so that your SQL is correct. As %s string interpolation
will conflict with the argument substitution that comes later you need
to escape the argument placeholder by using %%s (the first % escapes
the second to treat it like a regular %).
In python:
>>> "SELECT * FROM %s WHERE id=%%s" % 'ticket'
"SELECT * FROM ticket WHERE id=%s"
So that means you want to combine the concepts:
>>> cursor.execute("SELECT * FROM %s WHERE id=%%s" % 'ticket', [42])
It is all here in the docs:
http://trac.edgewall.org/wiki/TracDev/DatabaseApi#RulesforDBAPIUsage
:::simon
https://www.coderesort.com
http://trac-hacks.org/wiki/osimons
--
You received this message because you are subscribed to the Google Groups "Trac
Development" 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/trac-dev?hl=en.
