Python Forum
Python mysql query help please
Thread Rating:
  • 2 Vote(s) - 1 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python mysql query help please
#1
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
Reply
#2
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)
"So, brave knights, if you do doubt your courage or your strength, come no further, for death awaits you all with nasty, big, pointy teeth!" - Tim the Enchanter
Reply
#3
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
Reply
#4
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))
Reply
#5
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...
"So, brave knights, if you do doubt your courage or your strength, come no further, for death awaits you all with nasty, big, pointy teeth!" - Tim the Enchanter
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Mysql and mysql.connector error lostintime 2 612 Oct-03-2023, 10:25 PM
Last Post: lostintime
  list the files using query in python arjunaram 0 652 Mar-28-2023, 02:39 PM
Last Post: arjunaram
  python sql query single quote in a string mg24 1 996 Nov-18-2022, 08:01 PM
Last Post: deanhystad
  "SUMIF" type query in Python (help required) BlainEillimatta 0 803 Oct-06-2022, 09:08 AM
Last Post: BlainEillimatta
  MSSQL query not working in Python kat35601 0 874 Apr-12-2022, 06:44 PM
Last Post: kat35601
  Mysql error message: Lost connection to MySQL server during query tomtom 6 15,684 Feb-09-2022, 09:55 AM
Last Post: ibreeden
Question Debian 11 Bullseye | Python 3.9.x | pip install mysql-connector-python-rf problems BrandonKastning 4 6,574 Feb-05-2022, 08:25 PM
Last Post: BrandonKastning
  Problem Using SQL Placeholder In MySQL Query AdeS 11 5,934 Jul-31-2021, 12:19 AM
Last Post: Pedroski55
  Error using mariadb select query with form in python? shams 2 1,956 Jul-29-2021, 12:30 PM
Last Post: shams
  Python and MySql ogautier 8 3,250 May-20-2021, 11:10 PM
Last Post: Pedroski55

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020