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.kuma...@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
--
http://mail.python.org/mailman/listinfo/python-list
