Trouble with Myqsl

[Bengt Richter]

On Wed, 24 Apr 2002 08:31:09 -0400, "Steve Holden" <sholden at holdenweb.com> wrote:

>"Jon Ribbens" <jon+usenet at unequivocal.co.uk> wrote in message
>news:slrnacc59a.tg8.jon+usenet at snowy.squish.net...
>> In article <u3ex8.94967$T%5.10614 at atlpnn01.usenetserver.com>, Steve Holden
>wrote:
>> > This error was caused by Fernando's following a bogus suggestion of
>mine.
>> > His original SQL syntax was correct
>>
>> Uh, no it wasn't, he was using '%s' instead of %s. I'm confused now.
>
><sigh> Sorry, I should have had the courage of my own convictions. When
>Fernando told me he was still getting errors on his SQL I assumed that the
>MySQLdb module required delimiters around string parameters. You're right,
>this doesn't make any sense. You should have been confused. It eventually
>transpired that Fernando had an install problem, which a clean reinstall
>fixed.
>
>>>> import MySQLdb
>>>> c = MySQLdb.connect(db="pwpsite")
>>>> cc = c.cursor()
>>>> cc.execute("SHOW TABLES")
>5L
>>>> cc.fetchall()
>(('chapter',), ('comment',), ('part',), ('remark',), ('reviews',))
>>>> cc.execute("CREATE TABLE tmp (k integer primary key, s char(12))")
>0L
>>>> cc.execute("INSERT INTO tmp (k, s) VALUES (%s, %s)",
>...     [(1, "one"), (2, 'two'), (3, 'three')] )
>3L
>
>So, for the record, you should *not* put SQL string quotes around parameter
>markers in your parameterized SQL statements.
>
So is cc.execute(...) acting like
 >>> def dummyccx(fmt, params):
 ...     for tup in params:
 ...         print fmt % 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)

or
 >>> 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')

or
 >>> def dummyccx(fmt, params):
 ...     ins = fmt.split('VALUES')
 ...     out = []
 ...     for tup in params:
 ...         out.append(ins[1] % tuple(map(repr,tup)))
 ...     print ins[0]+'VALUES'+', '.join(out)
 ...
 >>> dummyccx(
 ... "INSERT INTO tmp (k, s) VALUES (%s, %s)",
 ... [(1, "one"), (2, 'two'), (3, 'three')]
 ... )
 INSERT INTO tmp (k, s) VALUES (1, 'one'),  (2, 'two'),  (3, 'three')

where print is taking the place of passing actual SQL to mySQL.

IOW, I think mySQL wants quotes around 'one' 'two' and 'three', so it's
probably being arranged somewhere?

Regards,
Bengt Richter