SQLite3 - unrecognized token

-1

Hello! Can you help me?

I have tested in several ways, but I could not understand.

With up to 3 variables this form works, however with 7 variables it presents the error in the image below.

I've been stuck with this problem for more than 6 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)

    
asked by anonymous 09.12.2018 / 02:14

1 answer

0

You are creating a table that starts with a number, 5w2h , and the name of one of the where fields is reserved within SQL. One way to solve it is to put them all in quotation marks:

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 naming the quotes in each command, see:

No quotation marks / apostrophes (error with table name)

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

Quoted table name (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 names in quotation marks (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 beginning with numbers or reserved words) or prefix them with something, type table_5w2h , text_where etc.

    
09.12.2018 / 11:29