求教，怎样在 python 里执行 sql 查询……

因为有个需求需要每天按时导表，我懒得天天手动导，希望能用 python 来实现导表+发邮件的工作
然而我就是个做表的，对 python 的了解也就限于安装与卸载这种程度…所以在编写的时候遇到了很多麻烦
我现在就卡在了 sql 查询有语法错误上，同样的查询，放在 navicat 和 excel 里都是能正常查询的
然而不知道为什么 python 就总是说"you have an error in your SQL syntax" ╮(╯ _╰)╭
希望各位 CS dalao 能帮我看一下是哪里写的不对，靴靴
import pymysql
conn = pymysql.connect(host='****', port=3307,user='****',passwd='****',db='autocar',charset='UTF8')
cur = conn.cursor()
sql = "SELECT\
contract.contract_number AS 合同编号,\
apply.into_time AS 进件时间,\
contract.actual_loan_time AS 放款确认时间,\
apply_detail.city_manager AS 城市经理,\
apply_detail.marketing_manager AS 市场经理,\
apply_detail.sales_name AS 销售姓名,\
apply.org_name AS 分公司,\
apply.product_name AS 产品名称,\
apply.loan_term AS 借款期限,\
contract.loan_amount AS 合同金额,\
n.statusdes AS 实时状态\
FROM apply\
LEFT JOIN contract ON contract.apply_id = apply.apply_id\
LEFT JOIN apply_detail ON apply_detail.apply_id = apply.apply_id\
LEFT JOIN (\
SELECT\
group_concat(DISTINCT c.status_code SEPARATOR '||') AS statussum,\
c.is_in_node,\
c.apply_id,\
group_concat(DISTINCT c.status_name SEPARATOR '||') AS statusdes,\
group_concat(DISTINCT c.operator_name SEPARATOR '||') AS operatornames\
FROM\
node_record c\
WHERE\
c.is_in_node = 1\
AND c.is_valid = 1\
GROUP BY c.apply_id\
) n ON apply.apply_id = n.apply_id\
WHERE\
1 = 1\
AND apply.is_ex_apply = '1'\
AND apply.borrowing_type = '1'\
AND apply.into_time >= '2016-11-1'\
AND apply_detail.sales_name NOT LIKE '%测试%'"
cur.execute(sql)
for i in cur:
print(i)
cur.close()
conn.close()

第 1 条附言 · 2016-11-25 13:00:57 +08:00

Traceback (most recent call last):
File "C:/Users/linch/PycharmProjects/untitled/connect_mysql.py", line 39, in <module>
cur.execute(sql)
File "C:\Users\linch\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql-0.7.9-py3.5.egg\pymysql\cursors.py", line 166, in execute
result = self._query(query)
File "C:\Users\linch\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql-0.7.9-py3.5.egg\pymysql\cursors.py", line 322, in _query
conn.query(q)
File "C:\Users\linch\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql-0.7.9-py3.5.egg\pymysql\connections.py", line 835, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "C:\Users\linch\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql-0.7.9-py3.5.egg\pymysql\connections.py", line 1019, in _read_query_result
result.read()
File "C:\Users\linch\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql-0.7.9-py3.5.egg\pymysql\connections.py", line 1302, in read
first_packet = self.connection._read_packet()
File "C:\Users\linch\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql-0.7.9-py3.5.egg\pymysql\connections.py", line 981, in _read_packet
packet.check_error()
File "C:\Users\linch\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql-0.7.9-py3.5.egg\pymysql\connections.py", line 393, in check_error
err.raise_mysql_exception(self._data)
File "C:\Users\linch\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql-0.7.9-py3.5.egg\pymysql\err.py", line 107, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "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 'applyLEFT JOIN contract ON contract.apply_id = apply.apply_idLEFT JOIN apply_det' at line 1")

Process finished with exit code 1

apply_id

apply

SQL

Python

9 条回复 • 2016-11-25 23:38:46 +08:00

stamaimer

2016-11-25 12:13:38 +08:00 via iPhone

能把报错信息发上来吗？

lhy360121

2016-11-25 12:17:20 +08:00

% 要变成 %%

KagamineLenKai2

2016-11-25 13:05:16 +08:00

@lhy360121 试过了，还是报错有语法错误……

lxy

2016-11-25 13:19:19 +08:00

>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 'applyLEFT JOIN contract ON contract.apply_id = apply.apply_idLEFT JOIN apply_det' at line 1

apply 和 LEFT JOIN 之间少了空格。这么长的字符串直接用三引号"""str"""，会保留所有回车和空格，不用加这么多斜杠。