Flask Database Design with ORM

Question

I am designing a Stock Currency application and for that, I created a database. I searched for my question here first but the answerer told me to ask the same question here.

flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()


class Stock_Data(db.Model):
    __tablename__ = 'stock_datas'
    # Column names start with capital letter for convention to use data easier (for now)
    id = db.Column(db.Integer(), primary_key=True)
    Date = db.Column(db.DateTime, nullable=False)
    Open = db.Column(db.Float(), nullable=False)
    High = db.Column(db.Float(), nullable=False)
    Low = db.Column(db.Float(), nullable=False)
    Currency_Close = db.Column(db.Float(), nullable=False)
    Volume = db.Column(db.Integer(), nullable=False)

    # Foreign Key
    stock_id = db.Column(db.Integer, db.ForeignKey('stocks.id'))

    # Foreign Attribute To Reach
    stock = db.relationship('Stock', backref='Stock_Data', primaryjoin='Stock_Data.stock_id==Stock.id', lazy=True)


class Stock(db.Model):
    __tablename__ = 'stocks'
    id = db.Column(db.Integer(), primary_key=True)
    symbol = db.Column(db.String(10), unique=True)
    name = db.Column(db.String(50), unique=True)
    sector = db.Column(db.String(10), nullable=True)
    currency = db.Column(db.String(3), nullable=False)


class Parity_Data(db.Model):
    __tablename__ = 'parity_datas'
    id = db.Column(db.Integer(), primary_key=True)
    Parity_Close = db.Column(db.Float())
    Date = db.Column(db.DateTime, nullable=False)

    # Foreign Key
    parity_id = db.Column(db.Integer, db.ForeignKey('parities.id'))

    # Foreign Attribute To Reach
    parity = db.relationship('Parity', backref='Parity_Data', primaryjoin='Parity_Data.parity_id==Parity.id', lazy=True)


class Parity(db.Model):
    __tablename__ = 'parities'
    id = db.Column(db.Integer(), primary_key=True)
    parity_name = db.Column(db.String(8), unique=True)

I created this database design all classes are in separate files. Sotck is related to Stock_Data (1-N) and Parity is related to Parity_Data (1-N) as seen.

I am creating a connection between Stock and Parity by

string_stock = 'APPL'
stock = session.query(Stock).filter(Stock.symbol == str(string_stock).upper()).first()
stock_data = session.query(Stock_Data).filter(Stock_Data.stock_id == stock.id).all()
parity = session.query(Parity).filter(Parity.parity_name.endswith(stock.currency)).first()
parity_data = session.query(Parity_Data).filter(Parity_Data.parity_id==parity.id).all()

By this method, I can fetch all stock_data and parity_data separately. However, when I try to merge stock_data and parity_data I get two lists and cannot be merged. After that, I tried

data_joined_on_time = session.query(Stock_Data).join(Parity_Data, Stock_Data.Date == Parity_Data.Date).all()
dir(session.query(Stock_Data).join(Parity_Data, Stock_Data.Date == Parity_Data.Date).all()[0])  

This method joins data but the attributes: ['Currency_Close', 'Date', 'High', 'Low', 'Open', 'Volume', 'class', 'delattr', 'dict', 'dir', 'doc', 'eq', 'format', 'ge', 'getattribute', 'gt', 'hash', 'init', 'init_subclass', 'le', 'lt', 'mapper', 'module', 'ne', 'new', 'reduce', 'reduce_ex', 'repr', 'setattr', 'sizeof', 'str', 'subclasshook', 'table', 'tablename', 'weakref', '_decl_class_registry', '_sa_class_manager', '_sa_instance_state', 'id', 'metadata', 'query', 'query_class', 'stock', 'stock_id']

There is nothing about Parity_Data part.

My question consists of two parts:

1. I could not think of another way for my database design.Is my database design incorrect?
2. Is there a way to merge those two (stock_data and parity_data) by SQLAlchemy? I would like to filter first and join after it.
   If there is no way I will merge them on pandas dataframe but firstly I want to try it on SQLAlchemy.

Answer 1

At the end of the day I used pandas.

df = pandas.read_sql_query("""My join query""")

This all just solved my problem.