I am attempting to use execute many to update two columns in a sqlite3 database. I am using a pandas dataframe to store information about some tiff image files- their height, width (both integers) and using their file name (string) as the WHERE clause.
import ImageQA
import os
import sqlite3
home_directory = os.getcwd()
image_file_set = ImageQA.get_image_size(shelfmark_reference,working_directory)
#image_count = len(image_file_set))
tiff_file_list = list(image_file_set['TiffFile'])
height_list = list(image_file_set['Height'])
width_list = list(image_file_set['Width'])
zipped = zip(height_list,width_list,tiff_file_list)
file_list = list(zipped)
os.chdir(home_directory)
conn = sqlite3.connect('DigiFolio')
cursor = conn.cursor()
cursor.executemany('UPDATE Capture_information SET Height = ?,Width = ? WHERE TiffFileName =?',file_list)
conn.close()
The code runs fine, but when I query the database to see if the information has been entered correctly the Height and Width columns remain empty.
I think I know the problem, but have not managed to resolve it. Using ? to pass the third parameter means that there are not any quotation marks around the string in the WHERE statement. However, if I put quotes around the question mark, I get the following error- "Incorrect number of bindings supplied. The current statement uses 2, and there are 3 supplied." Suggesting that the quotation marks render the ? not unrecognizable as a parameter.
It is not necessary to quote the string fields, in fact it can cause problems as you have discovered. However...
From the python sqlite API doc:
commit()
This method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.
Thank you so much. Relieved, if feeling a bit dopey, that it's such a simple fix.