4

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 :(

Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

when i add backticks and run it ,i get this error-sqlite3.OperationalError: near "AUTO_INCREMENT": syntax error
@SakithKarunasena Try without the underscore.
no AUTOINCREMENT also do not work and i get the same error
@SakithKarunasena You'll need PRIMARY KEY right after INTEGER. (You can also drop the NOT NULL since the pkey makes it redundant.
All rowid table INTEGER PRIMARY KEY columns auto increment, btw. The AUTOINCREMENT keyword isn't needed unless you specifically want the changes it causes in the normal behavior: sqlite.org/autoinc.html
2

You can not name a table index. INDEX is a reserved keyword.

The documentation states:

The SQL standard specifies a large number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object.

Improve this answer

1 Comment

when i change the table name to search_index and run it i get this error-sqlite3.OperationalError: near "AUTO_INCREMENT": syntax error

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.