pymysql多行语句格式化

Question

下面的内容是从数据库中的一列获取公司名称的列表，然后逐个使用 MATCH AGAINST 来与另一列进行匹配。

代码如下：

cur = conn.cursor()
cur.execute("SELECT DISTINCT company FROM opportunities;")
o_companies = cur.fetchall()

results = []
for n in o_companies:
    entry = n[0]
    cur.execute( """
            SELECT DISTINCT lead_id, leads.created_date, leads.company_name,
            opp_id, opportunities.created_date, opportunities.company,
            DATEDIFF(
                    STR_TO_DATE(opportunities.created_date,'%d/%m/%Y'),
                    STR_TO_DATE(leads.created_date,'%d/%m/%Y')
                    ) as difference,
            MATCH(company) AGAINST ({0}) as match_rating
            FROM leads, opportunities WHERE MATCH(company) AGAINST({0}) > 0
            ORDER BY difference, match_rating;
            """.format(entry)
            )
    matches = cur.fetchall()
    for match in matches:
        print(match)
        results.append(match)

这里有两个问题：

第一个问题是代码不工作，我收到的错误信息是：

pymysql.err.ProgrammingError: (1064, "你的SQL语法有错误；请检查与你的MySQL服务器版本相对应的手册，以获取在第7行附近使用的正确语法：'Health (Canada)) as match_rating\n\t\t FROM leads, opportunities WHERE MATCH'")

第二个问题是我所有的新行和制表符字符都显示出来了，我本以为这些会被自动忽略。

是SQL语句有问题，还是格式有问题呢？

编辑：

这是从第一个SQL语句返回的元组的一个例子：

('Cuttime.fm',) ('Renault Nissan',) ('Scout Marketing',) ('Beechcraft',) ('mobily',) ('Oliver Wyman',) ('MASTHEAD MARKETING',) ('FSA',) ('Only-apartments',) ('buchan',) ('Ralphs McIntosh',) ('TCMPi
- The Corporate Marketplace, Inc.',) ('University of Maryland, College Park',) ('Burson-Marsteller Guatemala',) ('Randstad Tech',) ('Gulf States Financial Services',) ('Socialyte',) ('The Social Shack',) ('Consumerchoices',) ('London Underground',)

这些值是通过 n[0] 来访问的。

Answer 1

你忘记在查询中加引号了，应该是 AGAINST ('{0}')

为了测试，请打印出这个查询，然后在数据库中测试这个语句，这样你就可以更容易地 调试 了

这个语句必须是

 """ SELECT DISTINCT lead_id, leads.created_date, leads.company_name,
            opp_id, opportunities.created_date, opportunities.company,
            DATEDIFF(
                    STR_TO_DATE(opportunities.created_date,'%d/%m/%Y'),
                    STR_TO_DATE(leads.created_date,'%d/%m/%Y')
                    ) as difference,
            MATCH(company) AGAINST ('{0}') as match_rating
            FROM leads, opportunities WHERE MATCH(company) AGAINST('{0}') > 0
            ORDER BY difference, match_rating
            """.format(a)