Up next in 10
Query PostgreSQL Price Database Using SQLAlchemy, Python & Pandas
181 views
Dec 11, 2024
Learn how to query a price database using SQLAlchemy to get crypto, stocks, futures, and forex OHLCV price data using Python & Pandas. š Subscribe for more: https://bit.ly/3lLybeP š Follow along: https://analyzingalpha.com/query-price-data-sqlalchemy/ 0:00 Introduction 0:22 Get Imports Including Model & PSQL 0:33 Symbol & MinuteBar Tables for Crypto, Stocks, Futures & Forex 1:45 Create Get Symbols Function 3:39 Create Get MinuteBars Function 5:20 Define SQLAlchemy Query 7:23 Query Database with Pandas #crypto #python #pandas #trading #backtesting --- Videos are intended strictly for information, education, and entertainment purposes. Do not base any investment decisions based upon these materials. Please read the disclaimer: https://analyzingalpha.com/disclaimer
View Video Transcript
0:00
hello world today we're going to query
0:01
price data from a postgres database
0:04
using sq alchemy we'll use the database
0:07
we created in the last video for future
0:09
crypto stocks forex and futures algo
0:11
trading purposes if you haven't watched
0:13
that video i'll put a card up here and a
0:15
link to this database series in the
0:17
description below this should be a quick
0:19
one so let's get right to it and create
0:21
some code
0:22
we'll start by grabbing our imports
0:24
we're going to need four of them we're
0:26
going to grab date time to
0:28
manipulate our dates pandas to
0:31
manipulate data frames and also query
0:33
our database we're going to get our
0:36
models that we created in the previous
0:38
video
0:39
and for those of you that are new here
0:40
we're creating a database to be able to
0:43
trade stocks crypto forex and futures
0:45
we're starting with the symbol table
0:47
which is essentially the parent table
0:49
and then the minute bar table which is a
0:51
child table with uh ohcv
0:55
for open high low close volume data
0:58
at minute bar resolution or minute
1:01
resolution and then we don't want to
1:02
have to create connection code every
1:04
time that we want to connect to our
1:06
database so we'll just get the psql code
1:09
from two videos ago and if you're not
1:11
using postgres you could use any
1:13
database that you really want
1:15
because sq alchemy is essentially
1:17
database agnostic you know once you set
1:20
it up properly so let's grab those
1:22
imports we import
1:24
date time as dt
1:26
import pandas as pd
1:29
from models import
1:32
r2 table symbol
1:34
and minute bar
1:35
and then from psql import db and session
1:40
now with our imports out of the way
1:43
let's think about what we need to do we
1:45
need to create two functions one to get
1:47
our symbol data and the other to get our
1:49
minute bar data we'll start off with
1:51
symbol it's the easier one they're both
1:54
easy but this is very easy
1:58
get symbols
2:00
perfect
2:01
and i will as always upload this to
2:03
github so you can have a copy of this
2:06
notebook and work along with me we'll
2:08
create a function and we'll just for now
2:11
we'll say market equals none because
2:14
we're going to support markets in the
2:15
future but we won't actually add that
2:17
code right now we're just going to get
2:18
the symbols that are currently
2:20
in the
2:21
symbol
2:23
database we'll do query
2:25
equals
2:26
session we're using the sql community
2:28
session we're querying that session
2:30
we're passing it the symbol model
2:34
and then we'll grab the statement from
2:36
there so that'll just output essentially
2:38
an sql statement
2:40
and then we can use pandas to read that
2:43
uh statement and grab the data so we'll
2:46
do symbol
2:49
symbols equals pd.read sql
2:52
pass it to query
2:54
pass it to database and then we'll say
2:56
we want the index
2:57
column
2:58
the id
3:00
and then we need to return those symbols
3:02
to symbols
3:05
and let's see if that worked hopefully
3:07
they're making mistakes
3:08
get
3:09
symbols
3:11
and
3:12
of course
3:13
that symbols is not defined do i not hit
3:16
enter
3:16
here oh that's why
3:20
symbols
3:27
there we go
3:28
and you can see that we have our 311
3:31
crypto tickers now available to us
3:34
so with that out of the way let's get
3:36
our minute bar
3:39
we'll do three
3:43
get minute bars
3:47
okay let's think about what we want to
3:48
do here right so we don't want to just
3:51
blanket get all of our minute bars we
3:53
want to be able to pass it or pass this
3:55
function
3:57
some tickers
3:58
and we also want to be able to pass it
4:00
start and an end date right because
4:02
sometimes we don't want all of the
4:03
minute bars because if you have a really
4:05
large
4:06
list of tickers all those minute bars
4:08
will take a really long time or
4:10
depending upon how much memory you have
4:11
it could actually crash your system so
4:14
we want to make sure that we can pass
4:16
our function the tickers we want and the
4:17
start and end date if for some reason we
4:20
don't give it a start and end date we'll
4:22
just say let's get the most recent year
4:25
those sound like good defaults we'll
4:26
type in create a new function get bars
4:30
tickers
4:31
we'll do start equals none
4:33
and equals none perfect
4:36
now we want to say if we pass in a start
4:39
let's use that start otherwise let's use
4:42
a year ago we'll do start
4:45
start equal start
4:47
it starts so start if start exists and
4:50
there's not none else
4:52
dt date today which just passes it the
4:56
current
4:57
day of today
4:59
and then dt time delta
5:01
where we're subtracting uh 52 weeks
5:04
right essentially a year
5:06
and then we'll do the same thing for n
5:08
except end will just be for today else
5:14
dt
5:15
date night so no time delta
5:18
okay so that's easy enough and the query
5:20
is pretty easy too uh it's a little bit
5:22
more complex than the prior one
5:25
but essentially we're going to query
5:27
our tickers right our ticker
5:30
table and join the minute bar table and
5:33
and confine it to our start dates and
5:36
the tickers that we passed in and then
5:38
we'll order it at the end for good
5:40
measure
5:41
do session
5:42
dot query
5:44
pass it the symbol
5:47
object and then ticker
5:50
right because we just want the ticker
5:51
for this high open high low close
6:00
now we'll join
6:01
the minute bar
6:07
and
6:08
the way that this works is that in our
6:10
model we define relationships previously
6:13
so sq alchemy is smart enough to know
6:16
what we're joining on right we're not
6:18
explicitly saying join you know
6:21
the minute bar on the ticker id that
6:23
matches the symbol id right
6:26
when i say ticker id i mean ticker
6:28
underscore id
6:34
then we'll do we'll filter
6:36
between the dates
6:38
and our ticker so
6:39
filter minute bar
6:41
date between
6:43
start and end
6:47
join
6:48
filter
6:53
and filter
6:54
symbol
6:56
thicker
6:58
in
6:59
thickers
7:00
right if you pass it we're passing it a
7:02
list of tickers then we'll order by
7:08
the ticker and then the date
7:20
let's grab that statement as we did
7:22
previously
7:23
and then we passed that to pandas so the
7:26
bars equals pd read sql password the
7:29
statement we just created
7:32
the database and we'll parse the dates
7:35
so we want it to be
7:37
the
7:37
date format
7:40
date
7:44
or date type i should say and returns
7:48
bars
7:50
okay
7:51
let's see if that worked we'll do minute
7:54
bars equals get bars
7:56
we'll pass it uh the symbols but we
7:59
don't pass it all of the symbols we'll
8:01
grab
8:02
the symbols the ticker field
8:04
remember right here
8:06
and then we'll just grab the first five
8:10
and we'll pass
8:11
that to list
8:15
right so that'll give provide a list of
8:17
tickers
8:18
to our minute bar and let's print that
8:20
out to see if this worked
8:23
and what did i do wrong
8:25
week should be weeks
8:29
okay
8:32
weeks 52 oh my goodness leo weeks
8:36
equals
8:37
insert
8:39
i hit the insert key by accident
8:42
okay and that'll print out our minute
8:44
bars
8:47
see i told you that would be a quick one
8:49
i think that's our shortest video in the
8:51
series today but that's okay because the
8:53
next one is going to be a little bit
8:54
longer so let's think about where we are
8:57
we now have a price database and we can
9:00
actually get data out of that price
9:02
database so now we need to fill our
9:04
database with historical data and that
9:06
leaves me with a little bit of a dilemma
9:08
because i need to kind of think about
9:10
where you guys want to get data from i
9:12
think i'll start with some paid sources
9:14
we'll grab
9:15
polygon.io and then maybe quandl and
9:18
then after that we'll do some free
9:20
sources like yahoo finance but if
9:22
there's a free or paid source that
9:23
you're interested in learning how to
9:25
query let me know in the description
9:27
below and we'll see what we can do so
9:29
with that being said polygon dot io is
9:32
the next video and i hope to see you
9:34
there thanks and see you soon
#Programming