Prev: Minor annoyances with properties
Next: matplotlib: show xticks only for discrete times
From: Kushal Kumaran on 27 May 2010 10:17
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.

Also, you'll need as many %s in the values clause as the number of
columns you have. Basically, the query needs to be something like:

insert into tablename (col1, col2, col3) values (%s, %s, %s)

and the tuple argument to cursor.execute will have to have three values.

Also, lose the single quotes around the %s.

--
regards,
kushal


From: Kushal Kumaran on 27 May 2010 10:19
On Thu, 2010-05-27 at 09:34 -0400, Victor Subervi wrote:
> On Thu, May 27, 2010 at 8:34 AM, Victor Subervi
> <victorsubervi(a)gmail.com> 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??
>
> (Note to self: enterPeople3.py)
>
> ...and here's another one:
>
> print 'insert into categories (Store, Category, Parent)
> values("%s", "%s", Null)'% (store, cat)
> # cursor.execute('insert into categories (Store, Category,
> Parent) values("%s", "%s", Null)', (store, cat))
>

Lose the quotes around the %s. I'm pretty sure somebody mentioned this
in the previous MySQL problem thread.

--
regards,
kushal



From: Kushal Kumaran on 27 May 2010 11:17
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(a)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



From: Kushal Kumaran on 27 May 2010 12:11
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(a)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


From: MRAB on 27 May 2010 12:56
Kushal Kumaran wrote:
[snip]
> 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)))

That should be:

', '.join(['%s'] * len(values)))

> logging.debug('stmt: %s, values: %s' % (stmt, values))
>
> cursor.execute(stmt, values)
>
> Hope it helps.
>

 |  Next  |  Last
Pages: 1 2
Prev: Minor annoyances with properties
Next: matplotlib: show xticks only for discrete times