I'm having trouble groking cursor.execute()
Chris Nelson
timetable_cursor.execute('SELECT ondate, username, availability '
'FROM %s '
'WHERE ondate >= "%s" AND ondate <= "%s" '
'GROUP BY ondate, username' %
(self.table_name,
from_date.isoformat(),
to_date.isoformat(),))
which fails in PostgreSQL. I changed it to:
timetable_cursor.execute("SELECT ondate, username, availability "
"FROM %s "
"WHERE ondate >= '%s' AND ondate <= '%s' "
"GROUP BY ondate, username" %
(self.table_name,
from_date.isoformat(),
to_date.isoformat(),))
That is, I reversed the use of single and double quotes.
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):
File
"/usr/local/lib/python2.6/dist-packages/Trac-0.11.6-py2.6.egg/trac/web/api.py",
line 377, in send_error
'text/html')
File
"/usr/local/lib/python2.6/dist-packages/Trac-0.11.6-py2.6.egg/trac/web/chrome.py",
line 743, in render_template
message = req.session.pop('chrome.%s.%d' % (type_, i))
File
"/usr/local/lib/python2.6/dist-packages/Trac-0.11.6-py2.6.egg/trac/web/api.py",
line 195, in __getattr__
value = self.callbacks[name](self)
File
"/usr/local/lib/python2.6/dist-packages/Trac-0.11.6-py2.6.egg/trac/web/main.py",
line 265, in _get_session
return Session(self.env, req)
File
"/usr/local/lib/python2.6/dist-packages/Trac-0.11.6-py2.6.egg/trac/web/session.py",
line 160, in __init__
self.get_session(req.authname, authenticated=True)
File
"/usr/local/lib/python2.6/dist-packages/Trac-0.11.6-py2.6.egg/trac/web/session.py",
line 176, in get_session
super(Session, self).get_session(sid, authenticated)
File
"/usr/local/lib/python2.6/dist-packages/Trac-0.11.6-py2.6.egg/trac/web/session.py",
line 59, in get_session
(sid, int(authenticated)))
File
"/usr/local/lib/python2.6/dist-packages/Trac-0.11.6-py2.6.egg/trac/db/util.py",
line 64, in execute
return self.cursor.execute(sql_escape_percent(sql), args)
File
"/usr/local/lib/python2.6/dist-packages/Trac-0.11.6-py2.6.egg/trac/db/util.py",
line 64, in execute
return self.cursor.execute(sql_escape_percent(sql), args)
InternalError: current transaction is aborted, commands ignored until
end of transaction block
and trac.log says:
ProgrammingError: syntax error at or near "E'team_availability'"
LINE 1: SELECT ondate, username, availability FROM E'team_availabili...
^
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?
Chris
--
Christopher Nelson, Software Engineering Manager
SIXNET - Solutions for Your Industrial Networking Challenges
331 Ushers Road, Ballston Lake, NY 12019
Tel: +1.518.877.5173, Fax: +1.518.877.8346 www.sixnet.com
osimons
On Jan 24, 6:54 pm, Chris Nelson <Chris.Nel...@SIXNET.com> 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):
>
> 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 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
Chris Nelson
>> ...
> 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])
Perfect. Thank you so much!
> It is all here in the docs:
>
> http://trac.edgewall.org/wiki/TracDev/DatabaseApi#RulesforDBAPIUsage
I'll look. Thanks.
Chris Nelson
> On 01/24/2012 05:07 PM, osimons wrote:
> > So that means you want to combine the concepts:
> > >>> cursor.execute("SELECT * FROM %s WHERE id=%%s" % 'ticket', [42])
>
> Perfect. Thank you so much!
>
> > It is all here in the docs:
>
> >http://trac.edgewall.org/wiki/TracDev/DatabaseApi#RulesforDBAPIUsage
do with
cursor.execute("SELECT ondate, username, availability FROM %s
" % \
self.table_name +
"WHERE ondate >= '%s' " % fromDate.isoformat()
+
" AND ondate <= '%s' " % toDate.isoformat() +
" AND username IN ('%s')" % "',
'".join(users))
The first two I'm OK with but is there DB API help fur building a set/
list for "WHERE ... IN ..."? http://trac.edgewall.org/wiki/TracDev/DatabaseApi#GuidelinesforSQLStatements
notes
For anything not portable (and you really fall quickly in there),
you need to use some methods from the connection when building your
SQL query (e.g. db.cast(column, type), db.concat(*params), db.like(),
db.like_escape(), db.quote(param), db.get_last_id(cursor, table,
col)).
but gives an example of LIKE but not IN. Is there some support for
IN? There best I can come up with is something like:
cursor.execute("SELECT * from table WHERE username in (%s)" +
MyDbList(users)
where MyDbList is something like:
def MyDbList(db, pyList):
myList = [db.quote(e) for e in pyList]
return ','.join(myList)
Is there a cleaner way?
Chris Nelson
> On Jan 24, 5:15 pm, Chris Nelson <Chris.Nel...@SIXNET.com> wrote:
> > On 01/24/2012 05:07 PM, osimons wrote:
> IN? There best I can come up with is something like:
>
> cursor.execute("SELECT * from table WHERE username in (%s)" +
> MyDbList(users)
>
> where MyDbList is something like:
>
> def MyDbList(db, pyList):
> myList = [db.quote(e) for e in pyList]
> return ','.join(myList)
>
> Is there a cleaner way?
API doesn't have quote(). Is there a way to determine the string
quoting character in 0.11?
osimons
On Jan 26, 10:25 pm, Chris Nelson <Chris.Nel...@SIXNET.com> wrote:
> On Jan 24, 5:15 pm, Chris Nelson <Chris.Nel...@SIXNET.com> wrote:
>
> > On 01/24/2012 05:07 PM, osimons wrote:
> > > So that means you want to combine the concepts:
> > > >>> cursor.execute("SELECT * FROM %s WHERE id=%%s" % 'ticket', [42])
>
> > Perfect. Thank you so much!
>
> > > It is all here in the docs:
>
> > >http://trac.edgewall.org/wiki/TracDev/DatabaseApi#RulesforDBAPIUsage
>
> I've made progress but even with the docs, I don't quite know what to
> do with
>
> cursor.execute("SELECT ondate, username, availability FROM %s
> " % \
> self.table_name +
> "WHERE ondate >= '%s' " % fromDate.isoformat()
> +
> " AND ondate <= '%s' " % toDate.isoformat() +
> " AND username IN ('%s')" % "',
> '".join(users))
>
> There best I can come up with is something like:
>
> cursor.execute("SELECT * from table WHERE username in (%s)" +
> MyDbList(users)
>
> where MyDbList is something like:
>
> def MyDbList(db, pyList):
> myList = [db.quote(e) for e in pyList]
> return ','.join(myList)
>
> Is there a cleaner way?
the users, then add users as args (or extend your existing args with
the users list to be replaced in same order). In Python:
>>> users = ["one", "two", "three"]
>>> sql = "SELECT * FROM table WHERE username in (%s)" %
','.join(('%s',) * len(users))
>>> print sql
'SELECT * FROM table WHERE username in (%s,%s,%s)'
>>> cursor.execute(sql, users)
:::simon
Chris Nelson
> On Jan 26, 10:25 pm, Chris Nelson<Chris.Nel...@SIXNET.com> wrote:
>> On Jan 24, 5:15 pm, Chris Nelson<Chris.Nel...@SIXNET.com> wrote:
>>> On 01/24/2012 05:07 PM, osimons wrote:
>> Is there a cleaner way?
>
> Yes, manipulate the string to insert as many %s as there are items in
> the users, then add users as args (or extend your existing args with
> the users list to be replaced in same order). In Python:
>
> >>> users = ["one", "two", "three"]
> >>> sql = "SELECT * FROM table WHERE username in (%s)" %
> ','.join(('%s',) * len(users))
> >>> print sql
> 'SELECT * FROM table WHERE username in (%s,%s,%s)'
> >>> cursor.execute(sql, users)
Great. I got that one working. Now I'm down to a an INSERT with
integers *and* strings:
insert_cursor.execute("INSERT INTO %s " % self.table_name + \
"(ondate, username, availability) " \
"VALUES %s " % \
", ".join(["('%s', '%s', %d)" %
(t[0], t[1], t[2] and
1 or 0,)
for t in inserts]))
I don't think I can just put "%s %s %s" in there; I'm afraid that the DB
API will try to store '1' (not 1). Guess I'll try a couple of things....
osimons
Types may in theory even differ between backends, so just stick with
%s and it will be stored in correct type by the db api.
BTW, for a number of these questions I would recommend that you hooked
up with the #trac IRC channel at irc.reenode.net. Much quicker
turnaround for answers & understanding in a group chat:
http://trac.edgewall.org/wiki/IrcChannel
See you there!
:::simon
Chris Nelson
> ...
> Don't use %d - it is always %s regardless of underlying datatype.
> Types may in theory even differ between backends, so just stick with
> %s and it will be stored in correct type by the db api.
TracJSGanttChart and TeamCalendar plugins are both now db-agnostic. Thanks.