在Python中使用mysql.connector处理格式参数失败 [英] Failed processing format-parameters with mysql.connector in Python
问题描述
我无法弄清楚此插入语句在做什么.我收到的错误是:
I can't figure out what I'm doing wrong with this insert statement. The error I'm getting is:
"Failed processing format-parameters; %s" % err)
mysql.connector.errors.ProgrammingError: Failed processing format-parameters;
'MySQLConverter' object has no attribute '_navigablestring_to_mysql'`
具体的代码行是:
update = '''INSERT INTO myDB.newtable (ID,Record,Latitude,Longitude,code) VALUES (%s,%s,%s,%s,%s)'''
cursor2.execute(update,(ID,Record,Latitude,Longitude,code))
cnx2.commit()
我也尝试过这种格式:
update = ("INSERT INTO myDB.newtable (ID,Record,Latitude,Longitude,code) VALUES (%s, %s, %s, %s, %s)")%(ID,Record,Latitude,Longitude,code)
cursor2.execute(update)
并得到此错误:
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column '45676kb' in 'field list'
.
45676kb
只是整个值的一部分.完整的字符串为45676kb-98734-98734-123nn
.
45676kb
is only a portion of the entire value. The complete string is 45676kb-98734-98734-123nn
.
我认为第二次尝试的语法更正确,因为我至少遇到了sql错误,但是我不知道如何使用mysql.connector正确格式化我的插入语句.
I think the syntax of the second attempt is more correct, because I'm at least getting an sql error but I can't figure out how to properly format my insert statement with mysql.connector.
推荐答案
第一个选项是将查询参数放入查询中的正确方法-这称为参数化查询.在这种情况下,您要让数据库驱动程序转义查询参数,将其安全地插入查询中并处理Python到MySQL的类型转换.
The first option is the correct way to put query parameters into the query - it is called a parameterized query. In this case, you are letting the database driver to escape the query parameters, safely insert them into the query and handle the Python-to-MySQL type conversions.
您收到的错误意味着它无法将ID
,Record
,Latitude
,Longitude
或code
参数值之一转换为有效的MySQL数据库类型.具体来说,请查看您发布的变量类型:
The error you are getting means that it could not convert one of the ID
, Record
, Latitude
, Longitude
or code
parameter values to a valid MySQL database type. To be specific, see the variable types you have posted:
ID <type 'unicode'>
Record <type 'unicode'>
Latitude <class 'bs4.element.NavigableString'>
Longitude <class 'bs4.element.NavigableString'>
code <type 'unicode'>
问题出在Latitude
和Longitude
上-它们是BeautifulSoup
的 NavigableString
类实例-MySQL转换器在理解如何将NavigableString
对象转换为有效的MySQL类型方面遇到困难.事先将它们明确转换为字符串:
The problem is with Latitude
and Longitude
- they are BeautifulSoup
's NavigableString
class instances - the MySQL converter having difficulties in understanding how to convert a NavigableString
object into a valid MySQL type. Convert them to strings explicitly beforehand:
update = """
INSERT INTO
myDB.newtable
(ID,Record,Latitude,Longitude,code)
VALUES
(%s,%s,%s,%s,%s)
"""
cursor2.execute(update, (ID, Record, str(Latitude), str(Longitude), code))
这篇关于在Python中使用mysql.connector处理格式参数失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!