Issues with SQLite and Python

Question

When this runs:

cursor.execute("DROP TABLE IF EXISTS 2019results")
cursor.execute("""CREATE TABLE IF NOT EXISTS 2019results (
                                    p_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                                    hospitalahcanumber INTEGER,
                                    MedicareCharges INTEGER,
                                    MedicareRevenues INTEGER,
                                    MedicAIDCharges INTEGER,
                                    MedicAIDRevenues INTEGER,
                                    EmployeeDiscounts INTEGER,
                                    OtherDeductions INTEGER
                                    )""")
cursor.execute("""INSERT INTO 2019results VALUES VALUES (hospitalahcanumber, MedicareCharges, MedicareRevenues,MedicAIDCharges, MedicAIDRevenues,EmployeeDiscounts,OtherDeductions)""")

I get the error:

cursor.execute("DROP TABLE IF EXISTS 2019results")
sqlite3.OperationalError: unrecognized token: "2019results"

Now, if I put single quotes around 2019results in the three cursor.execute()'s above, I no longer get the unrecognized token. But then, my INSERT statement fails with;

File "2019SQLqueryXXX.py", line 84, in main
cursor.execute("""INSERT INTO '2019results' VALUES (p_ID, MedicareCharges, MedicareRevenues,MedicAIDCharges, MedicAIDRevenues,EmployeeDiscount
s,OtherDeductions)""")
sqlite3.OperationalError: no such column: hospitalahcanumber

All of what I pass into that INSERT statements are type <class 'int'>, and I set the table up as INTEGER type.

I can put single quotes around each variable in the INSERT , I can avoid the "no such column" error, but I end up with a third error - data mismatch.

cursor.execute("DROP TABLE IF EXISTS '2019results'")
cursor.execute("""CREATE TABLE IF NOT EXISTS '2019results' (
                                    p_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                                    hospitalahcanumber INTEGER,
                                    MedicareCharges INTEGER,
                                    MedicareRevenues INTEGER,
                                    MedicAIDCharges INTEGER,
                                    MedicAIDRevenues INTEGER,
                                    EmployeeDiscounts INTEGER,
                                    OtherDeductions INTEGER
                                    )""")
cursor.execute("""INSERT INTO '2019results' VALUES ('p_ID','hospitalahcanumber', 'MedicareCharges', 'MedicareRevenues','MedicAIDCharges', 'MedicAIDRevenues','EmployeeDiscounts','OtherDeductions')""")

Here is that final error message:

File "2019SQLqueryXXX.py", line 84, in main
cursor.execute("""INSERT INTO '2019results' VALUES ('p_ID','hospitalahcanumb
er', 'MedicareCharges', 'MedicareRevenues','MedicAIDCharges', 'MedicAIDRevenues'
,'EmployeeDiscounts','OtherDeductions')""")
sqlite3.IntegrityError: datatype mismatch

I have a synergy of errors here and I would appreciate some help. Why does putting 'single quotes' seem to be necessary? Why does it say "no such column' when I do have a table that exists with those columns? Why do I have a data mismatch when everything is set up to be an Integer?

I imagine this difficulty stems from you beginning your table name with a digit. If you didn't do that, I don't think you would have to quote the table name. — khelwood, Sep 01 '20 at 15:17
Related: [What are valid table names in SQLite?](https://stackoverflow.com/questions/3694276/what-are-valid-table-names-in-sqlite) — wwii, Sep 01 '20 at 15:25
You just helped me deal with the first issue - I no longer get the "unrecognized token" error. I just now have to figure out why I get "no such column" and "data mismatch" — Peter Van Loon, Sep 01 '20 at 15:27
Thanks to both of you for helping me with my initial problem!! — Peter Van Loon, Sep 01 '20 at 15:42

score 0 · Answer 1 · answered Sep 01 '20 at 19:18

I found the issue. I needed to change the INSERT statement. it should be:

cursor.execute("""INSERT INTO results2019 VALUES (?,?,?,?,?,?,?,?)""", (p_ID,hospitalahcanumber, MedicareCharges,MedicareRevenues,MedicAIDCharges, MedicAIDRevenues,EmployeeDiscounts,OtherDeductions)

I am not sure why my code did not work, but I do know that this does.

Issues with SQLite and Python

1 Answers1