SQLAlchemy: OperationalError: (sqlite3.OperationalError) no such column: _TransactionTable_.UNITS

I am trying to create a database and in it creating multiple tables. All the tables are created without giving any error. I have double checked in output as well. When I am trying to load the data in the fact_table, it gives me an output of no such column, although it is already present. I have added the code below.

EXTRACTION:

class transactionStage(Base):
    __tablename__ = '_TransactionTable_'
    RowID = Column(Integer, primary_key=True)
    PANID = Column(Float)
    WEEK = Column(Float)
    UNITS = Column(Float)
    DOLLARS = Column(Float)
    OUTLET = Column(String)
    IRI_KEY = Column(Float)
    prod = Column(String)
    Calendar_week_starting_on = Column(DateTime)
    Calendar_week_ending_on = Column(DateTime)

TRANSFORM:

def extract_staging_transaction(db_engine,file_path):
    Session = sessionmaker(bind=db_engine)
    session = Session()
    import pandas as pd
    transaction = pd.read_csv(file_path)
    rows = transaction.apply(lambda row : {'RowID': int(row['Row_ID']),
                                           'PANID': float(row['PANID']),
                                           'WEEK': float(row['WEEK']),
                                           'UNITS': float(row['UNITS']),
                                           'DOLLARS': float(row['DOLLARS']),
                                           'OUTLET': row['OUTLET'],
                                           'IRI_KEY': float(row['IRI_KEY']),
                                           #'COLUPC': float(row['COLUPC']),
                                           'prod' :row['prod'],
                                           'Calendar_week_starting_on': parser.parse(row['Calendar_week_starting_on']),
                                           'Calendar_week_ending_on': parser.parse(row['Calendar_week_ending_on']),
                                          }, axis=1)
    for row in rows:
        row = transactionStage(**row)
        session.add(row)
    session.commit()
    
extract_staging_transaction(engine, transaction_path)

LOAD:

class fact_table(Base):
    __tablename__ = "fact_table"
    Fact_Key =  Column(Integer,primary_key=True,autoincrement=True)
    HH_key = Column(Integer, ForeignKey('dim_HH.HH_key'))
    Male_HH_key = Column(Integer, ForeignKey('dim_Male_HH.Male_HH_key'))
    Female_HH_key = Column(Integer, ForeignKey('Dim_Female_HH.Female_HH_key'))
    product_key = Column(Integer, ForeignKey('Dim_Product.product_key'))
    race_key = Column(Integer, ForeignKey('Dim_Race.race_key'))
    appliance_key = Column(Integer, ForeignKey('Dim_Appliance.appliance_key'))
    pet_key = Column(Integer, ForeignKey('Dim_Pet.pet_key'))
    UPC_key = Column(Integer, ForeignKey('Dim_UPC.UPC_key'))
    Date_Key = Column(Integer, ForeignKey('Dim_Date.Date_Key'))
    Store_Key = Column(Integer, ForeignKey('Dim_Store.Store_Key'))
    PANID = Column(Float)
    UNITS = Column(Float)
    DOLLARS = Column(Float)
    Calendar_week_starting_on = Column(Integer, ForeignKey('Dim_Date.Date_Key'))
    Calendar_week_ending_on = Column(Integer, ForeignKey('Dim_Date.Date_Key'))
    IRI_KEY = Column(Float)
def load_fact_table(db_engine):
    from sqlalchemy import alias, and_
    from sqlalchemy.orm import aliased
    
    DimCalendar_week_start_key = aliased(DimDate)
    DimCalendar_week_end_key = aliased(DimDate)
    Session = sessionmaker(bind=engine)
    session = Session()
    rows = session.query(MainStage.PANID.label('PANID'),
                  DimHH.HH_key.label('HH_key'), 
                  dimMaleHH.Male_HH_key.label('Male_HH_key'),
                  DimFemaleHH.Female_HH_key.label('Female_HH_key'),
                  DimProduct.product_key.label('product_key'),
                  DimRace.race_key.label('race_key'),
                  DimAppliance.appliance_key.label('appliance_key'),
                  DimPet.pet_key.label('pet_key'),
                  DimUPC.UPC_key.label('UPC_key'),
                  DimDate.Date_Key.label('Date_Key'),
                  DimStore.Store_Key.label('Store_Key'),       
                  transactionStage.UNITS.label('UNITS'),
                  transactionStage.DOLLARS.label('DOLLARS'),
                  transactionStage.Calendar_week_starting_on.label('Calendar_week_starting_on'),
                  transactionStage.Calendar_week_ending_on.label('Calendar_week_ending_on'),
                  transactionStage.IRI_KEY.label('IRI_KEY')

I get the following error:

OperationalError: (sqlite3.OperationalError) no such column: TransactionTable.UNITS

Even though I have the above column defined and it is showing table as well when I use following command:

sql = "select * from _TransactionTable_ limit 5"
df = pd.read_sql_query(sql, engine)
df.head()

Please help me in understanding the error.



Read more here: https://stackoverflow.com/questions/68459951/sqlalchemy-operationalerror-sqlite3-operationalerror-no-such-column-transa

Content Attribution

This content was originally published by Vasudha Jain at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: