Python3 script not showing same result as MySQL engine for same query
<p>My python3 script is not generating the same result as MySQL. My query returns those rows whose value have changed over the week.</p>

<p>Python script:</p>

<pre><code>query = "SELECT cw.opportunityid, cw.probability, pw.probability, cw.stage, pw.stage, cw.amount, pw.amount, " \
    "cw.closedate, pw.closedate " \
    "FROM opty_data cw " \
    "LEFT JOIN opty_data pw ON cw.opportunityid = pw.opportunityid " \
    "AND pw.Week = \"{0}\" " \
    "WHERE cw.Week = \"{1}\" " \
    "AND IF(pw.opportunityid IS NULL, TRUE, ((cw.probability &lt;&gt; pw.probability) OR (cw.stage &lt;&gt; pw.stage) " \
    "OR (cw.Amount&lt;&gt;pw.Amount) OR (cw.CloseDate &lt;&gt; pw.CloseDate)))".format(Prev_Week,Curr_Week)
cursor.execute(query)
results = dictfetchall(cursor)
print(results)
</code></pre>

<p>Output:    </p>

<pre><code>[{
        'opportunityid' : '1',
        'probability' : '50',
        'amount' : Decimal('30.35'),
        'closedate' : datetime.date(2016, 8, 22),
        'stage' : 'Proposal'
    }, {
        'opportunityid' : '2',
        'probability' : '50',
        'amount' : Decimal('115.00'),
        'closedate' : datetime.date(2016, 6, 30),
        'stage' : 'Proposal'
    }, {
        'opportunityid' : '3',
        'probability' : '50',
        'amount' : Decimal('200.00'),
        'closedate' : datetime.date(2016, 8, 29),
        'stage' : 'Proposal'
    }
]
</code></pre>

<p>Query:</p>

<pre><code>SELECT cw.opportunityid, cw.probability, pw.probability, cw.stage, pw.stage, cw.amount, pw.amount, cw.closedate, pw.closedate FROM opty_data cw 
LEFT JOIN opty_data pw 
ON cw.opportunityid = pw.opportunityid AND pw.Week = "2016-35" WHERE cw.Week = "2016-36" 
AND IF(pw.opportunityid IS NULL, TRUE, ((cw.probability &lt;&gt; pw.probability) OR (cw.stage &lt;&gt; pw.stage) OR (cw.Amount&lt;&gt;pw.Amount) OR (cw.CloseDate &lt;&gt; pw.CloseDate)))
</code></pre>

<p>Expected Output shown correctly by MySQL:</p>

<pre><code>+-----------------+-------------+-------------+----------------+----------------
+------------+-------------+------------+------------+
| opportunityid   | probability | probability | stage          | stage
| amount     | amount      | closedate  | closedate  |
+-----------------+-------------+-------------+----------------+----------------
+------------+-------------+------------+------------+
| 1 | 50          | 50          | Proposal       | Proposal
|   20.35 |    30.35 | 2016-08-22 | 2016-08-22 |
| 2 | 50          | 50          | Proposal       | Proposal
|  113.00 |    115.00 | 2016-09-06 | 2016-06-30 |
| 3 | 0           | 50          | Drop           | Proposal
|  200.00 |   200.00 | 2016-08-29 | 2016-08-29 |
</code></pre>

<p>A Python dictionary consists of unique key value pairs, so one key can just appear once in a dictionary. As your raw SQL query returns two distinct values from the same column in a single row, the second occurrence of the column overwrites the first in the dictionary. However, you can easily fix this by specifying aliases for the columns using the AS keyword. Check out the following example:</p>

<pre><code>SELECT 
    p1.name AS p1name, 
    p2.name AS p2name 
FROM 
    p1, p2 
WHERE 
    p1.id != p2.id
</code></pre>

python mysql sql python-3.x