Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how to use mysql database LIKE operator in python". Xiaobian shows you the operation process through actual cases. The operation method is simple and fast and practical. I hope this article "how to use mysql database LIKE operator in python" can help you solve the problem.

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

Grammar:

SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern

pattern Here is where to put the specified template, and here you need to use " % ", also known as wildcard

% If it is placed before the condition, it is checked with... Data at the end; e.g.% Li

% If it is placed after the condition, it is checked with... Data at the beginning; e.g. Li %

% If it exists before and after the condition, it is to look up the included data; for example: % Li %

Small 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), wildcard place did not put quotes, so will report error...

Correct display e.g."% Lee %"

Example 1: Terminal running sql with LIKE in WHERE clause

Search for people whose addresses begin with Hang

root@7c6316b19d80:/# mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 140Server 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 information about people whose addresses end in 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: Executing SQL statements with LIKE using python scripts

Search for information about people whose addresses contain the z character

import pymysql #connect database 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

Search for information about people whose addresses do 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":"Zhang Sansan", "mobile": "17748484141", "address": "Zhejiang Hangzhou"}{"id": 9, "name": "111", "mobile": "188474549", "address": "Zhejiang Hangzhou"} Process finished with exit code 0

Knowledge point extension: mysql database in python like fuzzy query

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

As you know, mysql fuzzy query also needs to use %.

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

args = "%"+subtitle+"%"sqlQueryTitle="select count(*) from tbl_peng_article where title like "%s""%args About "mysql database in python how to use the LIKE operator" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the industry information channel. Xiaobian will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.