Python sqlite like to collect matching records records from table

We are using our student table. Check how to create database and student table here.

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)

Output, these three records ends with the name John

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)

Output ( John is present in the string , not only at staring or ending of the name column )

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

To match John only at ending of the string we can change the query like this

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

To match John only at starting of the string we can change the query like this.

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

Rows where name column starting with b and ending with n

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

Output

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

Both conditions to be satisfied.

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

Output ( having john and ro )

7 My John Rob 78 Five male

Using NOT

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

Output ( by using NOT , we have removed 10^th records )

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 ? "

Output

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')

Output is here

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

View and Download like.ipynb file

Sqlite Connection insert Delete Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

SQLite like Query