Monday, 7 August 2023

SQL query a pandas MultiIndex column name

I've written a pandas DataFrame with MultiIndex column names to an SQL database. Now I need to query one of the columns. But because of the MultiIndex, the column names are made of more than one word, or combinations of words and empty strings:

from sqlalchemy import Table, create_engine
import pandas as pd

engine = create_engine('path/to/db')
tbl = Table('table_name', MetaData(), autload_with=engine)
print(tbl.c.keys())

>>> ["('Level 1', 'Level 2')", "('Single Level', '')", "('word', '')"]

query = select(tbl.c.word)
pd.read_sql_query(query, engine.connect())

>>> AttributeError: word

I've tried select(tbl.c["('word', '')"] but that didn't work either.

How can I query the word column in the database after pandas/SQL has formatted the name of it like that in the database?



from SQL query a pandas MultiIndex column name

No comments:

Post a Comment