mysql/python连接器在处理空值时使用参数和字典崩溃

Question

insert_statement = ("INSERT INTO mydb.sets_ledger "
    "( exercise, weight, reps, rpe) "
    "VALUES ( %(Exercise)s, %(weight)s, %(reps)s, %(rpe)s")

#import the CSV file
workout_file = "squat_rpe.csv"
file_hook = open(workout_file, 'rb')
dictionary=csv.DictReader(file_hook)

print dictionary.dialect
print dictionary.fieldnames

for row in dictionary:
    print row['Day'], row['Exercise'], row['weight'], row['reps'], row['rpe']
    print "==> "
    cursor.execute(insert_statement, row)

当我从字典中提取一行数据时，如果其中一列（这里是'rpe'列）是空的，程序就会崩溃。

row =    {'rpe': '', 'weight': '60', 'reps': '3', 'Day': '04/26/14', 'Exercise': 'Competition Back Squat’}

最后给我报错了。

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

mysql数据库中，rpe、reps和weight这几列的类型都是decimal(10,5)，也就是小数。

当数据中有空值时，如何使用字典/参数的方法来生成SQL查询呢？

===================================

我做了一个单独的测试：

import csv
import mysql.connector

#engage the MySql Table
cnx= mysql.connector.connect(user='root', host='127.0.0.1', database='mydb')
cursor = cnx.cursor()


insert_statement_dic = ("INSERT INTO mydb.sets_ledger "
    "( exercise, rpe) "
    "VALUES ( %(exercise)s, %(rpe)s)")

insert_statement_tuple = ("INSERT INTO mydb.sets_ledger "
    "( exercise, rpe) "
    "VALUES ( %s, %s)")

tuple_param_10 = ('tuple insert test 10', 10)
tuple_param_pure_none = ('tuple insert test None', None)
tuple_param_quoted_none = ('tuple insert test quoted none', 'None')

cursor.execute(insert_statement_tuple, tuple_param_10);
cursor.execute(insert_statement_tuple, tuple_param_pure_none);
#cursor.execute(insert_statement_tuple, tuple_param_quoted_none);

dic_param_10 = {'exercise': 'dic_param_10', 'rpe': 19}
dic_param_None = {'exercise': 'dic_param_None', 'rpe': None}
dic_param_quoted = {'exercise': 'dic_param_None', 'rpe': 'None'}

cursor.execute(insert_statement_dic, dic_param_10);
cursor.execute(insert_statement_dic, dic_param_None);
#cursor.execute(insert_statement_dic, dic_param_quoted);


cnx.commit()
cursor.close()
cnx.close()

要在小数列中插入一个“无值”（NULL），我需要插入的是>None<，而不是>''<或者>'None'<。

这意味着，当我从以逗号分隔的值中构建参数字典时，我需要做一个清理函数，把任何空字符串（''）转换成内置的None。（除非我能找到某种方法，让CSV模块自动把空字符串（''）转换成None。）

mysql error handling data cleaning parameterized queries dictionary manipulation decimal data type null value handling csv module

mysql/python连接器在处理空值时使用参数和字典崩溃

1 个回答

撰写回答