python-3.xsqlitepysqlite

sqlite3.OperationalError: no such column: key


I have a error, it's sqlite3.OperationalError: no such column: key, but i don't know whats the problem. I tried changing the .db file name from apiKeys.db to key.db but still raises the error sqlite3.OperationalError: no such column: key. The code is

class Auth:
    conn = sqlite3.connect("key.db")
    c = conn.cursor()
    c.execute("""CREATE TABLE IF NOT EXISTS key (
                key)""")
    def add(key):
        conn = sqlite3.connect("key.db")
        c = conn.cursor()
        with conn:
            c.execute("INSERT INTO key VALUES (key=:key)",
                {'key': key})
        return True
    def get_all():
        conn = sqlite3.connect("key.db")
        c = conn.cursor()
        c.execute("SELECT * FROM key")
        return c.fetchall()
    def check(key):
        conn = sqlite3.connect("key.db")
        c = conn.cursor()
        c.execute("SELECT * FROM key")
        keys = c.fetchall()
        if key in keys:
            return True
        else:
            return False
    def remove(key):
        conn = sqlite3.connect("key.db")
        c = conn.cursor()
        with conn:
            c.execute("DELETE FROM key WHERE (key=:key)",
                {"key": key})
        return True

i added

conn = sqlite3.connect("key.db")
c = conn.cursor()

to the start of every function because without it, it would always raise an error.


Solution

  • This INSERT INTO key VALUES (key=:key) is not correct execute syntax. Only the placholder should be in the VALUES clause, ie VALUES (:key). (The remove method has similar problem).