SQLite3 Syntax Error: Unable to Create Tables in Python

Question

I encountered an issue while building my database. Whenever I try to run the function provided below, I encounter an unspecified syntax error.

import sqlite3

def create_all_sql():
    with sqlite3.connect("data.db", check_same_thread=False) as con:
        c = con.cursor()

        c.execute("""CREATE TABLE IF NOT EXISTS users (
            user_id TEXT UNIQUE PRIMARY KEY,
            user_name TEXT,
            hashed_password BLOB,
            hashed_salt BLOB,
            device_ver BLOB UNIQUE
        )""")

        c.execute("""CREATE TABLE IF NOT EXISTS devices (
                        device_id BLOB UNIQUE PRIMARY KEY,
                        user_id TEXT,
                        FOREIGN KEY (user_id) REFERENCES users(user_id),
                        device_name TEXT,
                        device_type TEXT,
                        north_cord TEXT,
                        east_cord TEXT,
                        timestamp TEXT
                    )""")
create_all_sql()

Error Message:

Traceback (most recent call last):
  File "", line 28, in <module>
    create_all_sql()
  File "", line 15, in create_all_sql
    c.execute("""CREATE TABLE IF NOT EXISTS devices (
sqlite3.OperationalError: near "device_name": syntax error

I've tried searching for similar issues but couldn't find any matches. Can anyone help me understand what might be causing this error and how to fix it? Any assistance would be greatly appreciated. Thank you!

Constraints go after column definitions. So move the FOREIGN KEY to the end. — Barmar
– Barmar, Commented Mar 11, 2024 at 22:42
Whenever you get a syntax error, the first place you should look is the syntax documentation. If that doesn't clear it up, you can post here. — Barmar
– Barmar, Commented Mar 11, 2024 at 22:43

denik · Accepted Answer · 2024-03-13 02:18:46Z

you forgot to save the database changes

import sqlite3

def create_all_sql():
    with sqlite3.connect("data.db", check_same_thread=False) as con:
        c = con.cursor()

        c.execute("""CREATE TABLE IF NOT EXISTS users (
            user_id TEXT UNIQUE PRIMARY KEY,
            user_name TEXT,
            hashed_password BLOB,
            hashed_salt BLOB,
            device_ver BLOB UNIQUE
        )""")

        c.execute("""CREATE TABLE IF NOT EXISTS devices (
                        device_id BLOB UNIQUE PRIMARY KEY,
                        user_id TEXT,
                        FOREIGN KEY (user_id) REFERENCES users(user_id),
                        device_name TEXT,
                        device_type TEXT,
                        north_cord TEXT,
                        east_cord TEXT,
                        timestamp TEXT
                    )""")
        con.commit()
        con.close()
create_all_sql()

Collectives™ on Stack Overflow

SQLite3 Syntax Error: Unable to Create Tables in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related