CODEWITHSUNDEEP
pythonsqldatabasestringpymysql
Isaac
Isaac

Reputation: 1401

Formatting in pymysql multiline statement

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.

The code is:

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)

There are two problems:

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

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.

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

Edit:

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

They values are being accessed using n[0].

Upvotes: 0

Views: 2971

Answers (1)

sundar nataraj
sundar nataraj

Reputation: 8692

you forget to provide quotes in the query AGAINST ('{0}')

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

the statement must be

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

Upvotes: 3

Related Questions