3
\$\begingroup\$

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.
\$\endgroup\$
1
\$\begingroup\$

At the end of the day I used pandas.

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

This all just solved my problem.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.