为什么不能在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;

据说，使用{}的字符串格式函数比使用占位符%更具Python风格.
我很努力地将一些字段写入表mingyan.

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

在我抓狂的时候效果很好，现在我用字符串格式功能替换了占位符方式.

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

scrapy收到错误信息

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

为什么无法在pymysql中用格式函数替换占位符?

有刮擦痕迹的物品.
项目含义不明确 简而言之:尽管使用％s"作为占位符，但在cursor.execute中实现的参数替换与Python字符串格式不同.这就是为什么您获得不同结果的原因.

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

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

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

驱动程序将使用此VALUES子句生成sql:

VALUES ('bar', NULL)

观察

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

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

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

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

MySQL的报价规则在对此

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']))

The scrapy got error info

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

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

The item in scrapy doucment.
item meaning in scrapy

解决方案

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.

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.

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))   

The driver will generate sql with this VALUES clause:

VALUES ('bar', NULL)

Observe that

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

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's quoting rules are discussed in detail in the answers to this question.

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