This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Country | Currency | ExchangeRate | MinTotal | |
---|---|---|---|---|
Brazil | Real | 3.13 | 5.0 | |
Canada | Canadian Dollar | 1.27 | 1.5 | |
Czech Republic | Kourna | 25.45 | 10.0 | |
Austria | Euro | 0.93 | 1.0 | |
Belgium | Euro | 0.93 | 2.0 | |
Denmark | Krone | 6.95 | 3.0 | |
USA | US Dollar | 1.0 | 1.2 | |
France | Euro | 0.93 | 2.3 | |
Germany | Euro | 0.93 | 4.1 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import pandas as pd | |
df = pd.read_csv("./countries.csv") | |
# convert our CSV to a list of dicts | |
data = pd.DataFrame(data).to_dict('rows') | |
# write your query as per usual, but parameterize your variables | |
q = """ | |
select | |
Country | |
, '{{ Currency }}' as Currency | |
, {{ ExchangeRate }} as ExchangeRate | |
, sum(Total*{{ ExchangeRate }}) as LocalCurrencyTotal | |
from | |
Customer c | |
inner join | |
Invoice i | |
on | |
i.CustomerId = c.CustomerId | |
where | |
Country = '{{ Country }}' | |
and Total > {{ MinTotal }} | |
group by | |
Country | |
""" | |
print db.query(q, data) | |
Country Currency ExchangeRate LocalCurrencyTotal | |
0 Brazil Real 3.13 449.3115 | |
1 Canada Canadian Dollar 1.27 375.9708 | |
2 Czech Republic Kourna 25.45 1087.2240 | |
3 Austria Euro 0.93 38.7159 | |
4 Belgium Euro 0.93 30.3831 | |
5 Denmark Krone 6.95 227.0565 | |
6 USA US Dollar 1.00 511.1800 | |
7 France Euro 0.93 161.1783 | |
8 Germany Euro 0.93 112.3812 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This is a great and neat solution for redundant SQL queries. Thanks for sharing!
(I think there is a minor typo in the 5th line. It should be
data = pd.DataFrame(df).to_dict('rows')
)