Build a Stock Price Database in Postgresql and Python

Published: Mar 9, 2025

They say data is the new oil. And if data is the new oil then databases are the new oil tankers. And without oil tankers, your oil will spill onto the floor and be worthless.

In this video, we’re going to cover the database fundamentals you need to start storing your data properly.

We’re going to be using Postgres as this is the second most popular sql database solution and also has some great time-series extensions such as timescale db which will allow us to better store our time series data like stock prices and trade data.

We’ll start off by installing a GUI so we can visualise our data without getting lost in the deep dark underworld that is vim.

To do this on Mac we can use one simple command on homebrew:

brew install --cask pgadmin4

(if you haven’t already installed homebrew you can learn how to do that here: https://www.jonjowadwa.com/blogs/install-python-with-pyenv)

We can then create a postgres database using the official postgres docker image like this:

docker run —name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5431:5432 -d postgres

What does this mean?

If you don’t have docker installed either you can do that here: https://docs.docker.com/engine/install/

If now we now run ‘docker ps’ in our terminal we should see our container is running.

Now we’ve done this we can open up the pgadmin app we installed at the start and register a new server. To do this we will go to servers, right click > register > server. In general tab we will give it a name. In the connection tab we add the host name as localhost, the port as 5431, the username as postgres and the password will be the one we set in the command above e.g mysecretpassword. You should now have a postgres container under servers. To view the tables, click schemas > public.

We’re now ready to start interacting with our database from a python file. Let’s start by importing the postgres python package pscyopg2 and then connecting

import psycopg2

We can now add a cursor to execute SQL from python:

cur = conn.cursor()

Let’s create a simple table that has a key, a ticker, price and date:

cur.execute("""
CREATE TABLE IF NOT EXISTS stocks (
id SERIAL PRIMARY KEY,
ticker VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")

We can now simply add price data as follows:

class Stock:
 def _ _ init _ _ (self, name, price):
  self.ticker = ticker
  self.price = price

We can now view our stock data with the following:

cur.execute("SELECT * FROM stocks")
data = cur.fetchall()

Finally, lets close the connection to avoid any issues:

conn.close()
cur.close()

It’s worth noting if you did not want to use python and instead write SQL directly, you can do this from the terminal via:

docker exec -it <name of container> psql -U postgres

Did you find this article helpful?