Create Price Database Using SQLAlchemy & Python

Learn how to create a price database for crypto, forex, futures, and stocks in PostgreSQL using Python and SQLAlchemy.

You should already know how to connect to a PostgreSQL Database Using SQLAlchemy), and you can download the code for this post at the Analyzing Alpha GitHub Repo.

Steps to Create the Price Database

Below is the Python code demonstrated in the video and Jupyter notebook to create the database in the following entity-relationship diagram (ERD):

1. Get Imports

import enum
import numpy as np
import pandas as pd

from sqlalchemy import BigInteger, Boolean, Column, \
                       Date, DateTime, Enum, Float, ForeignKey, Integer, \
                       String, UniqueConstraint, and_, func
from sqlalchemy.orm import relationship
from psql import Base, db, session

Let’s create a new class that inherits from enum to create constants that we can enumerate over for each of the markets that we’ll analyze.

2. Create Market Class

class Market(enum.Enum):
    crypto = 'crypto'
    stock = 'stock'
    forex = 'forex'
    futures = 'futures'

3. Create SQLAlchemy Classes & Tables

We’ll create two tables. The first is the symbol and minute_bar tables, which have a parent/child relationship. For each table, we create a class and inherit it from Base, which lets SQLAlchemy know this is an SQLAlchemy class/table.

class Symbol(Base):
    __tablename__ = 'symbol'
    id = Column(Integer, primary_key=True, autoincrement=True)
    ticker = Column(String(50), nullable=False)
    name = Column(String(200), nullable=False)
    market = Column(Enum(Market), nullable=False)
    active = Column(Boolean, nullable=False)

We’ll create a relationship on the minute_bar table that utilizes backref. SQLAlchemy understands to look at the classes and identify the type of relationship that exists. In our case, it’s a one-to-many relationship. Additionally, we create UniqueConstraint on the symbol_id and date — in other words, we should only have one bar per date per symbol.

class MinuteBar(Base):
    __tablename__ = 'minute_bar'
    id = Column(BigInteger, primary_key=True)
    date = Column(DateTime, nullable=False)
    open = Column(Float)
    high = Column(Float)
    low = Column(Float)
    close = Column(Float)
    volume = Column(Float)
    symbol_id = Column(Integer,
    symbol = relationship('Symbol', backref='minute_bars')
    UniqueConstraint(symbol_id, date)

4. Create the Database

With our table classes created, let’s create a function that creates our database. We use the Base metadata to create all of our classes that inherit from Base.

def create():

Leave a Comment