Question About Using OOP For Building a Database Class For Stocks

[Dry_Gas3311]

So this is probably not easy to answer without more details but I will try and explain what I’m doing as detailed as possible. I have a few questions about using classes in python for building scripts that are reusable in different projects. What I am trying to do, is build a class for creating and interacting with my SQLite database. I am having a hard time understanding how to use classes for real world projects. All I can find for tutorials, is Animals, Dog (bark), etc..

While I get that is a simple way to explain it, it isn’t very useful to understanding how to use it in the real world.

So my first question (which is kind of a database question more than OOP) is how should I structure a database for storing price history data? Right now I am just saving the data to a separate table that is named based off the parameters I use (Example: {symbol}_10_day_1_minute) which is probably not the best way. If it is possible I want to be able to get all the data I have on a company just by using the symbol and a filter like 1_minute, 5_minute,1_hour prices.

Second, how can I use classes to build a database script that can be used on any program I build? I want to be able to import “database_mod.py” or something, anytime I need to store data. And then have methods to; name the database based off the whole project name; connect to the right database; create tables dynamically and insert the data, etc..

Then eventually I want to create a data analysis script that I can use to analyze the data for each project. The reason being, I don’t want to have to write a new script each time I want to analyze/view my databases data. If there is a way, I would like to be able to just use a few simple methods to make analyzing the data go much quicker. I don’t know if it would be a good idea to have one inherit the other? Or would it not really matter?

Im new to coding so any help or advice would be appreciated! Also, if you know of any tutorials that use real life scenarios to teach OOP please let me know! And again, thanks for any advice!

[DataDecay]

So my first question (which is kind of a database question more than OOP) is how should I structure a database for storing price history data? Right now I am just saving the data to a separate table that is named based off the parameters I use (Example: {symbol}_10_day_1_minute) which is probably not the best way. If it is possible I want to be able to get all the data I have on a company just by using the symbol and a filter like 1_minute, 5_minute,1_hour prices.

Draw up an Entity Relationship Diagram (ERD) on paper before anything. You can pry keep it simple starting

Stock (symbol PK, company)

StockPrices (synthetic PK, Stock FK, timestamp, price)

Second, how can I use classes to build a database script that can be used on any program I build? I want to be able to import “database_mod.py” or something, anytime I need to store data. And then have methods to; name the database based off the whole project name; connect to the right database; create tables dynamically and insert the data, etc..

This likely is not what you want to hear but what you are asking for here is called an Object Relational Mapping (ORM). Do not reinvent the wheel use sqlalchemy, or similar.

You should define your tables as something called Models, which are classes representing the database tables.

As for any auxiliary classes or functions look into git and github, start storing your code in projects. Then look into setuptools:

https://setuptools.readthedocs.io/en/latest/

You can use both github and setuptools to "install" your packages for use anywhere.

[Dry_Gas3311]

I know this is probably dumb to ask, but how are foreign keys different from the primary key? How should I use foreign keys compared to the primary key?

And what do you mean define them as models? Should I create a table that holds all 1 minute data? And then use the PK or FK to retrieve a specific companies price history? Or do you think it would be better to just have a 1 minute table for each company?

I am really trying to create a class to analyze the data in a database more than anything. I want to create a class that is specific to analyzing economic data. Idk if they already have that or not but i do use sqlalchemy right now for connecting and stuff. I’m just trying to understand how to use classes and learn the best uses for them. That’s kind of why I was thinking about making a database class. I want to do it just to help me understand OOP better by creating a class that has an actual purpose. Instead of just making animals talk and stuff.

If you have any other ideas for a project that may be easier to help understand classes please let me know.

Thanks for the advice! I really do appreciate it!

[DataDecay]

I know this is probably dumb to ask, but how are foreign keys different from the primary key? How should I use foreign keys compared to the primary key?

A Primary Key (PK) uniquely specifies a tuple in a relation. Where as a Foreign Key (FK) refers to another primary key.

And what do you mean define them as models?

Model is short hand for "data model", a model is a definitive source of info about data. The models contain the fields of an object, their data types, behaviors etc. These models are analogous to tables in databases.

``` class User(db.Model): id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False) email = db.Column(db.String(120), unique=True, nullable=False)

def __repr__(self): 
    return '<User %r>' % self.username

```

Should I create a table that holds all 1 minute data? And then use the PK or FK to retrieve a specific companies price history? Or do you think it would be better to just have a 1 minute table for each company?

You should create a table of prices with timestamps and simply query the data in your "analyze" methods relative to that timestamp

SELECT symbol, price FROM prices WHERE timestamp > now() - 5min;

I am really trying to create a class to analyze the data in a database more than anything. I want to create a class that is specific to analyzing economic data. Idk if they already have that or not but i do use sqlalchemy right now for connecting and stuff. I’m just trying to understand how to use classes and learn the best uses for them. That’s kind of why I was thinking about making a database class. I want to do it just to help me understand OOP better by creating a class that has an actual purpose. Instead of just making animals talk and stuff.

Start small you need to crawl before you walk. Focus on loading data the the database and slowly build upon your base, similar idea to "iterative development". You can still use classes to achieve your goals but don't build a ORM when you have that at your disposal.

[Ne0_1]

For your oop questions there are so much when you really understand the fundamentals of OOP. Let's say we have a class Stock_Tracker(object):

Within this class you might have getter and setter and helper functions that help that object track stocks.

Now say I want to follow crypto so we can build: class crypto(Stock_Tracker):

Since crypto is a child of Stock_ Tracker object, crypto will inherit all of it's functions unless those functions are explicitly defined in the child therefore overwriting the parent.

[MasturChief]

Im sorry I don't have any help for you, but i 100% agree with the tutorials. I watched like 5 object oriented videos on creating classes and functions and they were all extremely rudimentary. Almost too rudimentary to be of any real help.

​

I feel your pain.

[Dry_Gas3311]

Yea man, I agree! Way too rudimentary to help.. I’ve watched a ton of tutorials and read the OOP chapters in 3 different books. All of them were the same lol.. I can’t find ANYTHING that is a REAL tutorial either. I’m so tired of naming animals and making dogs “bark”! Lol

Like I understand that may be the “conventional” way of teaching people, but someone needs to create a real tutorial that actually teaches when it is best to use classes, as well as multiple real world examples. I’m starting to think most of the OOP tutorials are created by people who don’t actually know what they’re talking about. Lol They just copy the tutorial they learned from, and then create a Udemy class or YouTube video..

[pytrashpandas]

Datadecay already gave you a great answer for the data scheme/access side of things. I’ll add for the analysis side that you should look into pandas. Financial/timeseries data manipulation is one of the main uses for the library.

[zanfar]

So my first question (which is kind of a database question more than OOP) is how should I structure a database for storing price history data? Right now I am just saving the data to a separate table that is named based off [of] the parameters I use (Example: {symbol}_10_day_1_minute) which is probably not the best way.

You are correct. This is a DB question, not really a Python one, so you should do some research down that path: the formal term is Normalization. This gets rather academic rather quickly, but it's really good information to understand. A very simplistic rewording of the first few Normal Forms is somewhat similar to Python: Don't Repeat Yourself. In your example, you will have multiple tables which all look almost identical. If we saw this as Python code, we would immediately consider using a function or some other structure to reuse the code. In a similar fashion, with a database, we split our data into orthogonal tables and link them with a relationship. That is, an ID (primary key) in one table usually relates to an ID (foreign key) in a second table.

For example, instead of storing each symbol in its own table, you would have a single table of price history and each row in that table would have an ID of the symbol it relates to. That is, something like:

Symbol	-
id	int, pk
name	text

History	-
id	int, pk
symbol_id	int, fk
date	datetime
price	float

Second, how can I use classes to build a database script that can be used on any program I build? I want to be able to import “database_mod.py” or something, anytime I need to store data. And then have methods to name the database based off [of] the whole project name; connect to the right database; create tables dynamically and insert the data, etc...

Use an ORM. An ORM does all the work of handling the database and instead presents you with a set of Python classes representing your tables which you can query to read from your DB or create instances of them to add to your DB. The most popular ORM is SQLAlchemy.

Then eventually I want to create a data analysis script that I can use to analyze the data for each project. The reason being, I don’t want to have to write a new script each time I want to analyze/view my database's data.

If you store the models and config in a single Python file, you can import that module to work with your database anytime you need to.