How to use the LIKE clause in Python

[Columbo]

In my app I have a database of dinosaurs. The user types in the name of the dinosaur that they are looking for and it displays information about that particular dinosaur. The problem is that the user might not know the correct spelling of the name of the dinosaur that they are looking for and if the spelling of the name entered into the search box is not correct, it does not display anything. I wrote this same program in another language and used the 'LIKE' clause and if there was a typo, or the name was entered without capitalizing the first letter of the name, it still found the correct record and displayed it. I want to do the same using Python but I can't figure out how Python uses the LIKE clause. If anyone can give me an example I would greatly appreciate it.

Here is the code that I am using:

def searchRec(recNo,e):
     dinoName=searchBox.get()
     conn=sqlite3.connect('dinobase.db')
     c=conn.cursor()
     c.execute('SELECT * FROM dino WHERE name LIKE :dinoName',{'dinoName': dinoName})
     rows=c.fetchall()

     for row in rows:
         recNo = row[0]
         namebox.insert(0, row[1])
         meaningbox.insert(0, row[2])
         pronouncebox.insert(0, row[3])
         periodbox.insert(0, row[4])
         groupbox.insert(0, row[5])
         sizebox.insert(0, row[6])
         livedbox.insert(0, row[7])
         dietbox.insert(0, row[8])
         fossilsbox.insert(0, row[9])
        factfilebox.insert(1.0, row[10])

     c.close()
     conn.close()

[Yoriz]

The following is a link I just found by using google that explains what SQL like does and how its used:
https://www.w3schools.com/sql/sql_like.asp

[ndc85430]

Yes, to be clear, Python doesn't interpret the SQL. It's the database you're using (SQLite in this case) that does.

[Larz60+]

As stated by ncd85430, it's the SQLite engine that interprets the query.
That being said:

like = % for wildcard, for to find 'beautifulsoup' and anything that ends with soup: [inline]where ... like '%soup';[/inline
to find anything that has soup in anywhere in word: where ... like '%soup%'
you can use as many wildcards as necessary, for example:

Output:
select * from county where CountyName like '%Hill%nty%';

CountyId|StateId|StateCd|CountyName|ClassFP
107|05|AR|Phillips County|H1
095|08|CO|Phillips County|H1
057|12|FL|Hillsborough County|H1
017|13|GA|Ben Hill County|H1
147|20|KS|Phillips County|H1
059|26|MI|Hillsdale County|H1
041|30|MT|Hill County|H1
071|30|MT|Phillips County|H1
001|32|NV|Churchill County|H1
011|33|NH|Hillsborough County|H1
071|41|OR|Yamhill County|H1
211|48|TX|Hemphill County|H1
217|48|TX|Hill County|H1

[Columbo]

Yes, I realize that it is SQLite that interprets the query but how you code it in Python seems to be different than what I am used to. For example, I initially coded a search query like this:

c.execute('SELECT * FROM dino WHERE name = dinoName’)

But that wouldn’t work and later I was told that I had to code it like this:

c.execute('SELECT * FROM dino WHERE name = :dinoName',{'dinoName': dinoName})

That worked as far as retrieving a record where the name equals the search string but for the LIKE statement it doesn’t work.

c.execute('SELECT * FROM dino WHERE name LIKE :dinoName',{'dinoName': dinoName})

I also tried this:

c.execute('SELECT * FROM dino WHERE name LIKE dinoName’)

And also this:

c.execute('SELECT * FROM dino WHERE name LIKE %’ + dinoName + %’)

And this:

c.execute('SELECT * FROM dino WHERE name LIKE %dinoName%’)

They don’t work either.
I simply want to be able to find the proper record even if the user has a typo in the dinosaur name entered in the search box. For example, Correct spelling = Trilobite, but user could enter Trilobyte or maybe Trylobite.

[rob101]

This is what I have, in one of my functions, and I know it works, so maybe you can adapt it to your needs.

removed. see the code below

this

c.execute('SELECT * FROM dino WHERE name = dinoName’)

should be this (I think)

removed. see the below posts

Have a read of:

SQLite - SELECT Query

---

To add...

Maybe it would help you if you had my db script, rather than just a snippet, so here you go and I hope it's of some use to you (or anyone else, for that matter).

import sqlite3 as db
import os.path

path = '/home/rob/Desktop/python/sqlite/' #clearly, this will not work for you, so change it

#===========Database Functions===========#
def process_file(file, path = path):
    filename = os.path.join(path, file)
    try:
        f = open(filename, 'r')
    except FileNotFoundError:
        return file, 0
    else:
        f.close()
        return file, 1

def db_file(file_name):
    print(f"Testing connection...")
    database, test = process_file(file_name)
    if test:
        conn = db.connect(database)
        cur = conn.cursor()
        if conn:
            print (f"Connection to {database} established.")
            conn.close()
            print (f"Connection to {database} closed.")
    else:
        print(f"\nAlert!\n{database} database file not found.")
    print("Connection test complete.\n")
    return file_name, test

def get_table(database):
    items = ''
    if database:
        conn = db.connect(database)
        cur = conn.cursor()
        if conn:
            table = "table"
            data = cur.execute(f'SELECT name FROM sqlite_master where type=:table', {'table':table})
            tables = data.fetchall() # retuns a list of tuples
            if tables:
                table = ((tables[0])[0]) # get the name of the first (or only) table
                data = cur.execute(f'SELECT `_rowid_`,* FROM {table} ORDER BY `_rowid_` ASC LIMIT 0, 50000')
                items = data.fetchall()
            else:
                print(f"The {database} does not have any tables.")
                table = ""
            # house keeping
            cur.close()
            conn.close()
        if items:
            return items
    else:
        print("No database file found.")
        return database

def file_test(name):
    db_list = ['test.db','menu.db'] #add the db file names to this list. 'test.db' can be removed, if not needed
    ext = name.find('.')
    if ext < 0:
        name += '.db'
    if name in db_list:
        file_name, test = db_file(name)
        if not test:
            print(f"\nAlert!\nA new database file will be created.\n")
    else:    
        print(f"\nUnknown database file requested.\nIf {name} is a new database,\nyou'll need to add it\nto the file_test db_list\n")
        name = ""
    return name, #return a tuple, not a string
    
#=======End of database functions========#

file_name = input("Data base file name: ")

# file_test MUST be run first!
database = file_test(file_name)
table_object = get_table(database[0]) # returns a list of tuples

if table_object:
    print(table_object)

[Yoriz]

@rob101

SQL operations usually need to use values from Python variables. However, beware of using Python’s string operations to assemble queries, as they are vulnerable to SQL injection attacks

Instead, use the DB-API’s parameter substitution. To insert a variable into a query string, use a placeholder in the string, and substitute the actual values into the query by providing them as a tuple of values to the second argument of the cursor’s execute() method. An SQL statement may use one of two kinds of placeholders: question marks (qmark style) or named placeholders (named style). For the qmark style, parameters must be a sequence. For the named style, it can be either a sequence or dict instance. The length of the sequence must match the number of placeholders, or a ProgrammingError is raised. If a dict is given, it must contain keys for all named parameters. Any extra items are ignored. Here’s an example of both styles:

con = sqlite3.connect(":memory:")
cur = con.execute("CREATE TABLE lang(name, first_appeared)")

# This is the qmark style:
cur.execute("INSERT INTO lang VALUES(?, ?)", ("C", 1972))

# The qmark style used with executemany():
lang_list = [
    ("Fortran", 1957),
    ("Python", 1991),
    ("Go", 2009),
]
cur.executemany("INSERT INTO lang VALUES(?, ?)", lang_list)

# And this is the named style:
cur.execute("SELECT * FROM lang WHERE first_appeared = :year", {"year": 1972})
print(cur.fetchall())

[rob101]

@Yoriz

Thank you. I'll rewrite my code, as per the recommendations.

[ibreeden]

I also tried this:

c.execute('SELECT * FROM dino WHERE name LIKE dinoName’)

And also this:

c.execute('SELECT * FROM dino WHERE name LIKE %’ + dinoName + %’) 

And this:

c.execute('SELECT * FROM dino WHERE name LIKE %dinoName%’)

They don’t work either.

No, no, don't make it more difficult than it is!

You can do:

c.execute("SELECT * FROM dino WHERE name LIKE 'diplo%cus'")

Or, with the (preferred) qmark style:

name = "diplo%cus"
c.execute("SELECT * FROM dino WHERE name LIKE ?", (name,))

(Mind the extra comma to ensure (name,) is interpreted as a tuple!)

Or with the named style:

name = "diplo%cus"
c.execute("SELECT * FROM dino WHERE name LIKE :dinoname", {"dinoname": name})

A more advanced method can be to use the soundex(string) function. This produces a string representing how "string" would be pronounced (in English). But I'm not certain soundex() is supported in sqlite3 and it would complicate things.

[Larz60+]

see: https://python-forum.io/thread-38402-pos...#pid162547