sqlite3.OperationalError: near "index": syntax error

Question

I am trying to connect to a database with python using sqlite3 module and i gets an error - sqlite3.OperationalError: near "index": syntax error

---

I searched some solutions for this but i did not got the solution. I am new to sqlite3

---

def insert_into_db(url, title, description, keywords):
    con = sqlite3.connect('index.db')
    c = con.cursor()
    create = r'''CREATE TABLE IF NOT EXISTS index (id INTEGER NOT NULL AUTO_INCREMENT,url VARCHAR,description TEXT,keywords TEXT);INSERT INTO index(url, title, description, keywords)VALUES('{}','{}',{}','{}');'''.format(url, title,description, keywords)
    c.execute(create)
    con.commit()
    con.close()

---

help me to get rid of this error :(

Answer 1

INDEX is a keyword in SQLite3. Thus, it'll be parsed as a keyword. There are several ways around this, though.

According to the documentation, you could use backticks or quote marks to specify it as a table name. For example,

CREATE TABLE IF NOT EXISTS `index` ...

or

CREATE TABLE IF NOT EXISTS "index" ...

may work.

You can pass arguments to your sql statement from the execute() command. Thus,

create = r'''CREATE TABLE ... VALUES(?,?,?,?);'''  # use ? for placeholders
c.execute(create, (url, title, description, keywords))  # pass args as tuple

This is more secure compared to formatting your arguments directly with Python.

Note also that SQLite's syntax for autoinc is AUTOINCREMENT without the underscore and they require the field to also be an INTEGER PRIMARY KEY.