Skip to content

Instantly share code, notes, and snippets.

@glamp
Last active December 24, 2015 17:12
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save glamp/c8c2b894c84a6b11ee71 to your computer and use it in GitHub Desktop.
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
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
@yassineAlouini
Copy link

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'))

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment