Up next in 10
Create Price Database Using SQLAlchemy & Python
449 views
Dec 11, 2024
Learn how to create a price database for crypto, stocks, futures, and forex using SQLAlchemy, Python, and Postgres for future algo trading and backtesting purposes. š Subscribe for more: https://bit.ly/3lLybeP š Follow along: https://analyzingalpha.com/create-price-database-postgresql-sqlalchemy/ 0:00 Introduction 0:22 Entity Relationship Diagram 0:33 Symbol & MinuteBar Tables for Crypto, Stocks, Futures & Forex 1:43 Login to Database 2:27 Grant User Superuser Permissions 3:08 Import Modules 3:59 Import SQLAlchemy 5:22 Verify Database Creation 5:41 Create Market Class 5:58 Create Symbol Class 7:15 Create MinuteBar Class 9:59 Create Database #python #sqlalchemy
View Video Transcript
0:00
hello world today we're going to create
0:02
our price database if you haven't been
0:04
following along you're going to have to
0:06
check out the previous video where i
0:07
show you how to connect to a postgres
0:09
database using sq alchemy but if you
0:11
have been following along today we're
0:13
going to create our first two tables
0:15
we're going to create our symbol table
0:16
and our minute bar table but instead of
0:19
me just talking about it why don't i
0:20
show you on the erd
0:22
an erd or an entity relationship diagram
0:26
shows us the various relationships
0:28
between the tables and their fields and
0:30
the field types it's that easy
0:32
now you'll also notice i've color coded
0:35
the tables that we're going to create
0:37
today in purple these tables are the
0:39
symbol and minute part tables are
0:41
applicable to all of the markets that
0:43
we're going to be covering such as
0:45
crypto stocks futures and forex
0:47
and you'll notice there's some tables in
0:49
green below which we're going to cover
0:50
in a separate video
0:52
these tables will enable us to perform
0:54
analysis on stocks or i should say
0:57
fundamental analysis on stocks as we're
1:00
going to import quarterly financial data
1:02
into the securities fundamental database
1:05
and daily metric data such as the e v to
1:07
ebitda or p e ratios into the daily
1:10
metrics database now some of the tables
1:13
that we're going to create aren't listed
1:15
here yet such as the economic tables
1:17
we're going to pull from various sources
1:19
such as the federal reserve the
1:21
constituents tables where we're going to
1:23
have the s p 500 and nasdaq constituents
1:26
but for now this is where we're going to
1:28
start in future videos we'll add to this
1:30
erd i just didn't want to make it too
1:33
overwhelming at first
1:35
now with the understanding of what an
1:36
entity relationship diagram is and the
1:38
tables that we're going to create today
1:40
are let's create some code
1:43
we'll start by opening up a new terminal
1:45
window and logging into postgres
1:48
now keep in mind we're using sq alchemy
1:51
which abstracts away the technical
1:53
details of whatever database
1:55
implementation we're using so you
1:57
theoretically could use any database
1:59
that you want assuming sql supports it
2:02
now i'm going to use psql for those that
2:05
are following along to make things
2:06
easier but like i said you could use
2:08
whatever database you want your command
2:11
just might be a little bit different so
2:13
first we'll list all of the databases
2:15
now you'll notice that no alpha database
2:18
exists but in the previous episode we
2:21
created code that if no database exists
2:23
when we tried to connect it would create
2:25
the database for us
2:27
in order to do that we need to make sure
2:29
that the alpha user has super user
2:32
powers or has the ability to create
2:34
databases and we can do that by typing
2:37
alter user alpha
2:40
with super user i might call it enter
2:43
now this will change the alpha user to a
2:46
super user i don't suggest doing this if
2:49
this is a production machine
2:52
you should only ever give the minimum
2:54
amount of privileges any user needs in
2:56
order to connect and do what they need
2:58
to do but since ours is just a research
3:00
machine not a big deal
3:02
i'm going to quit out here and clear the
3:05
screen now we'll jump over to the
3:07
jupiter notebook we'll start in the
3:09
usual fashion and that's by grabbing our
3:11
imports
3:13
there are a few of them so we'll cover
3:15
each one one at a time so we import enum
3:18
or emu essentially um allows us to
3:21
create
3:22
a bunch of constants that we can
3:24
enumerate or iterate over for our case
3:26
we're going to create a market class and
3:28
we're going to inherit the e-name class
3:31
so that way we can
3:32
enumerate over our various markets which
3:35
will be crypto forex futures and stocks
3:37
okay we'll also grab
3:40
our numpy and pandas we'll do import
3:43
pi fmp
3:45
import and instant nfpd
3:47
and then we'll grab quite a few things
3:49
from sq alchemy
3:52
specifically these will be the different
3:54
fields and some constraint and
3:57
relationship information so we'll do
3:58
from sql
4:01
import
4:02
big integer we need bigins for our
4:05
minute bar ids because if we only use a
4:08
standard integer it's likely that we'll
4:10
run out of ids causing problems in the
4:12
future
4:13
boolean
4:15
column
4:18
eight
4:19
eight time and
4:21
float
4:22
foreign key this is most of the stuff we
4:25
saw in the erd diagram previously
4:29
let's see in teacher
4:35
unique
4:36
constraint
4:38
then we're going to have constraints
4:39
there are multiple columns some and
4:41
logic and func
4:43
we'll also want to grab relationship
4:45
from a skewer for me of lrm so we can
4:48
create relationships to a various
4:50
classes
4:51
relationship
4:56
there we go having trouble typing and
4:59
then from the previous episode we're
5:01
going to want to grab
5:02
um bass and db in session uh from psq
5:06
from psql to create it again last video
5:09
import
5:10
base bp in session then enter if all
5:14
works
5:15
it will run and we'll actually create a
5:17
database behind the scenes so let's
5:19
switch over to the terminal and see if
5:21
that actually happened
5:23
with psql
5:24
we'll list our databases and it looks
5:26
like whenever we did connect
5:29
to postgres it created our alpha
5:32
database for us that's great
5:34
go ahead and clear the screen and we'll
5:36
jump back over to jupiter now with our
5:39
imports available to us let's create our
5:41
market class class
5:44
market
5:46
lesson
5:48
and then we'll create our four markets
5:50
will be crypto stocks forex and futures
5:57
easy enough now let's create our symbol
6:00
table and we create our tables as
6:02
classes and sql comments we'll create
6:04
class
6:05
symbol
6:08
base
6:09
our table name which is different than
6:11
our class name our class named
6:12
capitalized
6:14
the symbol
6:17
id
6:19
this is just an integer we don't need uh
6:22
big in here because we're not going to
6:23
have that many symbols
6:25
primary
6:27
true
6:29
increment
6:32
ticker column
6:35
and we'll make it 50.
6:37
now for all of these fields we're going
6:39
to set nullable equal to false meaning
6:41
it has to exist let's fill the name name
6:45
column
6:49
spring 200
6:51
available with false
6:54
market
6:56
column
6:59
snr market class
7:02
into false
7:04
and active
7:09
well
7:10
false
7:12
that looks like that worked okay perfect
7:15
now we'll create our minute bar class
7:18
class
7:20
minute bar
7:21
ascend base
7:23
the table name
7:28
we go to minute bar
7:31
the id is our big end as we mentioned
7:34
previously
7:39
once the primary
7:41
key is true
7:51
false
7:53
and we'll add our open hi-lo and close
8:03
and now let's add our symbol
8:05
relationship from the symbol
8:09
id
8:10
column
8:14
integer
8:16
foreign key
8:21
symbol id
8:23
and then on update or delete
8:26
recascade
8:28
okay because if we delete any symbol it
8:31
should delete the minute bars too
8:35
paid
8:47
and then now we're going to create our
8:48
relationship with symbol relationship
8:54
and this is with the symbol class
8:57
okay and we back reference a symbol
9:00
table so back breath equals symbol now
9:04
this just creates a class relationship
9:06
that we can use
9:08
in our python code
9:10
relationship
9:14
then we'll create
9:16
one unique constraint
9:20
we should never have a symbol id and the
9:23
date being the same right so we'd have a
9:26
unique date for every symbol
9:30
and remember that date is actually a
9:32
date time
9:34
enter perfect oh no
9:40
do you hear what i do
9:44
let's be like that
9:56
okay now all that's left to do is to
9:58
create our database so that
10:01
create function for this
10:03
oops
10:06
base metadata
10:08
create all and this will create all of
10:10
the classes
10:12
um that we provided to sqlcoming we'll
10:15
click create
10:17
enter and hopefully uh that ran we can
10:19
go ahead and check our terminal to see
10:21
if it did
10:23
we'll log in the sql
10:25
we have alpha we'll connect the alpha
10:29
open d key for the different tables so
10:32
we have both the minute bar and symbol
10:35
start from symbol
10:37
we'll see all of our columns that we
10:39
created and same thing for
10:41
minute bar
10:45
and you can see that we have that and we
10:47
also have the symbol id for the
10:48
relationship and sql me behind the
10:51
scenes understands that it is a
10:53
one-to-many relationship and we'll be
10:55
able to use that whenever we're
10:57
manipulating this data with python
11:00
congratulations you did it you now have
11:02
a price database that you can use to
11:04
import any asset with minute bar data in
11:07
fact we're going to use this database
11:09
that we created today to import crypto
11:11
data in this video right here now if you
11:14
haven't seen the previous video on
11:16
obtaining that crypto data i'll link
11:18
this right here so those are the two
11:20
videos you need this one first that one
11:23
second and i'd like to ask you if you
11:25
love this video please like and
11:27
subscribe it lets the google algorithm
11:29
know that this is a video worth sharing
11:31
thank you so much and i'll see you in
11:32
the next one
#Currencies & Foreign Exchange
#Investing
#Software