MySQL Python Insert strange?

Question

I dont see why it's not working. I have created several databases and tables and obviously no problem. But I am stuck with this table which is created from django data model. To clarify what I have done, created new database and table from mysql console and try to insert from python and working. But, this one is strange for me.

class Experiment(models.Model):
    user = models.CharField(max_length=25)
    filetype = models.CharField(max_length=10)
    createddate= models.DateField()
    uploaddate = models.DateField()
    time = models.CharField(max_length=20)
    size = models.CharField(max_length=20)
    located= models.CharField(max_length=50)

Here is view in mysql console

mysql> describe pmass_experiment;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| user        | varchar(25) | NO   |     | NULL    |                |
| filetype    | varchar(10) | NO   |     | NULL    |                |
| createddate | date        | NO   |     | NULL    |                |
| uploaddate  | date        | NO   |     | NULL    |                |
| time        | varchar(20) | NO   |     | NULL    |                |
| size        | varchar(20) | NO   |     | NULL    |                |
| located     | varchar(50) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

Above pmass_experiment table is created by django ORM after python manage.py syncdb

Now I am trying to insert data into pmass_experiment through python MySQLdb

import MySQLdb
import datetime,time
import sys

conn = MySQLdb.connect(
    host="localhost",
    user="root",
    passwd="root",
    db="experiment")

cursor = conn.cursor()
user='tchand'
ftype='mzml'
size='10MB'
located='c:\'
date= datetime.date.today()
time = str(datetime.datetime.now())[10:19]

#Insert into database
sql = """INSERT INTO pmass_experiment (user,filetype,createddate,uploaddate,time,size,located)
    VALUES (user, ftype, date, date, time, size, located)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   conn.commit()
except:
   # Rollback in case there is any error
   conn.rollback()
# disconnect from server
conn.close()

But, unfortunately nothing is inserting. I am guessing it's may be due to primary_key (id) in table which is not incrementing automatically.

mysql> select * from pmass_experiment;
Empty set (0.00 sec)

can you simply point out my mistake?

Thanks

You are catching all exceptions and so can't see what is wrong. Either raise the exception again, or at least print its traceback. — utapyngo
– utapyngo, Commented Oct 22, 2011 at 14:47
Unless there's some really really intelligent string interpolation going on there, you query will reach the DB literally like this insert into blah (user) values (user) which is not valid. — Mat
– Mat, Commented Oct 22, 2011 at 14:49
shouldn't that c:\ be double escaped? 'c:\\' this might be the problem? — Serdalis
– Serdalis, Commented Oct 22, 2011 at 14:50
@ Serdalis- Yeah that was supposed to be like that. While pasting here I changed it. — thchand
– thchand, Commented Oct 22, 2011 at 15:02

unutbu · Accepted Answer · 2011-10-22 15:44:56Z

1

sql = """INSERT INTO pmass_experiment (user,filetype,createddate,uploaddate,time,size,located)
    VALUES (user, ftype, date, date, time, size, located)"""

Parametrize your sql and pass in the values as the second argument to cursor.execute:

sql = """INSERT INTO pmass_experiment (user,filetype,createddate,uploaddate,time,size,located)
         VALUES (%s, %s, %s, %s, %s, %s, %s)"""
try:
   # Execute the SQL command
   cursor.execute(sql,(user, ftype, date, date, time, size, located))
   # Commit your changes in the database
   conn.commit()
except Exception as err:
   # logger.error(err) 
   # Rollback in case there is any error
   conn.rollback()

It is a good habit to always parametrize your sql since this will help prevent sql injection.

The original sql

INSERT INTO pmass_experiment (user,filetype,createddate,uploaddate,time,size,located)
    VALUES (user, ftype, date, date, time, size, located)

seems to be valid. An experiment in the mysql shell shows it inserts a row of NULL values:

mysql> insert into foo (first,last,value) values (first,last,value);
Query OK, 1 row affected (0.00 sec)
mysql> select * from foo order by id desc;
+-----+-------+------+-------+
| id  | first | last | value |
+-----+-------+------+-------+
| 802 | NULL  | NULL |  NULL | 
+-----+-------+------+-------+
1 row in set (0.00 sec)

So I'm not sure why your are not seeing any rows committed to the database table.

Nevertheless, the original sql is probably not doing what you intend.

edited Oct 22, 2011 at 15:44

answered Oct 22, 2011 at 14:48

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thchand Over a year ago

That's what I am talking about. Can you please explain how my original sql was causing cursor.execute to raise an expection?

thchand Over a year ago

I don't mean its not working. I just wanted further explantion about sql parametrize. But now it's clear. Thanks :)

unutbu Over a year ago

Oh, okay. I'll just link these here for future reference then: an example from the MySQLdb tutorial, and PEP 0249: DB-API 2.0

Collectives™ on Stack Overflow

MySQL Python Insert strange?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related