Creating table in SQLite makes syntax error

Question

i have function that should make from xlsx file SQLite database, but i have syntax error and i cant find where. Please help. The error is: OperationalError: near "(": syntax error

code is here:

def xlsx_to_db(xlsx_file, con, db_name, cur):
    import pandas as pd
    cur.execute("""CREATE TABLE IF NOT EXISTS {0}(
        {1} INTEGER NOT NULL,
        {2} INTEGER NOT NULL,
        {3} INTEGER NOT NULL,
        {4} INTEGER NOT NULL,     
        {5} INTEGER NOT NULL
        PRIMARY KEY ({1},{2},{3},{4})
        )""".format("db1","[TypRefDokl]","[Č.refer.dokladu]","[Číslo dokladu]","[Řádka ref.dok.]","[Účetní řádka]"))
    
    
    dfs = pd.read_excel(xlsx_file, index_col=([1, 2, 3, 4]))
    for line in dfs.keys():
        try:
            cur.execute("ALTER TABLE {0} ADD {1} NOT NULL".format("db1","[" + line + "]"))
        except:
            pass
    dfs.to_sql(db_name, con, index=True, if_exists="append")
    con.commit()
    return dfs

Thank you.

Which `cur.exectue` line is raising the error? My first thought would be that those don't look like proper column names in the first `cur.execute`. — Kyle Parsons, Aug 11 '21 at 15:03

Zev · Accepted Answer · 2021-08-11T16:11:59.470

You seem just to be missing a comma. It should be {5} INTEGER NOT NULL, not {5} INTEGER NOT NULL.

I was suspicious of those column names as well but it seems like with those square brackets, they work (https://stackoverflow.com/a/9917205/1830793).

I used https://sqliteonline.com/ to validate the SQL so that should help if you have any other similar issues.

The reason you are getting the error message about the ( is because:

CREATE TABLE IF NOT EXISTS {0}(
        {1} INTEGER NOT NULL,
        {2} INTEGER NOT NULL,
        {3} INTEGER NOT NULL,
        {4} INTEGER NOT NULL,     
        {5} INTEGER NOT NULL PRIMARY KEY

would actually be valid so the first invalid thing is the parentheses.

Creating table in SQLite makes syntax error

1 Answers1