MySQLdb module, method executemany with anything other than strings?

[Steve Holden]

olekristianvillabo at gmail.com wrote:
> The method cursor.executemany is there in order to avoid multiple calls
> to cursor.execute().
> 
> I have tried, with success, to do like every single example (that I
> have found on the www) on the subject shows, to use a insert statement
> on the form:
> statement = INSERT INTO table (colA,colB,colC) values (%s,%s,%s)
> 
> and pass in a list containing tuples
> list = [('bla','bla','bla'),('bla','bla','bla'),('bla','bla','bla')]
> 
> on the form
> 
> cursor.executemany(statement,list)
> 
> This works fine for all strings, but I have never been able to insert a
> single integer or a float using this method. I get an error message
> reporting that float (or an int) is required.
> 
> Statement is then of course changed to something like
> statement = INSERT INTO table (colA,colB,colC) values (%s,%i,%f)
> list = [('bla',1,0.65),('bla',3,3.7),('bla',3,0.9)]
> 
> Havee anybody experienced similar problems?
> Am I doing something wrong?
> Any feedback is greatly appreciated.
> 
> 
> Here is som real output from the interpreter:
> 
>>>>statement = 'insert into testtable3 (url,probability) values (%s,%f)'
                                                                      ^^
That's your problem, right there.
>>>>l
> 
> [('url1', 0.98999999999999999), ('url2', 0.89000000000000001)]
> 
>>>>cursor.executemany(statement,l)
> 
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File "C:\Python24\Lib\site-packages\MySQLdb\cursors.py", line 181, in
> execu
> any
>     self.errorhandler(self, TypeError, msg)
>   File "C:\Python24\Lib\site-packages\MySQLdb\connections.py", line 33,
> in de
> lterrorhandler
>     raise errorclass, errorvalue
> TypeError: float argument required
> 
It's just that you should use "%s" for *all* parameters, no matter what 
their type:

 >>> conn = db.connect()
 >>> curs = conn.cursor()
 >>> curs.execute("""
... create table thingy(
...    f1 char(10) primary key,
...    f2 float)""")
0L
 >>> l = [('url1', 0.98999999999999999), ('url2', 0.89000000000000001)]
 >>> curs.executemany("""
... insert into thingy (f1, f2) values (%s, %s)""", l)
2L
 >>>

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC             http://www.holdenweb.com/