Running SQL queries containing parameters in Python scripts

pydobc

pyodbc doesn’t support named parameters - parameters instead have to be represented as ? in the query string, with parameters then passed as a tuple to e.g. pd.read_sql_query().

One drawback: As of the time of writing, parameters like these will be flagged as errors in VSCode.

SQLAlchemy

SQLAlchemy has two APIs: Core and ORM (object relational mapper).

In Core, qmark style parameters are possible, as are named parameters, though the latter might rely on using e.g. engine.execute() rather than pd.read_sql_query(). 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 Core this needs to be done using dynamic SQL (taking care to avoid SQL injections)..

In ORM, variables can simply be used instead of query parameters.