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.

Raymond W · Accepted Answer · 2018-12-30 19:51:56Z

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)

answered Dec 30, 2018 at 19:51

Raymond W

6765 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

meowmeow · Accepted Answer · 2020-02-12 20:08:06Z

0

            ) AS  A2,
        Bills

I think this is where your issue is. You're not calling the Bills table in your FROM clause, you're calling the return table from the subquery you wrote with the alas A2. In other words, your From clause is pointing at the A2 'table' not Bills. As Qianbo Wang mentioned, if you want to return output from these two separate tables you will have to join them together.

answered Feb 12, 2020 at 20:08

meowmeow

2681 silver badge6 bronze badges

Collectives™ on Stack Overflow

Getting no such table error using pandas and sqldf

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related