How to use the LIKE clause in Python - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: How to use the LIKE clause in Python (/thread-38402.html) |
How to use the LIKE clause in Python - Columbo - Oct-08-2022 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() RE: How to use the LIKE clause in Python - Yoriz - Oct-08-2022 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 RE: How to use the LIKE clause in Python - ndc85430 - Oct-08-2022 Yes, to be clear, Python doesn't interpret the SQL. It's the database you're using (SQLite in this case) that does. RE: How to use the LIKE clause in Python - Larz60+ - Oct-08-2022 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:
RE: How to use the LIKE clause in Python - Columbo - Oct-09-2022 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. RE: How to use the LIKE clause in Python - rob101 - Oct-09-2022 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 belowthis c.execute('SELECT * FROM dino WHERE name = dinoName’)should be this (I think) removed. see the below postsHave 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) RE: How to use the LIKE clause in Python - Yoriz - Oct-09-2022 @rob101 https://docs.python.org/3/library/sqlite3.html#how-to-use-placeholders-to-bind-values-in-sql-queries Wrote: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 RE: How to use the LIKE clause in Python - rob101 - Oct-09-2022 @Yoriz Thank you. I'll rewrite my code, as per the recommendations. RE: How to use the LIKE clause in Python - ibreeden - Oct-09-2022 (Oct-09-2022, 03:18 AM)Columbo Wrote: I also tried this: 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. RE: How to use the LIKE clause in Python - Larz60+ - Oct-09-2022 see: https://python-forum.io/thread-38402-post-162547.html#pid162547 |