I can't seem to be able to see for myself, what when wrong in this line of codes, have been staring at it for the last 20 min but I can't seem to figure anything out.
here is the error:
Traceback (most recent call last):
File "C:\Users\usera\PycharmProjects\itptesting_2\itptesting\init_db.py", line 7, in <module>
connection.executescript(f.read()),
sqlite3.OperationalError: near ")": syntax error
and the fullcode:
import sqlite3
connection = sqlite3.connect('database.db')
with open('schema.sql') as f:
connection.executescript(f.read())
cur = connection.cursor()
cur.execute("INSERT INTO posts (title, content) VALUES (?, ?)",
('First Post', 'Content for the first post')
)
cur.execute("INSERT INTO posts (title, content) VALUES (?, ?)",
('Second Post', 'Content for the second post')
)
cur.execute("INSERT INTO users (fullname, email, password) VALUES (?, ?, ?)",
('UserA', '[email protected]', 'asdfghjkl')
)
cur.execute("INSERT INTO entries (stockname, partnum, company, quantity, uom, remarks) VALUES (?, ?, ?, ?, ?, ?)",
('Beer','Albee0001','RES','24','BTL','new stock for promotions ')
)
connection.commit()
connection.close()
and schema.sql
DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created_timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL,
content TEXT NOT NULL
);
DROP TABLE IF EXISTS signup;
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
fullname TEXT NOT NULL,
email TEXT NOT NULL,
password TEXT NOT NULL
);
DROP TABLE IF EXISTS entries;
CREATE TABLE entries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
stockname TEXT NOT NULL,
partnum TEXT NOT NULL,
company TEXT NOT NULL,
quantity INTEGER NOT NULL,
uom TEXT NOT NULL,
date_recorded NOT NULL DEFAULT CURRENT_TIMESTAMP,
remarks TEXT NULL,
FOREIGN KEY(id) REFERENCES users(id),
);
I had tried deleting the database.db file and regenerating it by run init_db.py but it still gave me the same error. What should I do?
CodePudding user response:
You have a couple of problems, this is the correct syntax:
CREATE TABLE entries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
stockname TEXT NOT NULL,
partnum TEXT NOT NULL,
company TEXT NOT NULL,
quantity INTEGER NOT NULL,
uom TEXT NOT NULL,
date_recorded DATE NOT NULL DEFAULT (CURRENT_TIMESTAMP),
remarks TEXT,
FOREIGN KEY(id) REFERENCES users(id)
);