Yet Another MySQL Problem

[Kushal Kumaran]

On Thu, 2010-05-27 at 20:47 +0530, Kushal Kumaran wrote:
> 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".
> 

Since I'm in a good mood today, here's a little present:

def insert(cursor, table, columns, values):
    """Insert a row into a table.  columns must be a list of column
    names.  values must be a list of values for the new row.  The
    columns and values must correspond."""
    assert len(columns) == len(values)

    stmt = """
insert into %s (%s) values (%s)
""" % (table,
       ', '.join(columns),
       ', '.join('%s' * len(values)))
    logging.debug('stmt: %s, values: %s' % (stmt, values))

    cursor.execute(stmt, values)

Hope it helps.

-- 
regards,
kushal