时间戳

首页 > 解决方案 > Python3：ValueError：索引 568 处不支持的格式字符“B”（0x42）

python - Python3：ValueError：索引 568 处不支持的格式字符“B”（0x42）

问题描述

因此，我试图将 URL 保存到数据库，但我不断收到此错误：

ValueError: unsupported format character 'B' (0x42) at index 568

当我尝试执行查询时。

网址：

https://images-na.ssl-images-amazon.com/images/I/81Sp%2BP3kX-L._AC_SL1500_.jpg

编码：

query = f"INSERT INTO Ruby.available_links(user_email, product_name, product_price, product_currency, product_old_price, product_store, user_link, spider_link, spider, created_date, product_img)" \
                    f"VALUES ('{email}', '{product_name}', '{product_price}', '{str(product_currency)}', '{product_price}', '{company[0]}', '{user_short_url['link']}', '{str(url)}', '{spider}', '{str(date.today())}', '{str(product_img)}')"
            conn.execute(query)

我知道这是导致问题的 url，因为当我从查询中删除它时，它成功执行了查询并且没有显示任何错误。

完整追溯：

File "new_link_handler.py", line 70, in runner_new_links
    conn.execute(query)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 976, in execute
    return self._execute_text(object_, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1149, in _execute_text
    parameters,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1250, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1478, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", line 168, in execute
    query = self.mogrify(query, args)
  File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", line 147, in mogrify
    query = query % self._escape_args(args, conn)
ValueError: unsupported format character 'B' (0x42) at index 568

标签： pythonmysqlsqlalchemy

解决方案

使用原始 SQL 和 SQLAlchemy 时，您可以使用 sqlalchemy.text 对象来确保在发送到数据库的 SQL 中正确引用参数值（文档）。防止由于未引用的特殊字符或数据类型导致的错误，如本问题所示，以及 SQL 注入攻击。

要使用文本对象，请在 SQL 中声明值，如下所示：

stmt = """INSERT INTO my_table (some_column) VALUES (:some_value)"""

或者

stmt = """SELECT some_column FROM my_table WHERE some_column = :some_value"""

然后创建一个文本对象

stmt = sqlalchemy.text(sql)

然后使用bindparams方法将参数值绑定到查询中

stmt = stmt.bindparams(some_value='whatever')

生成的对象现在可以由连接执行。

这是基于您的代码的示例：

import sqlalchemy as sa   

url = 'https://images-na.ssl-images-amazon.com/images/I/81Sp%2BP3kX-L._AC_SL1500_.jpg'
stmt = sa.text("""INSERT INTO links (url) VALUES (:url)""")
stmt = stmt.bindparams(url=url)

engine = sa.create_engine('mysql+pymysql://root:@localhost/test')

with engine.connect() as conn:
    conn.execute(stmt)

推荐阅读