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] >
conn = pymysql.connect(
host = params.HOST,
user = params.USER,
password = params.PWD,
db = 'db',
autocommit = True)
test_queries = [queries.VETTED, queries.CREATED, queries.CLOSED_OPPS]
with conn.cursor() as cursor:
for query in test_queries:
() #..blank output -- doesn't make sense because corresponding query works in MySQL env
[...] #..expected output from query 2
[...] #..expected output from query 3
VETTED = """
date_format(oa.logged_at, '%Y-%m-%d') as `action_date`,
oa.id = oaf.audit_id AND
oaf.field = 'status' AND
oaf.new_text = 'Qualified' OR
oaf.new_text = 'Disqualified' OR
oaf.new_text = 'Canceled'
oa.object_class = 'CRM\\Bundle\\SalesBundle\\Entity\\Lead' AND
lead.id = oa.object_id AND
(lead.status_id = 'qualified' OR lead.status_id = 'canceled')
CREATED = """
DATE_FORMAT(lead.createdat, '%Y-%m-%d') as `creation_date`,
CLOSED_OPPS = """
date_format(closed_at, '%Y-%m-%d') `close_date`,
status_id = 'won'
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...
represents a string containing only one backslash character:
To get a string containing two backslash characters:
You would need four backslashes in the Python literal, like this: