对于Python mysql.connector，在mysql JSON字段的cursor.execute()中使用".format()"与“％s"进行比较， [英] Use of '.format()' vs. '%s' in cursor.execute() for mysql JSON field, with Python mysql.connector,

问题描述

我的目标是使用mysql.connector库将JSON对象存储到json类型的MySQL数据库字段中.

My objective is to store a JSON object into a MySQL database field of type json, using the mysql.connector library.

import mysql.connector
import json

jsonData = json.dumps(origin_of_jsonData)

cnx = mysql.connector.connect(**config_defined_elsewhere)
cursor = cnx.cursor()
cursor.execute('CREATE DATABASE dataBase')
cnx.database = 'dataBase'
cursor = cnx.cursor()
cursor.execute('CREATE TABLE table (id_field INT NOT NULL, json_data_field JSON NOT NULL, PRIMARY KEY (id_field))')

现在，下面的代码工作正常，我的问题的重点是使用'％s':

Now, the code below WORKS just fine, the focus of my question is the use of '%s':

insert_statement = "INSERT INTO table (id_field, json_data_field) VALUES (%s, %s)"
values_to_insert = (1, jsonData)
cursor.execute(insert_statement, values_to_insert)

我的问题:将变量aValue组合到字符串中时，我非常严格地坚持使用'...{}'.format(aValue)(或f'...{aValue}')，因此避免使用%s(无论出于何种原因那，让我们在这里不讨论它们-而是我想尽可能地保留它，因此是我的问题.

My problem with that: I am very strictly adhering to the use of '...{}'.format(aValue) (or f'...{aValue}') when combining variable aValue(s) into a string, thus avoiding the use of %s (whatever my reasons for that, let's not debate them here - but it is how I would like to keep it wherever possible, hence my question).

在任何情况下，无论如何尝试，我都无法使用类似于上述结构的东西来创建将jsonData存储到mySql数据库中的东西，并使用'...{}'.format()(任何形状或形式)而不是%s.例如，我尝试了许多次迭代

In any case, I am simply unable, whichever way I try, to create something that stores the jsonData into the mySql dataBase using something that resembles the above structure and uses '...{}'.format() (in whatever shape or form) instead of %s. For example, I have (among many iterations) tried

insert_statement = "INSERT INTO table (id_field, json_data_field) VALUES ({}, {})".format(1, jsonData)
cursor.execute(insert_statement)

但是无论我如何转动和扭曲它，我都会不断遇到以下错误:

but no matter how I turn and twist it, I keep getting the following error:

ProgrammingError:1064(42000):您的SQL语法有错误；在第1行的"[some_content_from_jsonData})]"附近检查与您的MySQL服务器版本相对应的手册以使用正确的语法

ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[some_content_from_jsonData})]' at line 1

现在我的问题:

1)有没有办法避免在这里丢失我所使用的％s?

1) Is there a way to avoid the use of %s here that I am missing?

2)如果没有，为什么?是什么使得这不可能呢?是cursor.execute()函数，还是它是JSON对象，还是完全不同? {}.format()是否应该能够做到%s可以做的所有事情，甚至更多?

2) If not, why? What is it that makes this impossible? Is it the cursor.execute() function, or is it the fact that it is a JSON object, or is it something completely different? Shouldn't {}.format() be able to do everything that %s could do, and more?

推荐答案

首先:切勿将数据直接插入查询字符串！

在MySQL查询字符串中使用%s与在python字符串中使用%s不同. 在python中，您只需格式化字符串，然后'hello %s!' % 'world'变为'hello world!'.在SQL中，%s表示参数插入.这会将您的查询和数据分别发送到服务器.您也不受此语法的约束. python DB-API规范为此指定了更多样式: DB-API参数样式(PEP 249).与将数据直接插入查询字符串相比，这有几个优点:

Using %s in a MySQL query string is not the same as using it in a python string. In python, you just format the string and 'hello %s!' % 'world' becomes 'hello world!'. In SQL, the %s signals parameter insertion. This sends your query and data to the server separately. You are also not bound to this syntax. The python DB-API specification specifies more styles for this: DB-API parameter styles (PEP 249). This has several advantages over inserting your data directly into the query string:

说您有一个查询，可通过密码对用户进行身份验证.您可以通过以下查询来做到这一点(当然，您通常会对密码进行加盐和哈希处理，但这不是此问题的主题):

Say you have a query to authenticate users by password. You would do that with the following query (of course you would normally salt and hash the password, but that is not the topic of this question):

SELECT 1 FROM users WHERE username='foo' AND password='bar'

构造此查询的简单方法是:

The naive way to construct this query would be:

"SELECT 1 FROM users WHERE username='{}' AND password='{}'".format(username, password)

但是，如果有人输入' OR 1=1作为密码，将会发生什么.格式化后的查询将变成

However, what would happen if someone inputs ' OR 1=1 as password. The formatted query would then become

SELECT 1 FROM users WHERE username='foo' AND password='' OR 1=1

将始终返回1.使用参数插入时:

which will allways return 1. When using parameter insertion:

execute('SELECT 1 FROM users WHERE username=%s AND password=%s', username, password)

这将永远不会发生，因为查询将由服务器单独解释.

this will never happen, as the query will be interpreted by the server separately.

如果使用不同的数据多次运行同一查询，则使用格式化查询和参数插入之间的性能差异可能会很大.使用参数插入，服务器只需要编译一次查询(每次都相同)，然后使用不同的数据执行查询，但是如果使用字符串格式，则必须一次又一次地编译查询.

If you run the same query many times with different data, the performance difference between using a formatted query and parameter insertion can be significant. With parameter insertion, the server only has to compile the query once (as it is the same every time) and execute it with different data, but with string formatting, it will have to compile it over and over again.

这篇关于对于Python mysql.connector，在mysql JSON字段的cursor.execute()中使用".format()"与“％s"进行比较，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！