3

Is there a way to perform an SQL query that joins a MySQL table with a dict-like structure that is not in the database but instead provided in the query?

In particular, I regularly need to post-process data I extract from a database with the respective exchange rates. Exchange rates are not stored in the database but retrieved on the fly and stored temporarily in a Python dict.

So, I have a dict: exchange_rates = {'EUR': 1.10, 'GBP': 1.31, ...}.

Let's say some query returns something like: id, amount, currency_code.

Would it be possible to add the dict to the query so I can return: id, amount, currency_code, usd_amount? This would remove the need to post-process in Python.

chrisvdb
  • 2,080
  • 2
  • 20
  • 28

1 Answers1

1

This solution doesn't use a 'join', but does combine the data from Python into SQL via a case statement. You could generate the sql you want in python (as a string) that includes these values in a giant case statement.

You give no details, and don't say which version of Python, so it's hard to provide useful code. But This works with Python 2.7 and assumes you have some connection to the MySQL db in python: 

exchange_rates = {'EUR': 1.10, 'GBP': 1.31, ...} 

# create a long set of case conditions as a string
er_case_statement = "\n".join("mytable.currency = \"{0}\" then {1}".format(k,v) for (k,v) in exchange_rates.iteritems())

# build the sql with these case statements
sql = """select <some stuff>, 
  case {0}
  end as exchange_rate,
  other columns
  from tables etc
  where etc
""".format(er_case_statement)

Then send this SQL to MySQL

I don't like this solution; you end up with a very large SQL statement which can hit the maximum ( What is maximum query size for mysql? ).

Another idea is to use temporary tables in mysql. Again assuming you are connecting to the db in python, with python create the sql that creates a temporary table and insert the exchange rates, send that to MySQL, then build a query that joins your data to that temporary table.

Finally you say you don't want to post-process in python but you have a dict from somewhere do I don't know which environment you are using BUT if you can get these exchange rates from the web, say with CURL, then you could use shell to also insert these values into a MySQL temp table, and join there.

sorry this is general and not specific, but the question could use more specificity. Hope it helps someone else give a more targeted answer.

Community
  • 1
  • 1
billspat
  • 531
  • 6
  • 8