Running SQL queries containing parameters in Python scripts

pandas pd.read_sql()/pd.read_sql_query()

Parameters are supplied in a different format depending on the database driver being used.

pyodbc doesn’t support named parameters - parameters instead have to be represented as ? in the query, with parameters then passed as a tuple to pd.read_sql()/pd.read_sql_query(). (One drawback: As of the time of writing, parameters like these will be flagged as errors in VSCode).

sqlite does support named parameters. These are represented as e.g @parameter_name (or possibly other forms) in the query, with parameters passed as a dictionary to pd.read_sql()/pd.read_sql_query().

SQLAlchemy engine.execute()

qmark-style parameters are possible, as are named parameters.

If qmark parameters are used, parameters are passed to engine.execute() as a tuple.

Named parameters are represented as e.g. :parameter_name in the query, with parameters passed as a dictionary to engine.execute().

NB: In SQLAlchemy, query parameters can only be used to supply the values of things (e.g. column values) not the names of things. In order to supply the name of something when using engine.execute(), dynamic SQL is required (taking care to avoid SQL injections).

SQLAlchemy ORM

engine.execute() uses one of SQLAlchemy’s two APIs (Core).

In its second API, ORM (an object-relationship mapper), variables can simply be used instead of query parameters.