Forgon Forgon - 6 months ago 25
Python Question

near "-": syntax error when trying to create a table with a foreign key in sqlite using python

I was playing around with sqlite in Python and trying to do some tutorials but when I try to create a table with a foreign key I get the "near "-": syntax error".

Here is my code

import sqlite3 as lite

conn = lite.connect("hospital.db")

c = conn.cursor()
c.execute("""CREATE TABLE IF NOT EXISTS Department(did INTEGER PRIMARY KEY AUTOINCREMENT, deptName TEXT)""")
c.execute("""CREATE TABLE IF NOT EXISTS Room(rid INTEGER PRIMARY KEY AUTOINCREMENT, nbBeds INT, nbEmptyBeds INT)""")
c.execute("""CREATE TABLE IF NOT EXISTS Patient(pid INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, lastname TEXT, address TEXT, telephone TEXT, e-mail TEXT, admitttanceDate TEXT, dischargeDate TEXT, FOREIGN KEY (roomId) REFERENCES Room(rid))""")
c.execute("""CREATE TABLE IF NOT EXISTS Employee(eid INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, lastname TEXT, address TEXT, telephone TEXT, e-mail TEXT, role TEXT, employmentStart TEXT, employmentEnd TEXT, employmentDays INT, FOREIGN KEY (patientId) REFERENCES Patient(pid), FOREIGN KEY(dept_id) REFERENCES Department(did))""")

conn.close()


I don't have any "-" character in any of my tables, so I do not understand what's wrong here.

Answer

e-mail is not a valid column identifier. Either put the name in double quotes, square brackets or backticks, or remove the -:

"e-mail" TEXT
[e-mail] TEXT
`e-mail` TEXT
email TEXT

should all work.

Comments