Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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
share|improve this question
    
Can you fix the indentation please? –  BradHards Jan 13 '13 at 22:06

3 Answers 3

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.

share|improve this answer
    
+1 It's an issue of sanitising inputs (in this case as they're strings, they need to be escaped by quotes in the code, which using ? will do for you). Better yet, if you can build a generator with yield you can use cursor.executemany(statement, iterator) –  om_henners Feb 12 '13 at 23:25

I think:

 if ext == extension

should be:

 if ext == "extension"

unless extension is a variable set further up. If the latter then perhaps check what it is set to using a print statement.

share|improve this answer
    
i have changed the extension and it still does not work. i have also realised that in order to create a column called index in sqlite it has to be in "" or ``. I have not defined extension as a variable, in fact the first part of the code is basedir = '/Documents/Workingfiles/Asterdem2/Burundi/burundi' extension = '.tif' # do for '.sid' and '.sid' conn = lite.connect("ngi.sqlite") cur = conn.cursor() table = 'bbi_catalog_aug2012' columns = 'index,path,filename' –  Admire Dec 10 '12 at 8:45

First, the name index is reserved by SQLite ( SQLite: create Index ).

Then you need to review the string formatting in Python ( see for example Python String Format )

What you want is, in SQL (index is renamed ind):

'INSERT INTO bbi_catalog_aug2012 (ind,path,filename) VALUES ("...","...","....");'

In Python with the older "%" string formatter:

statement = "INSERT INTO %s (%s) VALUES (%s,%s,%s);" %(table,columns,1,2,3)

or with the new string.format()

 statement = "INSERT INTO {0} ({1}) VALUES ({2},{3},{4});".format(table,columns,1,2,3)

and the result is:

'INSERT INTO bbi_catalog_aug2012 (ind,path,filename) VALUES (1,2,3);'

So your script:

conn = sqlite.connect("ngi.sqlite")
with conn:
    cur = conn.cursor()
     for root, dirnames, filenames in os.walk(basedir):
      print 'root is ' + root
      for filename in filenames:
          ext = os.path.splitext(filename)[1].lower()
          if ext == extension:
                 index = filename[:9]
                 filebase = os.path.splitext(filename)[0]
                 fullpath = os.path.join(root,filename)
                 statement = "INSERT INTO {0} ({1}) VALUES ('{2}','{3}','{4}');".format(table,columns,index,fullpath,filebase)
                 cur.execute(statement)

The problem of replacing string formating by ? is not important here:

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (1.13. sqlite3 )

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.