Normalize Python DB API calls between SQLite and MySQL/PGSQL

Somehow there is no single formatting for parameters/variables in python DB API calls.  Looking at the DB API specification, it seems that specific database drivers can be written  with ‘?’ or ‘%s’, or even other conventions.

paramstyle Meaning
qmark Question mark style, e.g. ...WHERE name=?
numeric Numeric, positional style, e.g. ...WHERE name=:1
named Named style, e.g. ...WHERE name=:name
format ANSI C printf format codes, e.g. ...WHERE name=%s
pyformat Python extended format codes, e.g. ...WHERE name=%(name)s

How do you know the ‘paramstyle’ of your database connection?  For SQLite use:

>>> import sqlite3
>>> sqlite3.paramstyle
'qmark'

Using a library like sqlalchemy circumvents this problem.  In case you need to use the DB API drivers, a simple function which formats SQL strings can be used to enhance portability. The function below does this. It is a quick 80% solution. Adapt as needed.

def fs(sql_string):
""" format sql string according to db engine used """
# normalize dbapi parameters, always use %s (MySQL, PG) in sql_string,
# set escape_string to r'?' for sqlite
# e.g. %s and ?
# escape_string = r'%s '
escape_string = r'? '
# autoincrement_string = "AUTO_INCREMENT" # for MySQL
autoincrement_string = "AUTOINCREMENT" # for sqlite
return_string = sql_string
return_string = re.sub(r'%s ', escape_string, return_string)
return_string = re.sub(r'AUTO_INCREMENT', autoincrement_string, return_string)
return return_string

It is possible (and likely) that the SQL for the DDL will be different between e.g. SQLite and MySQL.  For example, in MySQL you’d use ‘AUTO_INCREMENT’, whereas in SQLite you’d use ‘AUTOINCREMENT’.  By extending the above approach to also replacing these strings, you can further abstract the database code.