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

Question

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 1

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