Emmanu Emmanu - 7 months ago 100
SQL Question

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

I have a table called 'authors' in database 'menagerie'.Which has two columns Id and photo.Id is INT and photo is BLOB.when I try to store image in to mysql database table I am getting Error as

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 = %s WHERE id = %s"

#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()