Yet Another MySQL Problem

[Kushal Kumaran]

On Thu, 2010-05-27 at 10:30 -0400, Victor Subervi wrote:
> On Thu, May 27, 2010 at 10:17 AM, Kushal Kumaran
> <kushal.kumaran at gmail.com> wrote:
>         
>         On Thu, 2010-05-27 at 08:34 -0400, Victor Subervi wrote:
>         > Hi;
>         > I have this code:
>         >
>         >     sql = "insert into %s (%s) values ('%%s');" %
>         (personalDataTable,
>         > string.join(cols[1:], ', '))
>         > #    cursor.execute(sql, string.join(vals[1:], "', '"))
>         >     cursor.execute('insert into %s (%s) values ("%s");' %
>         > (personalDataTable, string.join(cols[1:], ', '),
>         string.join(vals[1:],
>         > '", "')))
>         >
>         > Now, if I uncomment the 2nd line and comment the third, the
>         command
>         > fails because, apparently, that "');" at the tail end of sql
>         (1st
>         > line) gets chopped off. Why??
>         
>         
>         That's not why it is failing.
>         
>         The second argument to cursor.execute must be a tuple of
>         values that
>         will be escaped and interpolated into the query.  You are
>         passing in a
>         string instead.
> 
> So I tried this:
> 
>     sql = "insert into %s (%s) values (%%s);" % (personalDataTable,
> string.join(cols[1:], ', '))
>     cursor.execute(sql, vals[1:])
> 
> and got this:
> 

> <snip>

> query = 'insert into doctorsPersonalData (Store, FirstNam...OB, Email,
> PW, State, ShippingState) values (%s);', db = <weakproxy at
> 0x2b4c17e707e0 to Connection>, db.literal = <bound method
> Connection.literal of <_mysql.connection open to 'localhost' at
> e6b08c0>>, args = ['prescriptions', 'Beno', 'Candelon', '123', '456',
> '789', '11 here', '', 'csted', '00820', '22 there', '', 'csted',
> '00820', '2000-01-01', 'benoismyname', '12345', 'CA', 'AR']
> 
> TypeError: not all arguments converted during string formatting
>       args = ('not all arguments converted during string
> formatting',)  
> 
> 
> You sure about not converting to string??
> 

Yep, pretty sure.  You still need to have as many %s in the query string
as the number of values.  Since you seem to be passing in 19 values (by
a rough count), the query string must be like this:

insert into doctorsPersonalData (Store, FirstNam.....) values
(%s, %s, %s, ...19 of these)

not

insert into doctorsPersonalData (Store, FirstNam.....) values
(%s)

The exception with the "not all arguments converted" message says you
have too few %s.

> 
> 
>         Also, lose the single quotes around the %s.
> 
> Well, sure, if not converting to string. Otherwise it's needed. Dennis
> advised not using quotes, but what he meant was not using double
> quotes. Single quotes, I have found by experimentation, do work.

You seem to have a strange definition of "work".

-- 
regards,
kushal