Python psycopg2 LINE 1: CREATE TABLE IF NOT EXISTS

Question

HELLO fellow programmers today i present you with a bug that i have in my script the debugger tells me psycopg2.errors.SyntaxError: syntax error at or near "user" LINE 1: CREATE TABLE IF NOT EXISTS score (user VARCHAR(255),highscor... i dont understand what is wrong with my syntax , the url is removed for privacy reasons its included in the actual code the script in savescore would run whenever the player would press the button Save Score then it would send the players name and highscore to my database

urlDATABASE = "urlnot shown "

up.uses_netloc.append("postgres")
urldb = up.urlparse(urlDATABASE)
connectiondb = psycopg2.connect(database=urldb.path[1:],
user=urldb.username,
password=urldb.password,
host=urldb.hostname,
port=urldb.port)

cur = connectiondb.cursor(cursor_factory=psycopg2.extras.DictCursor)
def savescore():
    global highscore
    global PlayerEntry
    Nume="score"#numele tabelului
    create=f"CREATE TABLE IF NOT EXISTS {Nume} (user VARCHAR(255),highscore VARCHAR(255))"
    cur.execute(create)#am creat tabelul 

    scriptadd= "INSERT INTO "+Nume+" (user,highscore) VALUES(%s,%s)"

    tupple=(PlayerEntry,highscore)

    cur.execute(scriptadd,tupple)#trimite informatiile la server ca sa execute scriptul

    connectiondb.commit()

1) As the error message said, "...syntax error at or near "user" ...", the issue is with `user`. That is because it is a [Reserved word](https://www.postgresql.org/docs/current/sql-keywords-appendix.html). My suggestion would be to change it to something like `user_name`. If you must use it then it has to be double quoted `"user"`. 2) **Do not** use `f` strings to build dynamic SQL. Use the `psycopg2` [sql](https://www.psycopg.org/docs/sql.html) module. — Adrian Klaver, Oct 24 '22 at 15:48

score 0 · Answer 1 · answered Oct 23 '22 at 20:37

0

You need to define, the table constraints. For example if they allow null or only unique values, so try this create variable instead.

create=f"CREATE TABLE IF NOT EXISTS {Nume} (user VARCHAR(255) NOT NULL, highscore VARCHAR(255) NOT NULL)"

Replace not null with null if you want to allow null.

answered Oct 23 '22 at 20:37

anarchy

3,709
2
16
48

No you don't. Per [Create Table](https://www.postgresql.org/docs/current/sql-createtable.html): *NULL The column is allowed to contain null values. This is the default.* You only need to specify `NOT NULL` if that is what you want. This answer is wrong and should be deleted. – Adrian Klaver Oct 24 '22 at 15:42

Python psycopg2 LINE 1: CREATE TABLE IF NOT EXISTS

1 Answers1