[DB-SIG] qmark to %s converter

[Magnus Lyckå]

I think this code could be used to support qmark format on
platforms that support format today. If we want to support
*both* %s and ?, we either need to "autodetect" this, or to
be able to tell the driver what to use. I haven't done that.
Anyway, this is a start, and it works with the SQL statements
I tried. Please try to break it. If it doesn't break, please
use it! :)

It would be great if we could make one paramstyle work with all
drivers!

#This code converts stuff like "SELECT * FROM ? WHERE X > ?" to
#"SELECT * FROM %s WHERE X > %s". It should be fairly simple to
#make the same thing for :this, :that.

# Magnus Lyckå, 2003-05-16

import re

#Cache parsed statements using
#"statements.setdefault(sql, parseQmark(sql))"
#to save time for repeated calls.
statements = {}

def parseQmark(sql):
     '''parseQmark(sql_with_?) -> sql_with_%s

     Parse SQL written with qmark parameter style (i.e. Direct SQL
     or SQL CLI style) so that the python %-operator can be used to
     pass in parameters. This means that % needs to be changed to %%,
     and ? needs to be changed to %s if it's not inside quotes.
     '''

     sql = sql.replace('%', '%%')

     #The tricky part is to separate stuff inside quotes from stuff
     #outside quotes. This is done with a regular expression. We get a
     #list of tuples with [(not quoted, quoted), (not quoted, quoted)...].
     #The present re only works if the string ends with ' though, so
     #we'll append '' and remove it before we return the final string.

     quote_separation = re.compile(r"(.*?)('.*?')", re.DOTALL)
     notQuoted_quoted = quote_separation.findall(sql+"''")
     replaced = [nq.replace('?', '%s')+q for (nq, q) in notQuoted_quoted]

     return "".join(replaced)[:-2] # remove extra ''

# Borrowed from sqlite and patched to allow %f etc (which isn't really
# needed if we use ?, since it'll be %s then anyway... :)
def _quote(value):
     """_quote(value) -> string
     This function transforms the Python value into a string suitable to 
send to
     the SQLite database in a SQL statement.  This function is automatically
     applied to all parameters sent with an execute() call.  Because of this a
     SQL statement string in an execute() call should only use '%s' [or
     '%(name)s'] for variable substitution without any quoting."""
     if value is None:
         return 'NULL'
     elif isinstance(value, str):
         return "'%s'" % value.replace("'", "''")
     elif type(value) in [long, int, float]:
         return value
     elif hasattr(value, '_quote'):
         return value._quote()
# Never mind dates right now...
##    elif have_datetime and type(value) in \
##            (DateTime.DateTimeType, DateTime.DateTimeDeltaType):
##        return "'%s'" % value
     else:
         return repr(value)

def execute(sql, params):
     sql = statements.setdefault(sql, parseQmark(sql))
     print sql % tuple(map(_quote, params))

testdata = [

     ("select * from x where i = ? and s = ?",
     (5, "Aren't you done?")),

     ("""insert into x
     (i, f, s) values (?, ?, ?)""",
     (42L, 3.14, 'Hello')),

     ("""SELECT 'It''s'||col2 FROM ?
     WHERE ? LIKE '%aren''t they cute?%'
     AND x = ?""",
     ('table1', 'col1', 5)),

     ("""SELECT 'It''s'||col2 FROM ?
     WHERE ? = 'aren''t they cute?'
     AND x = '?'""",
     ('table1', 'col1'))

     ]

for sql, params in testdata:
     execute(sql, params)
     print


--
Magnus Lycka (It's really Lyckå), magnus at thinkware.se
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program