MySQLdb delete query - TypeError: not all arguments converted during string formatting

Question

I'm trying to run a delete query using MySQLdb and passing 3 variables. The query is run as follows:

self.cursor.execute("""DELETE er.* FROM extension_recording er, 
  extension e, client c 
 WHERE er.extension_id = e.id AND e.client_id = c.id 
  AND c.parent_client_id = %s 
  AND DATE(er.cr_date) BETWEEN '%s' AND '%s'""" , 
(client_id, start_date, end_date))

Please excuse the indenting, couldn't seem to make it legible without.

And what happens is this: TypeError: not all arguments converted during string formatting

I've tried passing the SQL to the cursor as a string (I know this is vulnerable to SQL injection, was just testing) and the result is the same. I've examined the SQL and it seems fine.

Is this something to do with the single quoted dates in the query? Or something else?

Does it work if you change BETWEEN '%s' AND '%s' to BETWEEN %s AND %s? If not, what are the types of client_id, start_date, and end_date? — Aya
– Aya, Commented May 15, 2013 at 17:08
No that doesn't work, tried that already. Client ID is a string, the other two are dates. — btongeorge
– btongeorge, Commented May 15, 2013 at 18:25
I guess maybe a better question would be, how should I run the delete query using MySQLdb, given that I need to use dates as criteria? Is this the best way or is there some other method? — btongeorge
– btongeorge, Commented May 16, 2013 at 11:00
I just tried a similar example, and I can't reproduce the fault. You say you tried passing the SQL as a string, by which I assume you mean you interpolated the values before executing the query. Can you include the exact SQL you get when you do this? TBH, though, I don't see anything wrong with that code, so are you certain that's the query which is producing the error? Might help to include the full stack traceback. — Aya
– Aya, Commented May 16, 2013 at 14:02
@Aya Thanks for the continued help, here's the SQL before execution: DELETE er.* FROM extension_recording er, extension e, client c WHERE er.extension_id = e.id AND e.client_id = c.id AND c.parent_client_id = 0009 AND DATE(er.cr_date) BETWEEN '2013-05-01' AND '2013-05-15' — btongeorge
– btongeorge, Commented May 16, 2013 at 16:39

Aya · Accepted Answer · 2013-05-16 16:59:16Z

There's a clue in the traceback...

Traceback (most recent call last):
  File "/usr/local/bin/recording_archive_newer.py", line 194, in <module>
    print "Error %s deleting from DB" % (sys.exc_info())
TypeError: not all arguments converted during string formatting

The problem is not with the query, but with printing the error message.

sys.exc_info() returns a tuple of three elements, but you've only specified one placeholder in the string "Error %s deleting from DB".

It's worth noting that (sys.exc_info()) is not a tuple with one element, but is interpreted as sys.exc_info(). If you want to make it a one-element tuple, you need a trailing comma, i.e. (sys.exc_info(),).

However, if that line is part of a block like...

try:
    # do query
except:
    print "Error %s deleting from DB" % (sys.exc_info())

...you'd be better off re-raising the original exception, otherwise it'll be really difficult to work out where the actual problem is. I'd suggest changing it to something like...

try:
    # do query
except:
    print "Error deleting from DB"
    raise

...at least while you're debugging.

Collectives™ on Stack Overflow

MySQLdb delete query - TypeError: not all arguments converted during string formatting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related