I have a painted-myself-into-a-corner question that hopefully has a sensible solution I'm overlooking. I had a Python project using sqlite3, which I like a lot and use all the time, and I wanted to try to also support running it on postgres, in case scaling becomes an issue.
Some initial research suggested that there wasn't really a single de facto Python database abstraction layer (hopefully I didn't get this wrong), but psycopg2 fortunately seemed to have very similar structure and methods to sqlite3, and I was able to get away with only adding a couple helper functions and switch cases to my existing code to allow it to support both database libraries with the same queries.
The only exception, unbelievably enough, is the replacement character for variables; sqlite3 needs ? and psycopg2 needs %s. These are probably inherent to sqlite and postgres themselves for all I know.
This means that a function like this:
cur.execute("INSERT INTO repositories (repository_url, repository_name, repository_type, repository_thumbnail, last_crawl_timestamp, item_url_pattern) VALUES (%s,%s,%s,%s,%s,%s)", (repository_url, repository_name, repository_type, repository_thumbnail, time.time(), item_url_pattern))
Will only work for postgres, and if I change the %s's, to ?'s, it'll only work for sqlite. This defies any kind of elegant solution -- I don't really want to rig up some kind of string replacement to construct my queries, as that'll get dumb pretty quickly -- and mostly I'm just astonished that this has turned out to be my blocker.
Any thoughts?
The API in use by both implementations is the Python Database API Specification v2.0, documented in PEP 249. The module global
paramstyletells you what style of parameters a particular implementation expects. The possible values and meanings are documented here.