Getting "sqlite3.OperationalError: no such column:"

Question

I am new to python (as I am sure you can tell from my code) and have basically written a sample script to test some core commands so I can better understand how things work. I have gotten everything running as designed except the last "insert" command -- after hours of googling and experimenting I cannot figure out what's wrong, so I would appreciate it if anyone can show me what needs to be changed and helping me understand why (I'm sure it's basic, but am stumped!).

Below is the line that is giving me trouble:

c.execute("INSERT OR IGNORE INTO {tn} ({cn1}, {cn2}, {cn3}, {cn4}) VALUES ({VID}, {VSnu}, {VIN}, {VName})".\
        format(tn=table_name, cn1=column1, cn2=column2, cn3=column3, cn4=column4, VID=ID, VSnu=Snu, VIN=IN, VName=Name))

Here's the entire script for context, if that helps:

import sqlite3

sqlite_file = '/test_database.sqlite'    # name of the sqlite database   file
table_name = 'test_table'
column1 = 'my_1st_column'
column2 = 'my_2nd_column'
column3 = 'my_3rd_column'
column4 = 'my_4th_column'
ID = int(123456)
Base = 'Arnold'
Snu = 'test'

conn = sqlite3.connect(sqlite_file)
c = conn.cursor()

c.execute("UPDATE {tn} SET {cn2}=('Snu'), {cn3}=('Muh'), {cn4}=('Arnold_A') WHERE {cn1}=({NID})".\
    format(tn=table_name, cn1=column1, cn2=column2, cn3=column3, cn4=column4, NID=ID))

i = 1
while(i<15):
if i == 1: IN = 'B'
if i == 2: IN = 'C'
if i == 3: IN = 'D'
if i == 4: IN = 'E'
if i == 5: IN = 'F'
if i == 6: IN = 'G'
if i == 7: IN = 'H'
if i == 8: IN = 'I'
if i == 9: IN = 'J'

ID = ID+1
i = i+1
Name = Base + '_' + IN
params = (Snu, IN, Name)

c.execute("INSERT OR IGNORE INTO {tn} ({cn1}, {cn2}, {cn3}, {cn4}) VALUES ({VID}, {VSnu}, {VIN}, {VName})".\
        format(tn=table_name, cn1=column1, cn2=column2, cn3=column3, cn4=column4, VID=ID, VSnu=Snu, VIN=IN, VName=Name))

if(i == 10): break

conn.commit()
conn.close()

It will write the "VID" item just fine if isolated (which is an integer column and the primary key of the table), but everything after that it interprets as a column and I get the "no such column: [value]" error.

I get this error regardless of what comes after "VID" -- be it any variable, as demonstrated with the command above, or if I try to insert a direct string value. These other columns are simply text columns, if that helps.

Try `'{Vsnu}'` or `{'Vsnu'}` -> string literals in SQL are required to be surrounded by single quotes. You should *really* look into using parameters for query values. — Mark Benningfield, Apr 21 '19 at 00:44

Allan · Accepted Answer · 2019-04-21T02:26:33.103

I know you are just learning, so don't be scared by what I'm going to say next :-)

You are improperly forming SQL commands. There's a special feature known as parameter substitution that is present in many database APIs, including that of SQLite in Python.

You don't want to concatenate values like you are doing, because it opens up room for something called SQL injection. In simple scenarios like yours it may not a problem, but when people do it in services exposed on the internet, it can cause great damage (data theft, data loss, data corruption etc). This comic illustrates the problem: https://xkcd.com/327/

In your case, writing the SQL command with parameter substitution is not only going to make your code safer and easier to read, it will also solve the problem you are seeing.

See the example below:

import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()

# Here we don't need parameter substitution
c.execute("CREATE TABLE mytable (name text, quantity int)")

# We set the values somewhere else. For example, this could come from a web 
# form
entry = ("myname", 2)

# Now we execute the query, with the proper parameter substitution.
# Note the `?` characters. Python will automatically replace those with the 
# values in `entry`, making sure that things are right when forming the 
# command before passing to SQLite.
c.execute("INSERT OR IGNORE INTO mytable VALUES (?, ?)", entry)

print(c.execute("SELECT * from mytable").fetchall())

The example above assumes you don't need to parametrize the column names. In your case, you are apparently reading all columns, so you don't really need to pass all the names, just do like I did in the example above and it will read all columns. If you really need column names to be parameters to read a subset of data, then you'll have to resort to a concatenation mechanism similar to what you were using. But in that case, as always, be really careful with user input to make sure it's not forming bad queries and commands.

More details here: https://docs.python.org/3/library/sqlite3.html

Getting "sqlite3.OperationalError: no such column:"

1 Answers1