在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屋！