What are the methods for Python pymysql to pass parameters to SQL statements?

Directory

• How does Python-pymysql pass parameters to the SQL statement

• Method 1: Do not pass parameters

• Method 2: Use array to pass parameters

• Method 3: Use dictionary dict type to pass parameters

Python-pymysql How to pass parameters to SQL statements

Method 1: Do not pass parameters

## 方式一、不传递参数
 id = "01"
 name = "语文"
 sql = "select * from course where course_id = '%s'  and course_name = '%s' ;" %(id,name)
 ## 执行数据库查询或命令
 cursor.execute(sql)

Method 2: Use arrays to pass parameters

## 方式二、使用数组传递参数
  values = ['01','语文']
  sql = "select * from course where course_id = %s and course_name = %s "
  ## 执行数据库查询或命令
  cursor.execute(sql,values)

Note: here The placeholder is %s, whether it is a string, number or other type, it is this placeholder. %s cannot be quoted. What is the difference between

and the first way of writing?

The difference between the two is the parsing of variables:

The first way to write using the percent sign % is that the Python interpreter replaces %s accordingly. There is a problem with this approach and it cannot be parsed correctly in some cases, such as when special characters are included, which may lead to vulnerabilities in injection attacks.

Second, the variable is passed in as a parameter of execute, and the built-in method of MySQLdb interprets the variable into appropriate content.

Under normal circumstances, it is recommended to use the second method and pass variables as parameters to execute.

Method 3: Use dictionary dict type to pass parameters

## 方式三、使用字典dict类型传递参数
  sql = "select * from course where course_id = %(course_id)s and course_name =  %(course_name)s ;"
  ## 将参数封装到字典
  #values = {'course_id':'01','course_name':'语文'}
  values = {'course_name':'语文','course_id':'01'}
  ## 执行数据库查询或命令
  cursor.execute(sql,values)

In this way, the corresponding relationship between the parameters passed is relatively clear. Especially when there are many parameters, using a dictionary can ensure that the parameters are passed in the correct order.

The above is the detailed content of What are the methods for Python pymysql to pass parameters to SQL statements?. For more information, please follow other related articles on the PHP Chinese website!