Getting no such table error using pandas and sqldf

Question

I am getting a sqlite3 error.

OperationalError: no such table: Bills

I first call my dataframes using pandas and then call those dataframes in my query which works fine

import pandas as pd
from pandasql import sqldf

Bills = pd.read_csv("Bills.csv")
Accessorials = pd.read_csv("Accessorials.csv")

q = """
Select          
            CityStateLane, 
            Count(BillID) as BillsCount, 
            Sum(BilledAmount) as BillsSum, 
            Count(Distinct CarrierName) as NumberOfCarriers, 
            Avg(BilledAmount) as BillsAverage, 
            Avg(BilledWeight) as WeightAverage
From 
            Bills
Where 
            Direction = 'THIRD PARTY' 
Group by 
            CityStateLane
Order by 
            BillsCount DESC
"""

topCityStateLane = sqldf(q)

I then create another data frame using another query but this calls the errors saying Bills is not there even though I successfully used it in the previous query.

q = """
SELECT
         Bills.BillID as BillID,
         A2.TotalAcc as TotalAcc
FROM
            (SELECT
                    BillID_Value,
                    SUM(PaidAmount_Value) as "TotalAcc"
            FROM  
                    Accessorials 
            GROUP BY
                    BillID_Value 
            ) AS  A2,
            Bills 
WHERE    
            A2.BillID_Value  = Bills.BillID
 """
temp = sqldf(q)

Thank you for taking the time to read this.

Answer 1

Are you trying to join Bills with A2 table? You can't select columns from two tables in one select from statement.

q = """
SELECT
         Bills.BillID as BillID,
         A2.TotalAcc as TotalAcc
FROM
            (SELECT
                    BillID_Value,
                    SUM(PaidAmount_Value) as "TotalAcc"
            FROM  
                    Accessorials 
            GROUP BY
                    BillID_Value 
            ) AS  A2 
            join Bills 
            on A2.BillID_Value  = Bills.BillID
 """
temp = sqldf(q)