New to python and am trying to firgure out how to do a MySQL query
Database table name is Customers
We have the customer fields named cfname, clname, caddress, ccity, cstate, and czipcode
Trying to do a search against the table Customers in which the first name and lastname are searched on or address,or city, or state or zip and return the results
The command I am trying:
mycursor.execute("""SELECT * FROM Customers WHERE cfname LIKE %s AND clname LIKE %s OR caddress LIKE %s OR ccity = %s OR cstate = %s or czipcode = %s""")%(dbcfname, dbclname, dbcaddress, dbccity, dbcstate, dbczipcode)
Thank you in advance
Tony
So what is the problem? No results? Database errors? Code exceptions? You need to show us the code and a sample of the database records or we can only guess.
One guess from me would be the mixing of AND and OR in your query is a problem. You may need to use parenthesis to combine the first and last names properly:
mycursor.execute("""SELECT * FROM Customers WHERE (cfname LIKE %s AND clname LIKE %s) OR (caddress LIKE %s OR ccity = %s OR cstate = %s or czipcode = %s)""")%(dbcfname, dbclname, dbcaddress, dbccity, dbcstate, dbczipcode)
Error:Exception in Tkinter callback
Traceback (most recent call last):
File "C:\Users\tduxt\AppData\Local\Programs\Python\Python38-32\lib\tkinter\__init__.py", line 1883, in __call__
return self.func(*args)
File "C:/Users/tduxt/PycharmProjects/start/addressbook-DB.py", line 48, in query
mycursor.execute("""SELECT * FROM Customers WHERE (cfname LIKE %s AND clname LIKE %s) OR (caddress LIKE %s OR ccity = %s OR cstate = %s or czipcode = %s)""")%(dbcfname, dbclname, dbcaddress, dbccity, dbcstate, dbczipcode)
File "C:\Users\tduxt\PycharmProjects\start\venv\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "C:\Users\tduxt\PycharmProjects\start\venv\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "C:\Users\tduxt\PycharmProjects\start\venv\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s AND clname LIKE %s) OR (caddress LIKE %s OR ccity = %s OR cstate = %s or czip' at line 1
Bigger code snapshot:
def query():
mydb = mysql.connector.connect(host="test.com",user="test",passwd="test",db="test")
mycursor = mydb.cursor()
dbcfname = cfname.get()
dbclname = clname.get()
dbcaddress = caddress.get()
dbccity = ccity.get()
dbcstate = cstate.get()
dbczipcode = czipcode.get()
mycursor.execute("""SELECT * FROM Customers WHERE (cfname LIKE %s AND clname LIKE %s) OR (caddress LIKE %s OR ccity = %s OR cstate = %s or czipcode = %s)""")%(dbcfname, dbclname, dbcaddress, dbccity, dbcstate, dbczipcode)
#mycursor.fetchone()
#mycursor.fetchmany(50)
#records = mycursor.fetchall()
#print(records)
cfname.delete(0, END)
clname.delete(0, END)
caddress.delete(0, END)
ccity.delete(0, END)
cstate.delete(0, END)
czipcode.delete(0, END)
mydb.commit()
mydb.close()
return
DATABASE format
Table Customers:
customerID
cfname
clname
caddress
ccity
cstate
czip
sample data:
2,joe,smo, 123 test ave, testcity, teststate, 37645
3, test2,test3,234 test rd, test2city,teststate2,74653
Hope this helps
Thank you
Tony
I was able to get the following code to work. The only problem is the cfname and clname is too specific. I was hoping to be able to just type in as little as a J for the first name and an S for the last name and match on Joe Smo. And also for the address part was hoping to match on the name of the street or part of it. How can I make it less sensitive?
mycursor.execute("""SELECT * FROM Customers WHERE (cfname LIKE %s AND clname LIKE %s) OR (caddress LIKE %s OR ccity = %s OR cstate = %s or czipcode = %s)""",(dbcfname, dbclname, dbcaddress, dbccity, dbcstate, dbczipcode))
The SQL "LIKE" operator can use wildcards to make it less specific. The percent symbol (%) matches any number of any character and the underscore (_) matches exactly one of any character.
https://database.guide/how-the-like-oper...-in-mysql/
Since you need to use the percent sign and that's also python's format specifier it will be much easier to read with python's new f-string feature.
https://realpython.com/python-f-strings/
mycursor.execute(f"""SELECT * FROM Customers WHERE (cfname LIKE %{dbcfname}% AND clname LIKE %{dbclname}%) OR (caddress LIKE %{dbcaddress}% OR ccity = %{dbccity}% OR cstate = %{dbcstate}% or czipcode = %{dbczipcode}%)"""Note that you can use full expressions and even functions in f-strings so you could just use "
cfname.get()" instead of "
dbcfname" and you wouldn't need to bother with all the intermediate variables you assigned before your query.
CAVEAT: I don't have a convenient database set up to test any of this so no guarantees. Try it out and look at the links for more info and let us know how it goes...