[Python] How to use variable tables with SQLAlchemy Core

With SQLAlchemy Core you can run "raw" SQL queries, which can include parameters. However, I can't find an "official" way to use variable identifiers. Take the function get_entity below. It returns a row from a table and key specified by the function arguments.

For the moment, I created the _escape_identifier function to do this. However, this only works in Postgres (sqlite3 and MySql probably have their own escaping rules for identifiers). Also: I'm not 100% sure this works in every case.

import re, sqlalchemy

def _escape_identifier(identifier_name, always_double_quote=False):
# WARNING: Works in Postgres only, and is not intended for production code.
if not always_double_quote and re.match(r'^[a-z_][a-z0-9$_]*$', identifier_name, re.IGNORECASE):
return identifier_name
return '"' + identifier_name.replace('"', '""') + '"'

def get_entity(entity_name, key_column, key_value, entity_schema='public'):
ee = _escape_identifier
query = sqlalchemy.sql.text(f'''
SELECT *
FROM {ee(entity_schema)}.{ee(entity_name)}
WHERE {ee(key_column)} = :key;
''')
parameters = dict(key=key_value)
# run query with parameters and return data
# ...


The _escape_identifier function is basically doing the same thing as Posgres' QUOTE_IDENT function. However, that last function is returning text, so it adds another round trip (you gotta call a query on Postgres, and use the result instead of _escape_identifier). Also, it's Postgres specific.

The question: is there a way with SQLAlchemy to escape an identifier literal, so it can be used in a "raw" SQL query?

Continue reading...