Python sqlite where like

SQLite like Query

Read more on like query here.
There are two wildcards used with LIKE query.

% : Matches zero or more chars
_ : Matches one char

USING like query

We will collect records by using like query by using execute method. We will use LIKE with SELECT command.

We can display error message if there is any error by using try except .

Here we are collecting records where name column Ending with John.

q="SELECT * FROM student WHERE name like '%John'" try: my_cursor=my_conn.execute(q) for row in my_cursor: print(row[0],row[1],row[2],row[3],row[4]) except sqlite3.Error as my_error: print("error: ",my_error)

6 Alex John Four 55 male 10 Big John Four 55 female 21 Babby John Four 69 female

Using Parameters

Always use parameterized query when the data is coming from unknown sources. Use ? as placeholder and a provide a tuple to pass the value to query or execute() method. This is required to prevent injection attack.

We have used placeholders ( ? ) in our query and note that my_data is a tuple used for passing value to execute() method for our query.

Here we are matching the string John which can be any where in name column.

my_data=('%john%',) q="SELECT * FROM student WHERE name like ?" try: my_cursor=my_conn.execute(q,my_data) for row in my_cursor: print(row[0],row[1],row[3],row[2],row[4]) except sqlite3.Error as my_error: print("error: ",my_error)

1 John Deo 75 Four female 5 John Mike 60 Four female 6 Alex John 55 Four male 7 My John Rob 78 Five male 10 Big John 55 Four female 21 Babby John 69 Four female

my_data=('%john',) q="SELECT * FROM student WHERE name like ?"

my_data=('john%',) q="SELECT * FROM student WHERE name like ?"

my_data=('b%n',) # starting with b and ending with n q="SELECT * FROM student WHERE name like ?"

10 Big John 55 Four female 21 Babby John 69 Four female

Here is a summary of string matching using % along with LIKE query

‘%John’	Matches string ending with John
‘John%’	Matches string starting with John
‘%John%’	Matches string anywhere with John
‘j%n’	Matches string starting with j and ending with n

Using more than one parameters

We used OR Boolean string matching here so any one of the two conditions can be checked and records will be returned if any one condition is satisfied.

my_data=('%john%','%ow%') q="SELECT * FROM student WHERE name like ? or name like ?"

Using AND

my_data=('%john%','%ro%') q="SELECT * FROM student WHERE name like ? AND name like ?"

7 My John Rob 78 Five male

Using NOT

my_data=('%john%','%Big%') q="SELECT * FROM student WHERE name like ? AND name NOT like ?"

1 John Deo 75 Four female 5 John Mike 60 Four female 6 Alex John 55 Four male 7 My John Rob 78 Five male 21 Babby John 69 Four female

Matching single char by using _

We can match any char at one position by using underscore _ . Multiple underscores can be used to match more chars.

Under the sex column we have data as male or female. We can get all records where sex column first two chars are fe. This query will return all records having data as female. ( All male data is excluded as they don’t have fe as first two chars )

my_data=('fe____',) q="SELECT * FROM student WHERE sex like ? "

We can get records having marks in nineties. In mark column we can get records where first digit is 9, here we are excluding mark equal to or above 100 and marks less than 90.

my_data=('9_',) q="SELECT * FROM student WHERE mark like ? "

12 Recky 94 Six female 32 Binn Rott 90 Seven female 33 Kenn Rein 96 Six female

Some time we have to enter last 4 digits of our Credit Card number or account number. The query can be prepared using this part of the string.

Example : we want to collect all the account numbers ending with 044 in a five digit account number field. Here is the query for this.

SELECT * FROM account_master WHERE acc_no LIKE '__044'

PRAGMA case_sensitive_like

By default the value for PRAGMA case_sensitive_like is OFF , so our LIKE operator ignore case for ASCII characters. We can change this value and make it case sensitive.

my_data=('b%n',) # starting with b and ending with n q="SELECT * FROM student WHERE name like ?" try: my_conn.execute('PRAGMA case_sensitive_like = ON') my_cursor=my_conn.execute(q,my_data) for row in my_cursor: print(row[0],row[1],row[3],row[2],row[4]) except sqlite3.Error as my_error: print("error: ",my_error)

We will not get any matching rows as now LIKE is case sensitive ( there are two records starting with B ) . We can change the code to make it case insensitive.

my_conn.execute('PRAGMA case_sensitive_like = OFF')

10 Big John 55 Four female 21 Babby John 69 Four female

Источник

SQL LIKE Operator

LIKE opearator in SQL allows us to make queries based on first character, last character, sub-patterns or even position of the character.

You can also use NOT LIKE to exclude values with certain characters from your query results.

Overall LIKE operator makes relational databases and SQL querying much more useful, functional and workable.

In this tutorial we will explore LIKE operator with multiple SQL and Python examples.

You can execute SQL queries below from your IDE with Python as well as from DB Browser for SQLite’s Execute SQL section.

You can use LIKE operator to create queries that are not exact matches.

For example you can use ‘t%’ to query rows that start with t and you can use ‘%t’ to query items that end with t.

Internet Usage for countries that start with ‘be‘:

1- Querying text based on starting characters

Internet Usage for countries that start with ‘be‘:

a = cur.execute('''SELECT * FROM Intuse WHERE country like "be%" order by percentage desc limit 5''') 

('Bermuda', 60349.0, 61349, 197, '98.37%', 3)
('Belgium', 10021242.0, 11429336, 79, '87.68%', 30)
('Belarus', 7048231.0, 9468338, 92, '74.44%', 71)
('Belize', 176400.0, 374681, 174, '47.08%', 135)
('Benin', 1578008.0, 11175692, 80, '14.12%', 192)

Holy Python is reader-supported. When you buy through links on our site, we may earn an affiliate commission.

2- Querying text based on ending characters

conn = sqlite3.connect('Desktop/GA3.sqlite') cur = conn.cursor() a = cur.execute('''SELECT * FROM Intuse WHERE country like "%y" order by percentage desc limit 10''') 

('Norway', 5120225.0, 5305383, 117, '96.51%', 12)
('Germany', 77794405.0, 82114224, 18, '86%', 38)
('Hungary', 7461297.0, 9721559, 90, '76.75%', 62)
('Uruguay', 2360269.0, 3456750, 135, '68.28%', 84)
('Palestinian Authority', 3208312.0, 4920724, 120, '65.20%', 89)
('Turkey', 62075879.0, 80745020, 19, '64.68%', 92)
('Italy', 36387619.0, 59359900, 23, '61.30%', 101)
('Paraguay', 4160340.0, 6811297, 106, '61.08%', 102)
('Jersey', 38958.0, 165314, 183, '41.03%', 141)

It was surprising for me to see Germany with sub-90% internet usage. Also, some other industrialized European countries such as Hungary, Turkey and Italy seem to have somewhat lower than expected internet usage ratios. Maybe mountainous terrain is another restrain for the internet penetration and infrastructure investment decisions.

By the way isn’t it weird that there is no country whose name ends with “p”, “b” or “v” even though there is a country whose name ends with “q”.

Источник

Python-сообщество

• Начало
• » Python для новичков
• » Запрос sqlite для поиска по похожим строкам

#1 Ноя. 28, 2019 20:32:36

Запрос sqlite для поиска по похожим строкам

Приветствую.
Столкнулся с проблемой.
Есть база данных sqlite по которой нужно сделать выборку по похожим записям. Пробую делать запрос с прописанным в коде значением text и все работает.

SELECT * FROM table WHERE column LIKE '%text%') 

Требуется что бы была подстановка значения text введенного в программе пользователем.

Для поиска точного значения использую запрос такого вида и все работает нормально.

SELECT * FROM table WHERE column = '%s' " % text) 

import vk_api, random import sqlite3, datetime conn = sqlite3.connect("db.db3") c = conn.cursor() vk_session = vk_api.VkApi(token='TOKEN_HERE') from vk_api.longpoll import VkLongPoll, VkEventType longpoll = VkLongPoll(vk_session) vk = vk_session.get_api() def check_url(text): ### Работает при точном запросе c.execute("SELECT * FROM table WHERE column = '%s' " % text) result = c.fetchone() print("Ресулт ВК:") print(result) print("Текст ВК:") print(text) if result is None: return True return False def check_c(text): c.execute("SELECT * FROM table WHERE column LIKE %'%s'% " % text) ## Как подставить значение text c условием поиска по частичному совпадению ('%text%') ? result = c.fetchone() print("Ресулт like:") print(result) print("Текст Like:") print(text) if result is None: return True return False if not check_c(event.text.lower()): print("Есть") vk.messages.send( user_id=event.user_id, message="Есть" , keyboard=open("keyboard.json", "r", encoding="UTF-8").read(), random_id=random_id() ) 

Отредактировано Gens007 (Ноя. 28, 2019 21:21:23)

Источник