[DB-SIG] API suggestion: expose 'quote' method

[Stuart Bishop]

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

>> I'm suggesting that we make this part of the API (2.1, perhaps) so
>> that there's something there my application can depend on. I believe
>> it's an important part of dealing with the DMBS, and should be part of
>> the specs/requirements.
>
> +1

+1

It is needed for dumping generated SQL to error logs and such.
I can't think of other tasks that absolutely require this, but
there are plenty of tasks that are simplified by having it.
Here are implementations of this, and more I'd be interested in,
for PostgreSQL (I think based on code I ripped off from Psycopg).
In particular, not all backends will allow binding of table or
column names, so if you use case sensitive or non-alphanumeric
table or column names you need to know the quoting rules for them.

def quote(value):
     ''' Quote a value, such as a string or an integer, suitable for
         inclusion in an SQL command
     '''
     # Currently barfs on dates. Numbers quoted due to lazyness
     # but justified to ensure we don't lose precision during transport.
     quote_dict = {"\'": "''", "\\": "\\\\"}
     for dkey in quote_dict.keys():
         if value.find(dkey) >= 0:
             value=quote_dict[dkey].join(value.split(dkey))
     return "'%s'" % value

def quoteIdentifier(identifier):
     ''' Quote a database object name, such as a table or a view '''
     quote_dict = {'\"': '""', "\\": "\\\\"}
     for dkey in quote_dict.keys():
         if identifier.find(dkey) >= 0:
             identifier=quote_dict[dkey].join(identifier.split(dkey))
     return '"%s"' % identifier

cols = ['id','ISBN Number','DOI']
table = 'Demo House Publishers'
match_col = 'ISBN Number'
matches = ['1234567890','189481522X']
print 'SELECT (%s)\nFROM %s\nWHERE %s in (%s)' % (
     ', '.join([quoteIdentifier(i) for i in cols]),
     quoteIdentifier(table),
     quoteIdentifier(match_col),
     ', '.join([quote(m) for m in matches]),
     )

Generates:
SELECT ("id", "ISBN Number", "DOI")
FROM "Demo House Publishers"
WHERE "ISBN Number" in ('1234567890', '189481522X')

- -- 
Stuart Bishop <zen at shangri-la.dropbear.id.au>
http://shangri-la.dropbear.id.au/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (Darwin)

iD8DBQE+3TUOh8iUz1x5geARAgTTAJ9Ng8xhONbZSwDZ3z8nwpRb2rMCIgCbBZo1
xLKqgfDsdgrnrajVWlfWGcI=
=+pfO
-----END PGP SIGNATURE-----