CODEWITHSUNDEEP
pythonsqlsqlite
Corey Wu
Corey Wu

Reputation: 1159

OperationalError when inserting into sqlite 3 in Python

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)")

Upvotes: 0

Views: 1989

Answers (1)

abarnert
abarnert

Reputation: 365617

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.

Upvotes: 2

Related Questions