mysql_python+DBUtils 操作数据库常见错误

1、

错误：

Mon, 11 Apr 2016 07:11:00 [18257]db_mysql.py[line:60] [data_operate] [MainThread 140295489881920] ERROR sqlINSERT INTO ansible_playbook_result(uuid) VALUES(%s); error. Traceback (mostrecent call last):

 File "/usr/local/bin/ansible_engine/src/db/db_mysql.py", line55, in data_operate

   self.cursor.execute(sql, params)

 File "/usr/lib/python2.7/site-packages/DBUtils/SteadyDB.py",line 552, in tough_method

   result = method(*args, **kwargs) # try to execute

 File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py",line 187, in execute

   query = query % tuple([db.literal(item) for item in args])

TypeError: not all arguments convertedduring string formatting

源码：

sql = "INSERT INTO ansible_playbook_result(uuid,result) VALUES(%s);" % constant.CREATED_STATUS
params = (uuid)

#数据库操作函数

self.cursor.execute(sql, params)
#提交到数据库执行
self.conn.commit()

原因及解决方法：

params = (uuid) 改为 params = (uuid,)

这样 params才为元组，值为 (uuid,)。如果不加逗号，params的值为uuid的值。

2、

错误：

File "/usr/lib/python2.7/site-packages/DBUtils/SteadyDB.py", line 631, in tough_method
    raise error # reraise the original error again
OperationalError: (1054, "Unknown column 'mysql' in 'where clause'")

源码：

sql = """SELECT uuid,config_version FROM %s WHERE name=%s;""" % (constant.CONF_SOFTWARES_TAB,
                                                                 software_name)

原因及解决方法：

name后面的%s少了单引号。

或

改为 

sql = """SELECT uuid,config_version FROM %s WHERE name=%%s;""" % (constant.CONF_SOFTWARES_TAB,)
params = (software_name,)

3、

错误：

File"/usr/local/bin/ansible_engine/src/db/db_mysql.py", line 55, indata_operate

   self.cursor.execute(sql, params)

 File "/usr/lib/python2.7/site-packages/DBUtils/SteadyDB.py",line 631, in tough_method

   raise error # reraise the original error again

OperationalError: (1241, 'Operand shouldcontain 1 column(s)')

源码：

sql = """UPDATE %s SET `children_tasks`=%%s WHERE (`task_id`=%%s);""" % constant.TASK_GATHER_TAB

params = (uuid_list,task_uuid)
uuid_list是一个列表。

原因及解决方法：
写入数据库的数据必须为字符串

params = (json.dumps(uuid_list),task_uuid)