1

How i create the table mingyan.

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

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

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

showkey
  • 482
  • 42
  • 140
  • 295

3 Answers3

2

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.

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
0

I always do the following

self.cursor.execute("insert into mingyan(tag, cont) values (%(tag)s, %(cont)s)", \
    {'tag': item['tag'], 'cont': item['cont']})
Umair Ayub
  • 19,358
  • 14
  • 72
  • 146
0

Like in docs: https://pymysql.readthedocs.io/en/latest/modules/cursors.html#pymysql.cursors.Cursor.execute

If args is a list or tuple, %s can be used as a placeholder in the query. If args is a dict, %(name)s can be used as a placeholder in the query.

matt91t
  • 103
  • 1
  • 8