ProgrammingError: not all arguments converted during string formatting

Question

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`

In your query you shouldn't have a '?' . In python %s acts as a format specifier. — SaberSz, Mar 15 '18 at 10:24
https://stackoverflow.com/questions/45110251/number-of-mysql-query-parameters-match-arguments-passed-to-execute-but-python-r and once that's fixed you'll run in to this https://stackoverflow.com/questions/23600286/python-mysql-connector-database-query-with-s-fails, because instead of a sequence of arguments, i.e. `(data, )`, you're passing an argument as the 2nd arg to `execute()`. — Ilja Everilä, Mar 15 '18 at 10:51
@Suzume you were right about the ? but apparently i had a another problem in my code, when i was connecting to MySQL Server i had to specify the conn in order to be able to make changes in the database. — Zaid, Mar 15 '18 at 12:33

Zaid · Accepted Answer · 2018-03-21T12:00:55.210

0

Sorted!!!! just found the solution,

1 - apparently i could not use ? because of the format specifier, 2 - and i also did not add the con for not only being able to retrive 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()

edited Mar 21 '18 at 12:00

answered Mar 15 '18 at 12:43

Zaid

69
1
9

BTW, it would be more concise to do like `with myConnection as cur: …`, because a MySQLdb connection used as a context manager produces a cursor. – glglgl Mar 20 '18 at 13:43
i tried it before it did not work for me do you have better example on your mind? @glglgl – Zaid Mar 21 '18 at 11:16
No, currently not. But be aware that the way you do it might lead to transactions not committed if you don't have auto-commit activated. A context manager commits a cursor automatically if everything goes fine and rolls it back in the case of an exception. An error description of "it did not work" is nothing I can work with. – glglgl Mar 21 '18 at 11:24

score 0 · Answer 2 · answered Mar 20 '18 at 13:38

According to the Python Database Specification in PEP 249, the format used in a query to show where to insert the parameters depends on the paramstyle member of the database module:

if it is qmark, use ? (question mark)
if it is numeric, use :1, :2 etc. (numeric, positional style)
if it is named, use :name (named style)
if it is format, use %s (ANSI C printf format codes)
if it is pyformat, use %(name)s (Python extended format codes)

AFAIR, MySQLdb uses format, so you should replace your ? with %s. (If MySQLdb would properly use prepared statements, it would be qmark and ? was the right way to go.)

ProgrammingError: not all arguments converted during string formatting

2 Answers2