Re: [pysqlite] Escaping strings with pysqlite

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

Christoph Burgmer wrote:
> Hi list,
> 
> I am looking for an equivalent to conn.escape_string() from mysql-python 
> (which appears to be mysql_real_escape_string() from the mysql lib).
> 
> I'd like to be able to put the code together in a flexible way instead of 
> using the following proposition:
> 
> cur.execute("insert into mytable(mycol) values (?)", (my_string,))

I recommend to use SQLAlchemy if you want to programmatically create
queries.

> Google wouldn't come up with anything helpful except with the hint above, and 
> I don't know where I should look for an API. Is it exotic to ask for a 
> function like this?

The reason MySQLdb exposes this function at all is because MySQL doesn't
(or at least didn't for a decade) support native parameter binding in
its API. And adapters like MySQLdb then do their parameter binding by
using Python's formatting operator '%' and the escape function you mention.

SQLite, on the other hand, does have native parameter binding in the
engine using API functions, so pysqlite wraps this.

If you'd want to programmatically construct queries using pysqlite only,
you'd then need to provide your own escape function and do the parameter
substitution yourself. This will work just fine for all types, except
maybe BLOB.

You could reuse the _quote function from pysqlite 1.1 (*):

http://oss.itsystementwicklung.de/trac/pysqlite/browser/sqlite/main.py?rev=297%3A08c492e53d36

HTH,

- -- Gerhard

(*) pysqlite 1.1 was a minimalistic adaption of pysqlite 1.0 to use the
SQLite 3 API. SQLite 2 did not have native parameter binding, yet.
That's why we basically did the same thing as MySQLdb in older pysqlite 1.x.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIXNv4dIO4ozGCH14RAvrVAJsEdAoZMh5FYaTclNQc62LxlvuHdQCgrwyK
6i6L5BMEugqLDYTL3QBWKrY=
=2RCn
-----END PGP SIGNATURE-----