Warm tip: This article is reproduced from stackoverflow.com, please click
python sql sqlite

Using executemany in SQLite3 for an UPDATE statement

发布于 2020-03-27 15:45:43

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.

Questioner
Sotirios
Viewed
44
DinoCoderSaurus 2020-01-31 20:15

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.