Re: [pysqlite] [APSW] "SQLError: cannot rollback - no transaction is active"

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Michael Schlenker wrote:
> One important exception:
> 
> "SELECT foo WHERE bar IN (%s)" % ",".join(["'%s'" % str(val) for val in l])
> 
> this works with string substitution, but:
> 
> "SELECT foo WHERE bar IN (%)", l
> 
> does not usually...,

Err the latter does work.

>>> c.execute("select 3 in (?,?,?,?)", (3,4,5,6)).fetchall()
[(1,)]

If you want to generate the question marks:

>>> l=[4,5,6]
>>> q="?"*len(l)
>>> c.execute("select 3 in ("+",".join(q)+")", l).fetchall()
[(0,)]
>>> l.append(3)
>>> q="?"*len(l)
>>> c.execute("select 3 in ("+",".join(q)+")", l).fetchall()
[(1,)]

> You can work around it with some horrible pivot table hacks, and string
> processing in SQL but its pretty ugly, or you can generate not a single bind
> parameter but a bunch of them, depending on your list length, also ugly, but
> a bit less so.

I really don't understand what you are getting at.  Is the above hard or
ugly?  It is *always* a bad idea to not use bindings.  For example the
SELECT at the top of the message converts all values to strings, will
fail on any string with an apostrophe in it (fairly common with English
text) and makes you subject to SQL injection attacks.


Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkk0NHwACgkQmOOfHg372QQ8CQCgmLdCGBdCS0PMFPOs210GbEit
9goAoKYXp6h9kNJnphBAtDUgM3gEuJVN
=ynw1
-----END PGP SIGNATURE-----