MySQL Query Problem

[Victor Subervi]

Here's some more data:

        print 'insert into Passengers values (Null, %s, %s, %s, %s, %s, %s,
%s, %s, "no", "n/a")' % (curr_flight, curr_customer, name, curr_sex,
curr_weight, price, curr_rt, curr_confirmation)
        cursor.execute('insert into Passengers values (Null, %s, %s, %s, %s,
%s, %s, %s, %s, "no", "n/a")', (curr_flight, curr_customer, name, curr_sex,
curr_weight, price, curr_rt, curr_confirmation))
        database.commit()
        cursor.execute('select last_insert_id() from Passengers')
        last_insert_id = cursor.fetchone()[0]
        print 'update Passengers set name=%s, weight=%s where id=%s' %
(name, curr_weight, last_insert_id)
        cursor.execute('update Passengers set name=%s, weight=%s where
id=%s', (name, curr_weight, last_insert_id))
        database.commit()

This prints out:

insert into Passengers values (Null, 1, 1, Dr. Mengela, Male, 155, 100, 100,
654, "no", "n/a")
update Passengers set name=Dr. Mengela, weight=155 where id=15

Here's what's in the database:

mysql> select * from Passengers;
+----+------------+-------------+------+------+--------+-------+------------+--------------+----------+---------------+
| id | flights_id | customer_id | name | sex  | weight | price | round_trip
| confirmation | late_fee | late_fee_paid |
+----+------------+-------------+------+------+--------+-------+------------+--------------+----------+---------------+
|  1 |          1 |           1 | ''   | NULL |   NULL |  0.00 |          0
| 12345        | NULL     | NULL          |
| 15 |          1 |           1 | ''   | Male |   NULL |  0.00 |        100
| 654          | no       | n/a           |
+----+------------+-------------+------+------+--------+-------+------------+--------------+----------+---------------+
2 rows in set (0.00 sec)

mysql> describe Passengers;
+---------------+-------------------------------+------+-----+---------+----------------+
| Field         | Type                          | Null | Key | Default |
Extra          |
+---------------+-------------------------------+------+-----+---------+----------------+
| id            | int(11)                       | NO   | PRI | NULL    |
auto_increment |
| flights_id    | int(11)                       | NO   | MUL | NULL
|                |
| customer_id   | int(11)                       | NO   | MUL | NULL
|                |
| name          | varchar(40)                   | YES  |     | NULL
|                |
| sex           | enum('Male','Female')         | YES  |     | NULL
|                |
| weight        | int(11)                       | YES  |     | NULL
|                |
| price         | float(6,2)                    | NO   |     | NULL
|                |
| round_trip    | tinyint(1)                    | NO   |     | 1
|                |
| confirmation  | varchar(20)                   | YES  |     | NULL
|                |
| late_fee      | enum('no','yes')              | YES  |     | no
|                |
| late_fee_paid | enum('n/a','paid','not paid') | YES  |     | n/a
|                |
+---------------+-------------------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

and when I run the update:

mysql> select * from Passengers;
+----+------------+-------------+-------------+------+--------+-------+------------+--------------+----------+---------------+
| id | flights_id | customer_id | name        | sex  | weight | price |
round_trip | confirmation | late_fee | late_fee_paid |
+----+------------+-------------+-------------+------+--------+-------+------------+--------------+----------+---------------+
|  1 |          1 |           1 | ''          | NULL |   NULL |  0.00
|          0 | 12345        | NULL     | NULL          |
| 15 |          1 |           1 | Dr. Mengela | Male |    155 |  0.00
|        100 | 654          | no       | n/a           |
+----+------------+-------------+-------------+------+--------+-------+------------+--------------+----------+---------------+
2 rows in set (0.00 sec)

Please explain why it does that!
TIA,
beno

On Fri, Sep 17, 2010 at 8:51 AM, Victor Subervi <victorsubervi at gmail.com>wrote:

> Hi;
> I have this code:
>
>         cursor.execute('insert into Passengers values (Null, %s, %s, %s,
> %s, %s, %s, %s, %s, "no", "n/a")', (curr_flight, curr_customer, name,
> curr_sex, curr_weight, price, curr_rt, curr_confirmation))
>
> Now, when I print it out, add quotes where necessary and enter it in at a
> mysql prompt, all goes well. But when I do it this way, it enters null
> values for curr_customer and curr_weight! Same darn thing printed out works
> at the prompt. What gives?
> TIA,
> beno
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100917/bd851dab/attachment.html>