Saving API output async using SQLAlchemy and Tornado

Question

I want to save the output of a currency API in a MySQL table using SQLAlchemy and Tornado but when I loop over the JSON results the API returned and I insert each one to the database, the app get stuck. No other procedures can be executed until all inserts are completed when this happens.

I guess I should execute the insert also as a coroutine but not sure how to do that. I know there are several libraries for async SQLAlchemy such as Asyncio but are they really needed when using Tornado?

The code below blocks when executing the loop on the bottom and Currency_rate

from datetime import datetime
from decimal import Decimal
import urllib

import tornado.web
import tornado.httpclient
from tornado import gen

from src.entities.currency import list_currencies, view_iso_a3_currency
from src.entities.currency_rate import Currency_rate

@gen.coroutine
def currencylayer_currency_rate():
    http_client = tornado.httpclient.AsyncHTTPClient()
    base_url = "http://apilayer.net/api/live?"
    base_currency = view_iso_a3_currency('USD')
    vars = {'access_key': 'APIKEY', 'source': base_currency.iso_a3, 'format': 1}
    url = base_url + urllib.parse.urlencode(vars)
    response = yield http_client.fetch(url)
    if response.error:
        raise tornado.web.HTTPError(500)
    json = tornado.escape.json_decode(response.body)

    timestamp = datetime.fromtimestamp(int(json['timestamp'])).strftime('%Y-%m-%d %H:%M:%S')
    json_rates = json['quotes']
    for key, value in json_rates.items():
        quote_currency = view_iso_a3_currency(str(key)[-3:])
        if not quote_currency:
            continue
        currency_rate = Currency_rate(m_currency_id1 = base_currency.id,
                                      m_currency_id2 = quote_currency.id,
                                      rate = Decimal(value),
                                      date = timestamp,
                                      create_user = 1,
                                      update_user = 1,
                                      active = 1)
        currency_rate.add()

Answer 1

Unfortunately SQLAlchemy is not async, every request (operation) to db will block. Even more, concept of ORM is hard to make it work asynchronously (ref: How to make SQLAlchemy in Tornado to be async?).

You maybe interested in projects (async):

• momoko - postgres Tornado-based client, it is not an ORM,
• aiopg - postgres asyncio-based client (Tornado 4.3 and above), support for sqlalchemy query builders
• tornado-mysql - MySQL Tornado-based client

Hint:

response = yield http_client.fetch(url)
if response.error:
    raise tornado.web.HTTPError(500)

The yield will also raise a HTTPError on error, hence explicit raise is needless.