Unable to append dataframe into sqlite3

Question

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.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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.

Unable to append dataframe into sqlite3

Sign up