How to use the mysql database LIKE operator in python

The LIKE operator is used to search for a specified pattern in a column in the WHERE clause.

Grammar:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

pattern This is where the specified template is placed, and "%" is used here, also called the wildcard

% if it is placed in front of the condition , that is to check the data ending with...; for example: %李

% If it is placed after the condition, it is to check the data starting with...; for example: 李%

%If it exists before and after the condition, then the included data is checked; for example: %李%

Little knowledge points:

ERROR 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 "%z" at line 1

1064 error is LIKE query (syntax error) , there are no quotation marks around the wildcard, so an error is reported...

Correct display example: "%李%"

Example 1: The terminal runs sql and uses it in the WHERE clause LIKE

Query the information of people whose address starts with Hang

root@7c6316b19d80:/# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 140
Server version: 5.6.51 MySQL Community Server (GPL)
 
mysql> mysql> select * from test_user where address like "Hang%";
+----+--------+-------------+----------+
| id | name   | mobile      | address  |
+----+--------+-------------+----------+
|  3 | python | 18856565858 | Hangzhou |
|  4 | java   | 17756565858 | Hangzhou |
|  5 | php    | 15556565858 | Hangzhou |
|  6 | c#     | 17748484142 | Hangzhou |
+----+--------+-------------+----------+
4 rows in set (0.00 sec)
mysql>

Query the information of people whose address ends with u

mysql> select * from test_user where address like "%u";
+----+--------+-------------+----------+
| id | name   | mobile      | address  |
+----+--------+-------------+----------+
|  3 | python | 18856565858 | Hangzhou |
|  4 | java   | 17756565858 | Hangzhou |
|  5 | php    | 15556565858 | Hangzhou |
|  6 | c#     | 17748484142 | Hangzhou |
+----+--------+-------------+----------+
4 rows in set (0.00 sec)
mysql>

Example 2: Use python script to execute SQL statement containing LIKE

Query the personnel information whose address contains the z character

import pymysql
 
# 连接数据库
connection = pymysql.connect(host="localhost", user="root", password="123456",
                             database="testing", port=3306, charset="utf8",
                             cursorclass=pymysql.cursors.DictCursor)
 
try:
    with connection:
        with connection.cursor() as cursor:
            sql = """
                SELECT
                    *
                FROM
                    test_user
                WHERE
                    address LIKE "%z%";
            """
            cursor.execute(sql)
            result = cursor.fetchall()
            for i in result:
                print(i)
 
except pymysql.err.MySQLError as _error:
    raise _error

{"id": 3, "name": "python", "mobile": "18856565858", "address": "Hangzhou"}
{"id": 4, "name": "java", "mobile": "17756565858", "address": "Hangzhou"}
{"id": 5, "name": "php", "mobile": "15556565858", "address": "Hangzhou"}
{"id": 6, "name": "c#", "mobile": "17748484142", "address": "Hangzhou"}
 
Process finished with exit code 0

Query the personnel information whose address does not contain the z character

try:
    with connection:
        with connection.cursor() as cursor:
            sql = """
                SELECT
                    *
                FROM
                    test_user
                WHERE
                    address NOT LIKE "%z%";
            """
            cursor.execute(sql)
            result = cursor.fetchall()
            for i in result:
                print(i)
 
except pymysql.err.MySQLError as _error:
    raise _error

{"id": 1, "name": "张三三", "mobile": "17748484141", "address": "浙江杭州"}
{"id": 9, "name": "111", "mobile": "18847474549", "address": "浙江杭州"}
 
Process finished with exit code 0

Knowledge point expansion: python The mysql database like fuzzy query

% is a special symbol in python, such as %s and %d representing string placeholders and numeric placeholders respectively.

As everyone knows, mysql’s fuzzy query also needs to use %.

So, you can extract the string you want to check first and then pass it in as a parameter.

args = "%"+subtitle+"%"
sqlQueryTitle="select count(*) from tbl_peng_article where title like "%s""%args

The above is the detailed content of How to use the mysql database LIKE operator in python. For more information, please follow other related articles on the PHP Chinese website!