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, sessionLet’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,
ForeignKey('symbol.id',
onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False)
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():
Base.metadata.create_all(db)
create()