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))
>
> 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?
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)
:::simon
--
You received this message because you are subscribed to the Google Groups "Trac
Development" group.
To post to this group, send email to trac-dev@googlegroups.com.
To unsubscribe from this group, send email to
trac-dev+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/trac-dev?hl=en.
