Aaron Aaron - 2 months ago 13
MySQL Question

A query that works in MySQL terminal fails when executed via PyMySQL

I've provided three sample SQL queries below. Each of these works fine, returning the expected table output when executed directly from terminal in the

MySQL [db] >
environment.

Each other these queries is saved in a python doc called
queries.py
. The second two queries work fine when passed to the db via
pymysql
, but the first one returns an empty array.

I've checked out this post, this post, and this post and none of them seem to be addressing the issue.

Here's the sample code that I'm using to test in Python (version
3.5
):

import pymysql
import params
import queries

conn = pymysql.connect(
host = params.HOST,
user = params.USER,
password = params.PWD,
db = 'db',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor,
autocommit = True)

test_queries = [queries.VETTED, queries.CREATED, queries.CLOSED_OPPS]
with conn.cursor() as cursor:
for query in test_queries:
cursor.execute(query)
print(cursor.fetchall())

() #..blank output -- doesn't make sense because corresponding query works in MySQL env
[...] #..expected output from query 2
[...] #..expected output from query 3


Here's what
queries.py
looks like. Each of those queries returns expected output when executed in MySQL, but the first one,
VETTED
, returns a blank array when passed to the DB via
pymysql
:

VETTED = """
SELECT
date_format(oa.logged_at, '%Y-%m-%d') as `action_date`,
count(1) `count`
FROM
crm_audit_field oaf,
crm_audit oa,
crm_sales_lead lead
WHERE
oa.id = oaf.audit_id AND
oaf.field = 'status' AND
(
oaf.new_text = 'Qualified' OR
oaf.new_text = 'Disqualified' OR
oaf.new_text = 'Canceled'
) AND
oa.object_class = 'CRM\\Bundle\\SalesBundle\\Entity\\Lead' AND
lead.id = oa.object_id AND
(lead.status_id = 'qualified' OR lead.status_id = 'canceled')
GROUP BY
`action_date`;"""

CREATED = """
SELECT
DATE_FORMAT(lead.createdat, '%Y-%m-%d') as `creation_date`,
count(1)
FROM
crm_sales_lead `lead`
GROUP BY
creation_date;"""

CLOSED_OPPS = """
SELECT
date_format(closed_at, '%Y-%m-%d') `close_date`,
count(1) `count`
FROM
crm_sales_opportunity
WHERE
status_id = 'won'
GROUP BY
`close_date`;"""

Answer

I think you need four backslashes in the Python string literal to represent two backslash characters needed by MySQL, to represent a backslash character.

MySQL needs two backslashes in a string literal to represent a backslash character. The SQL text you have works in MySQL because the string literals contain two backslash characters.

But in the Python code, the SQL statement being sent to MySQL contains only single backslash characters.

That's because Python also needs two backslashes in a string literal to represent a backslash character, just like MySQL does.

So, in Python...

  """CRM\\Bundle"""
        ^^

represents a string containing only one backslash character: 'CRM\Bundle'

To get a string containing two backslash characters: 'CRM\\Bundle'

You would need four backslashes in the Python literal, like this:

  """CRM\\\\Bundle"""
        ^^^^