Kane Chew Kane Chew - 3 years ago 175
Python Question

Unable to append dataframe into sqlite3

I am trying to insert a dataframe into sqlite. My existing dataframe is as follows:

---------------------------------------
|date |location|location_code|
---------------------------------------
0 |12/14/2016 | FL | 2
1 |12/15/2016 | NY | 3


My python 3 code:

import sqlite3

conn = sqlite3.connect('transaction.sqlite')
cur = conn.cursor()

cur.executescript('''
DROP TABLE IF EXISTS New;

CREATE TABLE New (
index INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
date TEXT,
location TEXT,
location_code INTEGER)
''')

df.to_sql("Records", conn, if_exists="append")

conn.commit()
conn.close()


When I run the code, I get the following error:

Traceback (most recent call last):
File "C:/dbtest.py", line 15, in <module>
''')
sqlite3.OperationalError: near "index": syntax error


I know that when I change the word index to another word, it works.

However, I have no problem creating a field called index in the DB Browser for sqlite.

Could someone advice or help?

Thanks.

Answer Source

As you've noticed, index is a reserved word, and you cannot use it for a column name, at least not as-is - you'd have to escape it, using double quotes ("):

cur.executescript('''
    DROP TABLE IF EXISTS New;

    CREATE TABLE New (
        "index"  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
        date   TEXT,
        location TEXT,
        location_code INTEGER)
''')

The DB Browser probably escapes names automatically, allowing you to call a column you create there by a reserved word.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download