CODEWITHSUNDEEP
pythonsqlalchemy
Cilantro Ditrek
Cilantro Ditrek

Reputation: 1097

How to reference a column whose name starts with a numeral?

I am querying an existing table with some columns with names beginning with numerals (i.e., "1st_party", "2nd_party"). I am not the DBA for this dataset and cannot change the column names. In my current code, I cannot index the column without getting an "invalid syntax" error.

Please note: I am very new to SQLAlchemy, so I may simply have a misconception in a core concept of the ORM.

Here is my sample code:

sengine = create_engine('CONNECTION_STRING_GOES_HERE')

smeta = MetaData(schema='SCHEMA_GOES_HERE')
smeta.reflect(bind=sengine)

ssession = sessionmaker(bind=sengine)
source = ssession()

source_tbl = Table('TABLE_NAME_GOES_HERE', smeta, autoload=True, schema='SCHEMA_GOES_HERE', autoload_with=sengine)

cols = source_tbl.c
qry = source.query(source_tbl).where(cols.1st_party == 'PARTY_NAME_GOES_HERE'))
results = source.execute(qry)

This code has worked with columns that do not start with a numeral (let's say "department" instead of "1st_party"), but for the table in question, I need this field. Is there a better way for me to filter my query without needing to define the table object?

Upvotes: 2

Views: 49

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123484

If a column name results in an invalid Python identifier, e.g., source_tbl.c.1st_party, then we can use the name as a string index into the .c collection:

… .where(source_tbl.c["1st_party"] == "whatever")

Upvotes: 1

Related Questions