A query that works in MySQL terminal fails when executed via PyMySQL
<p>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 <code>MySQL [db] &gt;</code> environment. </p>

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

<p>I've checked out <a href="http://stackoverflow.com/questions/28489396/mysql-connector-python-query-works-in-mysql-but-not-python">this post</a>, <a href="http://stackoverflow.com/questions/11705450/sql-query-works-in-console-but-not-in-python">this post</a>, and <a href="https://github.com/PyMySQL/PyMySQL/issues/399" rel="nofollow">this post</a> and none of them seem to be addressing the issue. </p>

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

<pre><code>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
</code></pre>

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

<pre><code>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`;"""
</code></pre>

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

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

<p>But in the Python code, the SQL statement being sent to MySQL contains only <em>single</em> backslash characters.</p>

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

<p>So, in Python... </p>

<pre><code>  """CRM\\Bundle"""
        ^^
</code></pre>

<p>represents a string containing only <em>one</em> backslash character: <strong><code>'CRM\Bundle'</code></strong></p>

<p>To get a string containing <em>two</em> backslash characters: <strong><code>'CRM\\Bundle'</code></strong></p>

<p>You would need <em>four</em> backslashes in the Python literal, like this:</p>

<pre><code>  """CRM\\\\Bundle"""
        ^^^^
</code></pre>

python mysql python-3.x pymysql