Python MySQL连接器查询字符串语法

Question

我刚开始学习使用Python的MySQL连接器，现在我想写一个查询，找出那些用户ID（user_ids）中，产品ID（p_id）等于0的用户，然后去产品表里查找在那个城市有多少个产品可用。

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

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;" )
cursor1.execute(query)

这个查询在MySQL中可以正常执行，但在Python的MySQL连接器中，我遇到了以下错误。

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

File "C:/Python27/Lib/site-packages/mysql/connector/update_campus_user_profile_suset.py", line 12, in <module>
cursor1.execute(camp_zip)

File "C:\Python27\lib\site-packages\mysql\connector\cursor.py", line 491, in execute
self._handle_result(self._connection.cmd_query(stmt))

File "C:\Python27\lib\site-packages\mysql\connector\connection.py", line 683, in cmd_query
statement))

File "C:\Python27\lib\site-packages\mysql\connector\connection.py", 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

Answer 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;" )

有时候使用多行字符串会更自然一些：

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
        """)

Answer 2

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'''

这里有几个小建议：

• 最好把你的查询语句先放到一个变量里，比如叫做（'''stmt'''），然后再去执行它。
• 其实不需要使用;这个符号。