Python3 script not showing same result as MySQL engine for same query

Question

My python3 script is not generating the same result as MySQL. My query returns those rows whose value have changed over the week.

Python script:

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 <> pw.probability) OR (cw.stage <> pw.stage) " \
    "OR (cw.Amount<>pw.Amount) OR (cw.CloseDate <> pw.CloseDate)))".format(Prev_Week,Curr_Week)
cursor.execute(query)
results = dictfetchall(cursor)
print(results)

Output:

[{
        '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'
    }
]

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 = "2016-35" WHERE cw.Week = "2016-36" 
AND IF(pw.opportunityid IS NULL, TRUE, ((cw.probability <> pw.probability) OR (cw.stage <> pw.stage) OR (cw.Amount<>pw.Amount) OR (cw.CloseDate <> pw.CloseDate)))

Expected Output shown correctly by MySQL:

+-----------------+-------------+-------------+----------------+----------------
+------------+-------------+------------+------------+
| 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 |

the data are the same. It's just that python returns the result as a python dictionary. You need to format it according to your needs. — Jean-François Fabre
– Jean-François Fabre ♦, Commented Sep 8, 2016 at 19:47
@Jean-FrançoisFabre: How do I get results from both tables in the output if script is fetching complete output? — r0xette
– r0xette, Commented Sep 8, 2016 at 19:58
Formatting values to SQL query strings can be error prone and outright dangerous. Have a look at how to use placeholders with your DB-API. Those will handle quoting, escaping and converting python values to SQL constructs for you. — Ilja Everilä
– Ilja Everilä, Commented Sep 8, 2016 at 19:58

Phidelux · Accepted Answer · 2016-09-08 20:20:58Z

1

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:

SELECT 
    p1.name AS p1name, 
    p2.name AS p2name 
FROM 
    p1, p2 
WHERE 
    p1.id != p2.id

edited Sep 8, 2016 at 20:20

answered Sep 8, 2016 at 20:15

Phidelux

2,2911 gold badge35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

r0xette Over a year ago

Wow. How can I forget that. Thanks for your help.

Phidelux Over a year ago

If this works for you, consider accepting the answer. ;-)

Collectives™ on Stack Overflow

Python3 script not showing same result as MySQL engine for same query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related