I recently started working on my own autotrader. There’s still much to be done, but I’ve finished the first step – collecting data and put them in a database. I’ve got a PostgreSQL server running on Docker, and a script that reads data using the AlphaVantage API and writes to my database.

The next step would be to write my own Python API to query data from the database. The easy way for me would be to stick a bunch of SQL queries in some python functions, but why do that when you can make life more complicated! I’ve been wanting to learn about ORM, and decided this would be my chance to try it out with SQLAlchemy.

foreign keys

I first created these models:

from sqlalchemy import Column, ForeignKey
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION, TEXT, TIMESTAMP
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

class ModelMixin:
    __table_args__ = {"schema": "alphavantage"}
    created_at = Column(TIMESTAMP(timezone=True))

class Currency(Base, ModelMixin):
    __tablename__ = "currencies"
    currency = Column(TEXT, primary_key=True)

class FxPrice(Base, ModelMixin):
    __tablename__ = "fx"
    ts = Column(TIMESTAMP(timezone=True), primary_key=True)
    currency_from = Column(
        TEXT, ForeignKey("currencies.currency"), primary_key=True
    )
    currency_to = Column(
        TEXT, ForeignKey("currencies.currency"), primary_key=True
    )
    price = Column(DOUBLE_PRECISION)

    _currency_from = relationship("Currency", foreign_keys=[currency_from])
    _currency_to = relationship("Currency", foreign_keys=[currency_to])

With an existing database, my goal is to reproduce the tables with a SQLAlchemy model, so I can interact with the database through this bit of Python code.

But it didn’t work, and kept producing this error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'fx.currency_from' could not find table 'currencies' with which to generate a foreign key to target column 'currency'

After much digging, I found this is because the tables are not in the default public schema, and the solution is to specify schema name while declaring the columns with foreign keys:

    currency_from = Column(
        TEXT, ForeignKey("alphavantage.currencies.currency"), primary_key=True
    )
    currency_to = Column(
        TEXT, ForeignKey("alphavantage.currencies.currency"), primary_key=True
    )

alembic

Alembic is a tool for helping with database migrations. It helps tie together your SQLAlchemy models with tables in your database, so that any change you make in your ORM model (e.g. add a column, introduce a new table, etc) will be automatically reflected in your databse.

It was quite easy to set up, but when I ran

alembic revision --autogenerate
alembic upgrade head --sql

to try it out, it ended up trying to create two new tables in my database public."alphavantage.currencies" and public."alphavantage.fx". The expected behaviour is to do nothing, because those two tables already exist in my database.

Apparenly, by default, alembic only discovers tables in the default schema. In this case the tables are in a different alphavantage schema, and that caused some confusion.

To change its default behaviour, locate the env.py file in ./alembic, and add this line

include_schemas=True

in the bit about

context.configure()

of both of these two functions

def run_migrations_offline():
    ...

def run_migrations_online()
    ...

conclusion

Maybe check your schema names when SQLAlchemy can’t seem find a table that actually exists!