CODEWITHSUNDEEP
pythonpostgresqlsqlite
Jelle Besseling
Jelle Besseling

Reputation: 58

How can I write SQL statements for psycopg2 for PostreSQL and sqlite3?

I use the psycopg2 library for PostgreSQL in python for my production environment but I want to test my code with the sqlite3 library. The problem is that psycopg2 uses %s as a placeholder for parameter substitution, whereas sqlite3 uses ?.

I am not happy with the code I got now, it looks like this:

queries = {
     'is_admin':          "SELECT * FROM admins WHERE user_id = ?;",
}

if dbtype == 'postgres':
    for key, value in queries.items():
        queries[key] = value.replace('?', '%s')

It's kind of an ugly hack but I don't want to write all my queries two times, so what's the best solution for this problem?

Upvotes: 2

Views: 1494

Answers (1)

alecxe
alecxe

Reputation: 473863

Using simple ? to %s string replacement is dangerous, you may break your query, or harm the actual data in all sorts of unpredictable ways which could be difficult to debug.

Look into the "SQL for humans" records package that provides a uniform parameterization style for all the supported databases.

Note that records achieves the database-agnostic parameterization style only because of using the text() function from the SQLAlchemy package. You may as well use it directly:

The advantages text() provides over a plain string are backend-neutral support for bind parameters, per-statement execution options, as well as bind parameter and result-column typing behavior, allowing SQLAlchemy type constructs to play a role when executing a statement that is specified literally.

Upvotes: 4

Related Questions