Trouble with Myqsl

[Bengt Richter]

On Wed, 24 Apr 2002 23:11:45 -0000, Jon Ribbens <jon+usenet at unequivocal.co.uk> wrote:

>In article <aa7c52$ehu$0 at 216.39.172.122>, Bengt Richter wrote:
>> So is cc.execute(...) acting like
>> >>> def dummyccx(fmt, params):
>>  ...     for tup in params:
>>  ...         print fmt % tuple(map(repr,tup))
>>  ...
>> >>> dummyccx(
>>  ... "INSERT INTO tmp (k, s) VALUES (%s, %s)",
>>  ... [(1, "one"), (2, 'two'), (3, 'three')]
>>  ... )
>>  INSERT INTO tmp (k, s) VALUES (1, 'one')
>>  INSERT INTO tmp (k, s) VALUES (2, 'two')
>>  INSERT INTO tmp (k, s) VALUES (3, 'three')
>
>Sort of that one. Except it isn't 'repr', it's a function which
>examines the type of the parameter and converts it into appropriate
>MySQL representation (i.e. for strings put "'" at the beginning and
>end and quote various characters with a "\", for numbers convert them
>to strings, etc etc).
>
No surprise that it wasn't repr, though with subclassed types instantiated
just for passing as SQL parameters, I guess you could make it come close now.
I just wanted to make it clear that leaving "'" out of the format string
was not directly for the sake of SQL itself, but more for some intermediating
software's purposes.

>Oh, except you shouldn't be relying on the 'for tup in params', you
>should imagine it is doing:
>
>  def dummyccx(fmt, params):
>    print fmt % tuple(map(sql_quote, params))
>
I guess I could RTFM, but how do you do a single INSERT with many value tuples?
Does internal optimizing take care of that when it uses the list of tuples or
other sequence?

>Except it's more complicated than this because you might be using a
>mapping not a sequence for the parameters. See
>http://www.python.org/topics/database/DatabaseAPI-2.0.html
>for details.
Next time I want to do something with a DB, I'll have to try it with Python.
(I used the mySQL C API before).

Regards,
Bengt Richter