ProgrammingError: not all arguments converted during string formatting [duplicate]

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
– SaberSz, Commented Mar 15, 2018 at 10:24
stackoverflow.com/questions/45110251/… and once that's fixed you'll run in to this stackoverflow.com/questions/23600286/…, because instead of a sequence of arguments, i.e. (data, ), you're passing an argument as the 2nd arg to execute(). — Ilja Everilä
– Ilja Everilä, Commented Mar 15, 2018 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
– Zaid, Commented Mar 15, 2018 at 12:33

glglgl · Accepted Answer · 2018-03-20 13:38:25Z

0

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.)

answered Mar 20, 2018 at 13:38

glglgl

91.5k13 gold badges157 silver badges230 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zaid · Accepted Answer · 2018-03-21 12:00:55Z

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, 2018 at 12:00

answered Mar 15, 2018 at 12:43

Zaid

691 silver badge9 bronze badges

3 Comments

glglgl Over a year ago

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.

Zaid Over a year ago

i tried it before it did not work for me do you have better example on your mind? @glglgl

glglgl Over a year ago

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.

Collectives™ on Stack Overflow

ProgrammingError: not all arguments converted during string formatting [duplicate]

2 Answers 2

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Linked

Related