Problem with mediumtext field.

[Python Guy]

Im a neophyte python coder and Im having trouble with a mediumtext mysql field.

FDATA = "really big monster string"
Actually FDATA is a Mime encoded big string.

FNAME = "innocuous.jpg"

So then I have

dbc.execute(""" INSERT into TBLNAME (fname,fdata) VALUES (%s,%s) """ % (FNAME,FDATA)

I get error 1064 indicating Im giving it a wrong type. I know the %s for FDATA is the wrong format specifier, however I have no clue as to what would be correct here.

Little help?

[Andy Dustman]

And, as is the usual practice, you make the same mistake everyone else makes, even though the right way is in the docs and PEP-249. You should be doing this:

 |  execute(self, query, args=None)
 |      Execute a query.
 |
 |      query -- string, query to execute on server
 |      args -- optional sequence or mapping, parameters to use with query.
 |
 |      Note: If args is a sequence, then %s must be used as the
 |      parameter placeholder in the query. If a mapping is used,
 |      %(key)s must be used as the placeholder.
 |
 |      Returns long integer rows affected, if any

or in your case:

dbc.execute(""" INSERT into TBLNAME (fname,fdata) VALUES (%s,%s) """, (FNAME,FDATA) )

Note the use of comma instead of percent. %s is always the right format specifier, since it always gets a string: All arguments are converted to SQL literals (i.e. strings, with escaping as needed) before they are interpolated into the query. If you just use %, your strings won't be properly escaped, which is bad news.

I have no doubt this will fix your error.

Also see:

https://sourceforge.net/forum/forum.php?thread_id=1241324&forum_id=70461
http://www.python.org/peps/pep-0249.html

[Quentin Hartman]

I am trying to do something similar, and I am supposedly using the correct syntax according to your post:

cursor.execute("""insert into students (sid, name) values (%s, %s)""",(foo,bar))

where both foo and bar are variables containing strings. This gives me the error:

TypeError: argument 2 must be a mapping

I thought that this could take either a mapping or a sequence? I am sure I am doing something obviously wrong, but I haven't a clue what it might be.

[Andy Dustman]

I'd need to see more of the traceback.

[Quentin Hartman]

Here ya go:

./locatorpopulate.py
Traceback (most recent call last):
File "./locatorpopulate.py", line 55, in ?
cursor.execute("""insert into students (sid, name) values (%s, %s)""",(foo, bar))
File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 132, in execute
self.errorhandler(self, TypeError, m)
File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
TypeError: argument 2 must be a mapping

[Andy Dustman]

That error is being raised from inside _mysql.escape*(). Are you sure foo and bar are both strings? Are you making any changes to the conversion dictionary?

[Quentin Hartman]

I made sure those variables were strings by running them through the str() function of the strings module. They are being set from values in a list. I tried setting them like this:

foo = str(row[0])
bar = str(row[1])

And also by:

cursor.execute("""insert into students (sid, name) values (%s, %s)""",(str(foo), str(bar)))

and neither method changed the output of the traceback. I have also tried passing the list elements directly (which is ultimately what I would like to do) and that was no better.

[Andy Dustman]

Try doing this before your execute():

print db.literal( (foo,bar) )

where db is your database connection.

Also try it with (foo,) and (bar,).

[Quentin Hartman]

Well, it prints the data I expect to see , and then traces back, but the traceback is different:
("'12345'", "'DOE, JOHN ALBERT'")
Traceback (most recent call last):
File "./locatorpopulate.py", line 55, in ?
print conn.literal( (foo,bar) )
File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 181, in literal
return self.escape(o, self.converter)
TypeError: argument 2 must be a mapping

[Andy Dustman]

I can see from your earlier posts that you have Python-2.3, but you never did say what version of MySQLdb you had.

Also try printing the value of conn.converter before the call to literal().

[Quentin Hartman]

I was using 1.0 when I started, but I upgraded to 1.2 in the hopes that I might be triggering a boog of some kind. The behavior hasn't changed since the upgrade

Here's conn.converter:

{<type 'int'>: <function Thing2Str at 0xb7d2241c>, 0: <type 'float'>, 2: <type 'int'>, 3: <type 'long'>, 4: <type 'float'>, 5: <type 'float'>, 1: <type 'int'>, 8: <type 'long'>, 9: <type 'int'>, 10: <function Date_or_None at 0xb7d266bc>, 11: <function TimeDelta_or_None at 0xb7d2664c>, 12: <function DateTime_or_None at 0xb7d26614>, 13: <type 'int'>, 248: <function Str2Set at 0xb7d268b4>, <type 'list'>: <built-in function escape_sequence>, <type 'instance'>: <function Instance2Str at 0xb7d2256c>, <type 'NoneType'>: <function None2NULL at 0xb7d224fc>, <type 'array.array'>: <function array2Str at 0xb7d225dc>, <type 'datetime.timedelta'>: <function DateTimeDelta2literal at 0xb7d2672c>, 7: <function mysql_timestamp_converter at 0xb7d26764>, <type 'float'>: <function Float2Str at 0xb7d224c4>, <type 'str'>: <function string_literal at 0xb7d22b8c>, <type 'unicode'>: <function unicode_literal at 0xb7d246f4>, <type 'long'>: <function Thing2Str at 0xb7d2241c>, <type 'dict'>: <built-in function escape_dict>, <type 'object'>: <function Instance2Str at 0xb7d2256c>, 252: [(128, <function char_array at 0xb7d225a4>), (None, None)], <type 'datetime.datetime'>: <function DateTime2literal at 0xb7d266f4>, <type 'tuple'>: <built-in function escape_sequence>}

I can't say that I know what to do with all that. The rest of the output was unchanged.