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.