user3713220 user3713220 - 4 months ago 30
MySQL Question

python mysql connector join and group by

I am new to python mysql connector and I am trying to get results for a query that will find the user_ids with p_id = 0 will go to product table find how many products are available in that city

import mysql.connector
con = mysql.connector.connect(user='user', password = 'pass', host = '')

cursor1 = con.cursor(buffered = True)

query = ("SELECT l.user_id, count(u.prod_id)"
"FROM db1.users as l INNER JOIN db2.product as u "
"ON l.u_city = u.p_city"
"WHERE l.p_id =0 GROUP BY l.user_id limit 10;" )

the query is getting executed mysql but from python mysql connector i am getting the following error

C:\Python27\python.exe C:/Python27/Lib/site-packages/mysql/connector/
Traceback (most recent call last):

File "C:/Python27/Lib/site-packages/mysql/connector/", line 12, in <module>

File "C:\Python27\lib\site-packages\mysql\connector\", line 491, in execute

File "C:\Python27\lib\site-packages\mysql\connector\", line 683, in cmd_query

File "C:\Python27\lib\site-packages\mysql\connector\", line 601, 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 MySQL server version for the right syntax to use near 'l.campus_id <2 GROUP BY l.user_id' at line 1

Process finished with exit code 1

query ='''SELECT l.user_id, count(u.prod_id) FROM db1.users as l INNER JOIN db2.product as u ON l.u_city = u.p_city WHERE l.p_id =0 GROUP BY l.user_id limit 10'''
  • always it is better to intialize ur query to a variable with ('''stmt''') and execute
  • there is no need of using ;