Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
+1 vote
2 views
in Python by (47.6k points)

I am trying to insert a data into a BLOB column in MySQL Server it is keep giving me this error:

ProgrammingError: not all arguments converted during string formatting

I could not define why so please help,

P.S. the type of the column in MySQL is set to LONGBLOB

here is my code:

#from mysql.connector import MySQLConnection, Error

import MySQLdb

def update_blob(filename):

    # read file

    pic = open(filename)

    data = pic.read()

    # prepare update query and data

    query = "UPDATE image " \

            "SET picture = ? "

    print data

    ###############

    hostname = ''

    username = ''

    password = ''

    database = ''

    try:

        conn = MySQLdb.connect( host=hostname, user=username, passwd=password, db=database )

        print 'connected'

        cursor = conn.cursor()

        cursor.execute(query, data)

        conn.commit()

    except Error as e:

        print(e)

    finally:

        cursor.close()

        conn.close()

and the error:

ProgrammingError                          Traceback (most recent call last)

<ipython-input-35-389eb7e8c3c0> in <module>()

----> 1 update_blob('hovik.jpg')

<ipython-input-34-48db763c9aee> in update_blob(filename)

     21         print 'connected'

     22         cursor = conn.cursor()

---> 23         cursor.execute(query, data)

     24         conn.commit()

     25     except Error as e:

>/usr/lib/python2.7/dist-packages/MySQLdb/cursors.pyc in execute(self, query, args)

    238                 query = query % args

    239             except TypeError as m:

--> 240                 self.errorhandler(self, ProgrammingError, str(m))

    241 

    242         if isinstance(query, unicode):

>/usr/lib/python2.7/dist-packages/MySQLdb/connections.pyc in defaulterrorhandler(***failed resolving arguments***)

     50         raise errorvalue

     51     if errorclass is not None:

---> 52         raise errorclass(errorvalue)

     53     else:

     54         raise Exception(errorvalue)

`ProgrammingError: not all arguments converted during string formatting`

1 Answer

0 votes
by (106k points)
edited by

Here is the solution you can remove two things and your error is gone the first thing you can not use ‘?’ because of the format specifier, and the second you also did not add the con for not only being able to retrieve but also to insert in the database,

here is the example of the code that worked for me:

import MySQLdb

hostname = ''

username = ''

password = ''

database = ''

myConnection = MySQLdb.connect( host=hostname, user=username, passwd=password, db=database )

def doQuery() :

    fin  = open("hovik.jpg",'rb')

    contents = fin.read()

    fin.close()

    with myConnection:

        cur = myConnection.cursor()

        sql = "INSERT INTO image VALUES (%s)"

        ret = cur.execute(sql, [contents])

doQuery()

myConnection.close()

To know more about this you can have a look at the following video:-

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...