Emmanu Emmanu - 7 months ago 162
Python Question

Python TypeError: not enough arguments for format string when running mysql query

when I try to store image in to mysql database table using the command

query = "UPDATE authors SET photo = {} WHERE id = ()".format(data,author_id)


I am getting Error as

Traceback (most recent call last):
File "C:/Users/Emmanu/PycharmProjects/ImageClassification-working/db1.py", line 29, in <module>
main()
File "C:/Users/Emmanu/PycharmProjects/ImageClassification-working/db1.py", line 26, in main
update_blob(1,"d:/Emmanu/project-data/bc1.jpg")
File "C:/Users/Emmanu/PycharmProjects/ImageClassification-working/db1.py", line 20, in update_blob
cursor.execute(query, (author_id,))
File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 187, in execute
query = query % tuple([db.literal(item) for item in args])
TypeError: not enough arguments for format string


My full code is

import MySQLdb

conn = MySQLdb.connect("localhost","root","rset","menagerie" )
cursor = conn.cursor()


def read_file(filename):
with open(filename, 'rb') as f:
photo = f.read()
return photo

def update_blob(author_id, filename):
# read file
data = read_file(filename)

# prepare update query and data
query = "UPDATE authors SET photo = {} WHERE id = ()".format(data,author_id)

#query blob data form the authors table
cursor.execute(query, (author_id,))
photo = cursor.fetchone()[0]
cursor.close()
conn.close()

def main():
update_blob(1,"d:/Emmanu/project-data/bc1.jpg")

if __name__ == '__main__':
main()

Answer

Try with the following changes

query = "UPDATE authors SET photo = %s WHERE id  = %s"
cursor.execute(query, (data, author_id))

%s is a placeholder that is converted into a SQL literal to handle proper escaping (and avoid SQL injection).

Not sure what you were trying to do with .format(data, author_id), and then providing author_id as a one item tuple in your original execute call, but see if the proposed changes work for you.

With these changes you would end up with

import MySQLdb

conn = MySQLdb.connect("localhost","root","rset","menagerie" )
cursor = conn.cursor()


def read_file(filename):
    with open(filename, 'rb') as f:
        photo = f.read()
    return photo

def update_blob(author_id, filename):
    # read file
    data = read_file(filename)

    # prepare update query and data
    query = "UPDATE authors SET photo = %s WHERE id  = %s"

    #query blob data form the authors table
    cursor.execute(query, (data, author_id))
    cursor.close()
    conn.close()

def main():
     update_blob(1,"d:/Emmanu/project-data/bc1.jpg")

if __name__ == '__main__':
    main()
Comments