Up next in 10
Connect to PostgreSQL Using SQLAlchemy & Python
963 views
Dec 11, 2024
Learn how to connect to a PostgreSQL database using Python and SQLAlchemy. 👍 Subscribe for more: https://bit.ly/3lLybeP 👉 Follow along: https://analyzingalpha.com/connect-postgresql-sqlalchemy/ Note: If you want the database to be created automatically, you'll need to grant the user superpowers from within Postgres: =# ALTER USER alpha WITH SUPERUSER; 0:24 Open Jupyter Notebook 1:20 What is SQLAlchemy? 2:26 Import SQLAlchemy 3:05 Import Connection Settings 3:52 Create SQLAlchemy Engine Function 5:05 Create SQLAlchemy Engine 7:13 Create SQLAlchemy Session Function 7:45 Create SQLAlchemy Session #postgresql #sqlalchemy #python
View Video Transcript
0:00
have you ever wondered how to connect to
0:01
a postgres database using sq alchemy
0:04
well if you have you're in the minority
0:06
but you're in good company today we're
0:08
going to do exactly that we're going to
0:10
use sql command to connect the psql so
0:13
that way we can create our future
0:15
equities and crypto price database let's
0:18
dig into some code
0:19
again let's dive jump let's create some
0:22
code
0:23
we'll start by opening jupyter notebook
0:26
now obviously we don't want to have to
0:28
use jupyter every single time you want
0:30
to establish a connection to our
0:31
database instead we kind of want this
0:33
stuff in the future to happen behind the
0:35
scenes
0:36
but for now we'll use jupiter to
0:38
understand the code that we're going to
0:40
create and and what's happening under
0:42
the hood how to create you know what
0:44
what is a database engine what is a
0:46
session you know what is a you know
0:48
database driver
0:49
and after we discuss all that have a
0:51
firm understanding i'll upload this
0:55
jupyter notebook to github and also
0:57
upload a separate file that contains
1:00
just the stuff that we need so that way
1:02
in the next video when we're creating
1:04
the database model and tables we'll just
1:06
import this stuff get a connection and
1:10
away we go so hopefully that makes sense
1:12
so with that being said let's go ahead
1:15
and get understanding this sql how can
1:18
you step okay
1:19
so see uh sql alchemy is essentially a
1:23
database wrapper right an orm wrapper or
1:26
object relationship mapper
1:28
we use
1:29
sql alchemy
1:31
and we can use any database with it in
1:33
our case we used postgresql
1:36
so sql alchemy needs you know a database
1:39
driver which in our case is side cop g2
1:43
and that connects to postgres itself
1:47
you know we could you know have other
1:48
drivers connect to other database we
1:50
don't want the underlying database
1:52
architecture to matter we just want to
1:54
be able to use
1:55
sql alchemy code in order to manipulate
1:58
our database and don't care about the
1:59
backend so that's the benefit of using
2:02
sq alchemy
2:04
now whenever we're creating a connection
2:06
using sq alchemy we have really two
2:09
things that we have to worry about we
2:11
need to create an engine
2:13
and an engine is something where we pass
2:15
our database credentials to
2:17
and then once we have an engine we
2:20
create a session from that engine and we
2:22
get multiple sessions per engine if that
2:24
makes sense let's grab those imports
2:28
do chrom
2:30
sq alchemy
2:32
import create
2:34
engine
2:37
from executor alchemy
2:39
dot orm import especially maker and then
2:42
we're going to also import
2:45
some utilities so from sq alchemy
2:49
utils import database
2:52
exists and create database
2:56
essentially if
2:57
we
2:58
you know don't have a database we want
3:00
to check to see if the database exists
3:01
if it doesn't exist we want to create it
3:03
and then finally
3:04
we want to import our you know
3:07
connection settings so i've simply
3:08
created a localsettings.pi file
3:12
and your password should be different
3:13
than password and if you don't know how
3:16
to create a esql database
3:19
we talked about that in the previous
3:21
video i'll show you how to do that and
3:22
create permissions to that database but
3:24
essentially we're going to use that
3:27
python file to store our credentials and
3:29
then we don't include that in our github
3:32
or we use dot you know our dot git
3:34
ignore file to ignore that from pushing
3:36
it to a repository we'll do from
3:39
local settings
3:40
import let's create sql add settings and
3:44
then hit enter so that looks good
3:47
so first things first we'll create our
3:49
engine and then after we have our engine
3:51
we'll create our session so let's create
3:52
a function
3:54
get our engine so def
3:56
get engine
3:57
and pass it to user
3:59
password
4:01
host port and db
4:04
pass it the
4:05
url string
4:07
we'll do
4:09
postgresql
4:11
that's what we're
4:12
using
4:14
user
4:18
password
4:21
the host
4:26
port
4:28
and the database
4:31
and then we'll say if not database
4:34
exists or essentially the database
4:36
doesn't exist
4:39
read it
4:46
and then let's return the engine engine
4:48
equals
4:49
create engine
4:51
url
4:53
full size
4:54
and echo false because we don't want to
4:57
echo every command and then return
5:01
turn ending
5:03
okay that looks good now let's create
5:05
our first engine
5:07
engine equals get
5:09
engine
5:10
settings
5:11
eg user
5:19
okay that looks like it worked and we'll
5:21
receive the engine will look at the
5:24
engine url
5:26
and we can see that it's connected to
5:29
database alpha username alpha here's our
5:31
password and we're connected to
5:32
localhost using postgresql
5:36
awesome now before we create a session
5:41
you know i don't have to pass this
5:42
username and password every single time
5:45
to get engine let's create
5:48
another function here to do that for us
5:50
right so we'll do def
5:52
get engine from settings
5:58
keys
5:59
equal pd user eg
6:02
password
6:04
pg
6:08
which is essentially all of the
6:10
keys that we need right in order to
6:14
create a connection to our database
6:17
now we'll do if not all
6:20
e
6:21
in key so we're saying
6:23
for every key in here
6:24
make sure
6:26
it exists
6:27
in the settings.key so for e in
6:30
settings
6:34
and then if they if it doesn't exist we
6:37
raise an acceptance
6:39
section
6:40
say bad impact file
6:44
and now let's just return our engine we
6:46
don't need an else because it will never
6:48
get that far
6:49
right i mean
6:52
we don't need the else because and if
6:53
it's false it'll just raise the
6:55
exception okay so we'll do get engine
6:58
in fact
6:59
i don't need to do that i'll do this
7:02
copy this
7:05
now return that engine
7:08
and enter
7:10
perfect now what we can do is we'll
7:13
create that session so
7:15
sort of along the same lines we'll do
7:17
define a new function get session
7:22
engine equals get engine from settings
7:27
and then session equals
7:29
section maker we bind an engine
7:32
and then that would return you know a
7:34
session maker and we just run that to
7:36
get an individual session return
7:39
session
7:43
okay perfect and now let's get an actual
7:46
session discussion equals get session
7:51
and we'll just
7:53
double check to see if it exists perfect
7:55
okay so that shows us that we have a
7:59
sql alchemy rm session
8:02
and that's it now you are able to
8:05
connect to postgres using python and sql
8:08
we're going to use this connection in
8:10
the next video where we create a
8:12
database to store crypto tickers with
8:15
minute bar pricing and after that
8:25
in other words there's a lot we've got
8:27
to cover i hope you enjoyed this video i
8:29
hope you're excited about some of the
8:30
things that i just mentioned if you
8:32
found this valuable please like and
8:34
subscribe and i'll see you in the next
8:36
one thanks