Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Explain the use of sql LIKE operator using MySQL in Python?
LIKE is a operator in MySQL. The LIKE operator is used with the WHERE statement to search for a specific pattern in the table.
Suppose you want to search for values which start with “a” in the table, the LIKE statement is used in such scenarios.
There are two wildcard characters used with the LIKE clause.
% − This sign represents zero,one or multiple characters.
_ (underscore) − This represents one single character.
Example
LIKE ‘a%’ - Searches for all the values starting with a.
LIKE ‘%a’ -Searches for all the values which end with a.
LIKE ‘_a%’ - Searches for all the values where ‘a’ is at the second position. The _ represents a single character, the second character must be ‘a’ and after a there might be some characters present or not.
Syntax
SELECT * FROM table_name WHERE column_name LIKE pattern
Steps invloved in searching data based on some pattern in a table using MySQL in python
import MySQL connector
establish connection with the connector using connect()
create the cursor object using cursor() method
create a query using the appropriate mysql statements
execute the SQL query using execute() method
close the connection
Suppose we the following table named “Students” −
Students
+----------+-----------+ | name | marks | +----------+-----------+ | Rohit | 62 | | Rahul | 75 | | Inder | 99 | | Khushi | 49 | | Karan | 92 | +----------+-----------+
We want to find the names of the students which start with ‘K’.
Example
import mysql.connector db=mysql.connector.connect(host="your host", user="your username", password="your password",database="database_name") cursor=db.cursor() query="SELECT name FROM Students WHERE name LIKE 'K%' " cursor.execute(query) names=cursor.fetchall() for x in names: print(x) db.close()
The above code fetches all the names from the table which start with ‘K’.
Output
Karan Khushi
Similary, values can be serached based on various patterns using the above wildcards in any order.
4K+ Views
