MySQL Python 插入奇怪？

Question

我不明白为什么它不起作用。我创建了几个数据库和表，显然没有问题。但我坚持使用这个从 django 数据模型创建的表。为了澄清我所做的事情，从 mysql 控制台创建了新的数据库和表，并尝试从 python 插入并工作。但是，这对我来说很奇怪。

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)

这是mysql控制台中的视图

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)

上面的pmass_experiment表是由django ORM在python manage.pysyncdb之后创建的

现在我尝试通过python MySQLdb将数据插入到pmass_experiment< /strong>

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()

但是，不幸的是没有插入任何内容。我猜测这可能是由于表中的primary_key（id）没有自动递增。

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

你能简单地指出我的错误吗？

谢谢

Answer 1

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

参数化您的 sql 并将值作为第二个参数传递给 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()

始终参数化您的 sql 是一个好习惯，因为这将有助于防止 sql注入。

原来的sql

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

看起来是有效的。 mysql shell 中的实验显示它插入了一行 NULL 值：

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)

所以我不确定为什么您没有看到任何提交到数据库表的行。

然而，原始的 sql 可能没有达到您的预期。