OperationalError when inserting into sqlite 3 in Python

Question

I'm populating a database with data that I parse from JSON. When I execute my INSERT statement, I get an error: sqlite3.OperationalError: no such column: None. Some of the JSON data returns null, which would cause Python to insert None into the table, but I believe this should be fine? Does anyone know what the problem is?

Traceback: Traceback (most recent call last): File "productInfoScraper.py", line 71, in <module> ")")

My INSERT statement in Python:

cursor.execute("INSERT INTO ProductInfo VALUES(" +
        str(data["data"]["product_id"]) + ", " +
        "'" + str(data["data"]["product_name"]) + "'" + ", " +
        "'" + str(data["data"]["ingredients"]) + "'" + ", " +
        "'" + str(data["data"]["serving_size"]) + "'" + ", " +
        str(data["data"]["calories"]) + ", " +
        str(data["data"]["total_fat_g"]) + ", " +
        str(data["data"]["total_fat_percent"]) + ", " +
        str(data["data"]["fat_saturated_g"]) + ", " +
        str(data["data"]["fat_saturated_percent"]) + ", " +
        str(data["data"]["fat_trans_g"]) + ", " +
        str(data["data"]["fat_trans_percent"]) + ", " +
        str(data["data"]["cholesterol_mg"]) + ", " +
        str(data["data"]["sodium_mg"]) + ", " +
        str(data["data"]["sodium_percent"]) + ", " +
        str(data["data"]["carbo_g"]) + ", " +
        str(data["data"]["carbo_percent"]) + ", " +
        str(data["data"]["carbo_fibre_g"]) + ", " +
        str(data["data"]["carbo_fibre_percent"]) + ", " +
        str(data["data"]["carbo_sugars_g"]) + ", " +
        str(data["data"]["protein_g"]) + ", " +
        str(data["data"]["vitamin_a_percent"]) + ", " +
        str(data["data"]["vitamin_c_percent"]) + ", " +
        str(data["data"]["calcium_percent"]) + ", " +
        str(data["data"]["iron_percent"]) + ", " +
        "'" + str(data["data"]["micro_nutrients"]) + "'" +  ", " +
        "'" + str(data["data"]["tips"]) + "'" + ", " +
        str(data["data"]["diet_id"]) + ", " +
        "'" + str(data["data"]["diet_type"]) + "'" +
        ")")

My CREATE TABLE statement:

cursor.execute("CREATE TABLE ProductInfo(product_id INT, product_name TEXT,\
  ingredients TEXT, serving_size TEXT, calories INT, total_fat_g INT,\
  total_fat_percent INT, fat_saturated_g INT, fat_saturated_percent INT,\
  fat_trans_g INT, fat_trans_percent INT, cholesterol_mg INT, sodium_mg\
  INT, sodium_percent INT, carbo_g INT, carbo_percent INT, carbo_fibre_g\
  INT, carbo_fibre_percent INT, carbo_sugars_g INT, protein_g INT,\
  vitamin_a_percent INT, vitamin_c_percent INT, calcium_percent INT,\
  iron_percent INT, micro_nutrients TEXT, tips TEXT, diet_id INT, diet_type\
  TEXT)")

Building insert statements this way is a very bad idea. Besides the security/safety issue demonstrated in that comic, it's also hard to get all the quoting right, and hard to debug simple typos. — abarnert
– abarnert, Commented Aug 28, 2013 at 20:42
@abanert How would I do this properly? Something like cursor.execute("INSERT INTO Table VALUES(?, '?')", a, b) or something else entirely? — Corey
– Corey, Commented Aug 28, 2013 at 20:47
Well, you don't put quotes around the placeholders—part of the whole point is that the DB takes care of quoting for you. Also, named placeholders would be a lot more readable here than positional. See my answer for details. (Also, as a side note, it's usually considered a bad idea to rely on the order of the columns by using an INSERT without a column list. However, I don't think that's your problem here.) — abarnert
– abarnert, Commented Aug 28, 2013 at 20:49
Also, if you read the little tutorial at the start of the docs for sqlite3, it shows exactly how to do this right, and explains why. It's worth reading that, and also skimming the rest of the doc, because there's a lot of useful information there, and it's well written. — abarnert
– abarnert, Commented Aug 28, 2013 at 20:56

Community · Accepted Answer · 2020-06-20 09:12:55Z

First, you should never build SQL statements this way. As the documentation explicitly says:

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see http://xkcd.com/327/ for humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution.

And there are other problems besides the security problem—it's hard to get the quoting and converting right, very easy to make a simple mistake somewhere, and very hard to debug such mistakes when you make them.

The right way to do this is to use a named placeholder for each key, and pass your data["data"] as the parameter mapping:

cursor.execute("""INSERT INTO ProductInfo VALUES(
    :product_id, 
    :product_name,
    ...
    :diet_type)""", data["data"])

I have no idea whether that will solve your problem without enough sample code and data to test it, but it will eliminate many potential sources of error, so I'd give good odds it will.

The most likely problem is that one of the values you're trying to insert and aren't wrapping in quotes, str(data["data"]["sodium_mg"]), is a Python None value.

Just putting an unquoted None into a SQL statement means that you want to copy the value of the column named "None" to this column. If you want to use a null value, or the string "None", or something else, you have to write that properly.

Parameter binding will take care of that for you automatically by inserting a null value.

But this is just one example of what I meant by "hard to get the quoting and converting right", so I can't be sure it's exactly the one you're seeing.

Thanks for the advice, I will try again with your suggestions.

Collectives™ on Stack Overflow

OperationalError when inserting into sqlite 3 in Python

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