SQLite INNER JOIN in python: sqlite3.OperationalError: no such column:

Question

I'm having an issue with SQLite in python. The following code doesn't appear to work due to the error

sqlite3.OperationalError: no such column: Company

I'm trying to gather data from both of the tables and display them back to the user using tabulate but cannot proceed and I cannot figure out how to solve this problem. The solution is probably simple but due to my limited programming knowledge I'm unsure how to proceed.

Here's the code:

def view_all_by_CompID(data):        
    with sqlite3.connect("Clients.db") as db:
        cursor = db.cursor()
        cursor.execute("""SELECT CompanyID, Forename, Surname, eMail
                       FROM Clients
                       JOIN Company
                       ON Clients.CompanyID = Company.CompanyID
                       WHERE CompanyID = ?""",(data,))
        ViewData = cursor.fetchall()
        DataTableCompAndClient([ViewData])
    db.commit()

I am unsure why this happens as I'm certain that both tables exist and that (I believe) am calling them correctly. I don't know why it keeps giving me the error so any help would be appreciated. Here's a few details about the code:

Clients.db = The name of the database file

Clients = A table where client information is held

Company = A table where company information is held

CompanyID = A specified Company ID number present in both tables

I've looked at a variety of examples on this site but I can't seem to solve the issue. Any advice would be appreciated.

theres a space between in Company ID at start of select, this should probably be CompanyID.. — amdixon
– amdixon, Commented May 6, 2015 at 11:53
can you echo the actual sql run by the cursor by printing cursor._executed and test that directly in your sql client — amdixon
– amdixon, Commented May 6, 2015 at 12:15

B. Anderson · Accepted Answer · 2015-05-06 12:20:22Z

5

I have fixed the problem with the help of a friend. There were a few missing lines of code which needed entering, which are the following:

def view_all_by_CompID(data):        
    with sqlite3.connect("Clients.db") as db:
        cursor = db.cursor()
        cursor.execute("""SELECT Clients.CompanyID, Clients.Forename, Clients.Surname, Clients.eMail, Company.CompanyID, Company.CompanyName
                       FROM Clients
                       INNER JOIN Company
                       ON Clients.CompanyID = Company.CompanyID
                       WHERE Clients.CompanyID = ?""",(data,))
        ViewData = cursor.fetchall()
        DataTableCompAndClient([ViewData])
    db.commit()

answered May 6, 2015 at 12:20

B. Anderson

811 silver badge6 bronze badges

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

1 Comment

Matthew Over a year ago

nice catch there on the table specification. Gets me all the time.

Collectives™ on Stack Overflow

SQLite INNER JOIN in python: sqlite3.OperationalError: no such column:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related