为什么在pymysql中不能用格式函数替换占位符? [英] Why can't replace placeholder with format function in pymysql?

问题描述

我如何创建表mingyan.

CREATE TABLE `mingyan` (
  `tag` varchar(10) DEFAULT NULL,
  `cont` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

据说带{}的字符串格式化函数比占位符%更pythonic.
在我的scrapy中将一些字段写入到表中mingyan.

It's said that string format function with {} is more pythonic way than placeholder %.
In my scrapy to write some fields into a table mingyan.

self.cursor.execute("insert into mingyan(tag, cont) values (%s, %s)",(item['tag'],item['cont']))

它在我的scrapy中运行良好，现在我用字符串格式函数替换占位符方式.

It works fine in my scrapy,now i replace the placeholder way with string format function.

self.cursor.execute("insert into mingyan(tag, cont) values ({},{})".format(item['tag'],item['cont']))

scrapy 得到错误信息

The scrapy got error info

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; 

pymysql中为什么不能用格式函数替换占位符?

Why can't replace placeholder with format function in pymysql?

scrapy 文档中的项目.
scrapy 中的项目含义p>

The item in scrapy doucment.
item meaning in scrapy

推荐答案

简而言之:cursor.execute中实现的参数替换与Python字符串格式化不一样，尽管使用了"%s" 作为占位符；这就是为什么你会得到不同的结果.

In short: the parameter substitution implemented in cursor.execute is not the same as Python string formatting, despite the use of "%s" as a placeholder; that's why you get different results.

数据库期望以特定方式引用查询参数 - 用单引号或双引号，甚至是反引号括起来.Python 的 DBAPI 标准 提供了参数替换功能来自动化繁琐且容易出错的工作参数引用的过程.

Databases expect query parameters to be quoted - surrounded by single or double quotes, even backticks - in specific ways. Python's DBAPI standard provides parameter substitution functionality to automate the tedious and error-prone process of parameter quoting.

实现 DBAPI 标准的数据库驱动程序包会自动将正确的引用规则应用于查询参数.例如，给定这段代码

Database driver packages that implement the DBAPI standard automatically apply the correct quoting rules to query parameters. So for example, given this code

cursor.execute("""INSERT INTO mytable (foo, bar) VALUES (%s, %s);""", ('bar', None))   

驱动程序将生成带有此 VALUES 子句的 sql:

The driver will generate sql with this VALUES clause:

VALUES ('bar', NULL)

注意

• bar"用引号引起来
• None 已转换为 NULL，并且未加引号

• "bar" is in quotation marks
• None has been converted to NULL, and left unquoted

使用字符串格式而不是 DBAPI 参数替换意味着您需要自己了解和应用这些规则，例如:

Using string formatting rather than DBAPI parameter substitution means you need to know and apply these rules yourself, for example like this:

cursor.execute("""INSERT INTO mytable (foo) VALUES ('{}')""".format('bar'))

注意格式占位符周围的引号.

Note the quotation marks surrounding the format placeholder.

MySQL的引用规则在这个问题.

MySQL's quoting rules are discussed in detail in the answers to this question.

这篇关于为什么在pymysql中不能用格式函数替换占位符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！