在pymysql多行语句中格式化 [英] Formatting in pymysql multiline statement

问题描述

以下内容从数据库的一列中获取公司名称的列表，然后反复使用 MATCH AGAINST 将它们与另一列进行匹配。

The following gets a list of company names from one columns in a database, then iteratively uses MATCH AGAINST to match them against another column.

代码为：

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)

有两个问题：

首先是它不起作用，我得到的错误消息是：

The first is it doesn't work, the error message I'm getting is:

pymysql.err.ProgrammingError ：（1064，您的SQL
语法错误；查看与您的MySQL服务器版本
相对应的手册，以在'Health（Canada）'附近使用正确的语法，因为
match_rating\n\t\t FROM线索，机会在哪里匹配'在
第7行）

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 'Health (Canada)) as match_rating\n\t\t FROM leads, opportunities WHERE MATCH' at line 7")

第二个是我所有新行和制表符都在显示，我以为

And the second is all of my new line and tab characters are showing, I would have thought these would just be ignored automatically.

SQL语句是否存在问题或格式是否正确？

Is there something wrong with the SQL statement or is it with the formatting?

编辑：

以下是第一个SQL语句返回的元组的示例：

Here is an example of the tuples returned from the first SQL statement:

('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] 访问它们的值。

推荐答案

您忘记在查询 AGAINST（'{0}'）

中提供报价以进行测试打印查询并在数据库中测试语句，以便您可以轻松地调试

to test please print the query and test the statementin DB so that you can debug easily

该语句必须为

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

这篇关于在pymysql多行语句中格式化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！