[DB-SIG] Question about MySQL insert function

[Andy Todd]

Kiwi Golf wrote:
> Dear all,
> 
> Following is the example code I tried. I got a problem that the table is 
> created however the data can not insert into MySQL server. Any idea how to 
> fix this?
> 
> Thank you very much!
> Chloe
> --------------------------------------------
> import sys
> import MySQLdb
> 
> # connect to the MySQL server
> try:
>     conn = MySQLdb.connect (host = "localhost",
>                             user = "root",
>                             passwd = "1234",
>                             db = "animal")
> except MySQLdb.Error, e:
>     print "Error %d: %s" % (e.args[0], e.args[1])
>     sys.exit (1)
> 
> # create the animal table and populate it
> try:
>     cursor = conn.cursor ()
>     cursor.execute ("DROP TABLE IF EXISTS animal1")
>     cursor.execute ("""
>         CREATE TABLE animal1
>                (
>                    name CHAR(40),
>                    category CHAR(40)
>                )
>            """)
> 
>     cursor.execute ("""
>         INSERT INTO animal1 (name, category)
>         VALUES
>                    ('snake', 'reptile'),
>                    ('frog', 'amphibian'),
>                    ('tuna', 'fish'),
>                    ('racoon', 'mammal')
>            """)
>     print "%d rows were inserted" % cursor.rowcount
> 
>     cursor.close ()
> 
> except MySQLdb.Error, e:
>     print "Error %d: %s" % (e.args[0], e.args[1])
>     sys.exit (1)
> 
>     conn.close ()
>     sys.exit (0)
> 

Do you see any error messages when you run this code? What output do you 
get from your print statements?

As far as I can tell there isn't anything wrong with your SQL 
statements, they run OK in an interactive session against my database 
(Python 2.3, MySQLdb 1.2.0 and MySQL 5.0.18).

When you say that the data can't be inserted is that because you have 
some feedback indicating that or because you do a subsequent query and 
return no rows.

Just a couple of pointers;

- don't connect to your database as root, it's generally a bad idea
- don't wrap your entire transaction in a try .. except block, 
especially one that just catches every database error. If you want to 
deal with specific exceptions then trap them on or around the statement 
they are likely to be raised by.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/