[GIS] Why is SQLite python command not working

pythonsqlite

I am fairly new to python and am writing a code to read imagery and insert them into an SQLite database but I get the following error 

cur.execute(statement, (index,fullpath,filename)) 
sqlite3.OperationalError: near "index": syntax error.

What is it that I am getting wrong? The following is the code:

for root, dirnames, filenames in os.walk(basedir):
    print 'root is ' + root
    for filename in filenames:
        ext = os.path.splitext(filename)[1].lower() #extension as lowercase
            if ext == extension:
                index = filename[:9] #tile ID
            filebase = os.path.splitext(filename)[0] #first part of filename
                fullpath = os.path.join(root,filebase) + ext #build up path again
                statement = "INSERT INTO %s (%s) VALUES " % (table,columns) + "(%s,%s,%s);"
                cur.execute(statement, (index,fullpath,filename))
            print 'catalog entry: ' + index + ', ' + fullpath

Best Answer

It's not %s in your SQL prepared statements, it's ?.

statement = "INSERT INTO %s (%s) VALUES (?, ?, ?);" % (table,columns)

Also, you can define statement far earlier in your code (before the walk) and just reuse it.

Related Question