Sqlite3 - unrecognized token

Question

Hello! You can help me?

I’ve tried it in many ways, but I couldn’t understand.

With up to 3 variables this way works, but with 7 variables presents the error in the image below.

I’ve been stuck in this problem for over six hours.

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import sqlite3

a1,a2,a3,a4,a5,a6,a7 = "a","b","c","d","e","f","g"


def save(a1,a2,a3,a4,a5,a6,a7):
    conn = sqlite3.connect('5w2h.db')
    bd = conn.cursor()
    bd.execute("""CREATE TABLE IF NOT EXISTS 5w2h (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        who TEXT, 
        where TEXT, 
        when TEXT, 
        what TEXT, 
        why TEXT, 
        how TEXT, 
        how_much TEXT);""")

    bd.execute("""INSERT INTO 5w2h (who, where, when, what, why, how, how_much) VALUES (?,?,?,?,?,?,?);""", (a1,a2,a3,a4,a5,a6,a7))
    conn.commit()
    conn.close()


save(a1,a2,a3,a4,a5,a6,a7)

Answer 1

You are creating a table that starts with a number, 5w2h, in addition to the name of one of the fields, Where, is reserved word within SQL. One way to solve is to put them all in quotes:

sqlite> CREATE TABLE IF NOT EXISTS "5w2h" ( id INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL, "who" TEXT, "where" TEXT, "when" TEXT, "what" TEXT, "why" TEXT,
"how" TEXT, "how_much" TEXT);

The problem is that you will always have to remember to keep putting the names in quotes with each command, see:

No quotes/apostrophes (table name error)

sqlite> INSERT INTO 5w2h (who, where, when, what, why, how, how_much) VALUES
("a","b","c","d","e","f","g");
Error: unrecognized token: "5w2h"

Table name between quotation marks (error with where out of place)

INSERT INTO "5w2h" (who, where, when, what, why, how, how_much) VALUES
("a","b","c","d","e","f","g");
Error: near "where": syntax error

All quotes names (command executed successfully)

sqlite> INSERT INTO "5w2h" ("who", "where", "when", "what", "why", "how",
"how_much") VALUES ("a","b","c","d","e","f","g");

The best way to solve the problem is not to use them (tables and fields started with numbers or reserved words) or to prefix them with something, like table_5w2h, text_where etc..