Building Data-Driven Web Apps with Flask and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: Connecting to our 'database'
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Before we can interact with our packages
0:02
and query any or save them to the database
0:04
or anything like that, we're going to need to
0:06
well, connect to the database.
0:09
And a lot of the connections and interactions
0:11
with the database in SQLAlchemy
0:14
they operate around this concept
0:15
of the unit of work.
0:17
Construct inside SQLAlchemy that represents the unit of work
0:20
is called a session
0:21
and it internally manages the connection.
0:24
So with that in mind, let's go and add a new Python file
0:27
called db_session.py.
0:30
So in this file, we're going to get all the stuff set up
0:33
so that you can ask it for one of these sessions
0:35
and commit or rollback the session and so on.
0:37
So we need to create two basic things.
0:40
We need a factory, and I'll just say None for the moment
0:44
and we need an engine.
0:45
Now the engine I don't believe we need to share
0:47
but this factory we're going to need
0:49
to somehow keep this around right
0:51
this is kind of we'll use the engine
0:53
to get the factory and so on.
0:54
So let's go and let's create a little function here called
0:58
global_init(db_file: str) and we're going to use SQLite.
1:02
It's the simplest of all the databases
1:04
that are actual relational, you don't have
1:06
to set up a server, it just works with a file
1:08
but it's a proper relational database.
1:10
The way we do that is we pass in a db_file
1:13
which is a string.
1:15
So what we want to do is work with this
1:17
and see if, this has been called before
1:19
we don't need to run it twice
1:21
so we'll do something like this.
1:23
We'll say first let's just make sure
1:24
that's global factory - we'll say
1:25
if factory: return.
1:27
No need to call it twice, right?
1:29
But, if it hasn't been called
1:31
let's do maybe some validation here.
1:33
We'll say if not db_file or not db_file.strip()
1:42
it'll raise some kind of exception here, like
1:48
right, something pretty obvious.
1:49
You have to pass as a database file
1:51
otherwise we can't work with it.
1:53
Then we're going to get an engine here.
1:55
The way we get the engine is from SQLAlchemy
1:57
so we're going to have to import sqlalchemy.
2:00
Maybe we'll stick with this as sa
2:03
so here we just say sa.create_engine.
2:05
Super simple. Notice the signature here though
2:09
*args, **kwargs". I utterly hate this pattern
2:12
it means, you can pass anything and we're not going to help you
2:15
or tell you what could possibly go in there.
2:17
In fact, there is a known set of things you can pass
2:20
in here, so they should just be keyword arguments.
2:23
Well, anyway, that's the way it goes.
2:25
So, we're going to pass a couple of things.
2:27
We need to come up with a connection string
2:31
and when you're working with SQLAlchemy
2:33
what you do is you specify the database schema
2:36
or the type of database that's part
2:37
of the connection string.
2:38
So we'll say sqlite:///
2:41
and then we just add on the DB file like this.
2:43
Maybe just to be safe we'll say "strip", just in case.
2:48
That seems like that's a pretty good one
2:50
and then here we'll just pass the connection string
2:51
as a positional parameter, and then we
2:53
also may want to set this, so I'll go ahead
2:55
and like prime the pump for you.
2:57
I'll say "echo=false".
3:00
If you want to see what SQLAlchemy is doing
3:03
what queries it's sending, what table create statements
3:06
it's sending, stuff like that
3:08
you set this to "true", all the SQL commands sent
3:11
to the database are also echoed out
3:13
both just standard out, and standard error, I believe.
3:18
But I'm not going to show that, but just having this here
3:20
so you know you can flip that and really see what's going
3:21
on here. So we're going to set the factory here
3:24
but what we need is we actually need
3:26
import sqlalchemy.orm as orm
3:31
So we come in here and we say orm.sessionmaker
3:35
session... maker.
3:37
So this is a little bit funky, but you call this function
3:40
and it creates a callable which itself
3:43
when you call it, will create these units of work
3:46
or these sessions.
3:48
So we got this little bit of a funky thing going
3:50
on here, but then we also come in here
3:52
and we say "bind=engine".
3:55
So when the session is created
3:58
it's going to use the engine which knows the database type
4:01
and the database connection, and all that kind of stuff.
4:04
And that's pretty much it!
4:05
We're going to use this factory after everything's
4:08
been initialized.
4:09
We're actually going to do a couple of iterations
4:10
on this file to make it a little bit better and better
4:12
as we go, but let's not get ahead of ourselves.
4:15
I think this pretty much does it
4:17
so let's go ahead and call this over here.
4:21
Let's go - here we are, register_blueprints
4:23
like, setup_db(), let's have just a function down here...
4:29
So let's go ahead and create a data folder
4:33
not a data data, but a DB folder.
4:35
In here is where we're going to put our database file.
4:39
So what we need to do is work with the OS module
4:42
and we're going to actually figure out where this is.
4:44
So we want to say "durname", "pathoutdurname"
4:48
and we're going to give it the dunder file for apps.
4:51
So that's going to be the directory that we're working in
4:55
so right now we're this one, and then we need
4:57
to join it with DB and some file name.
5:00
Let's say "DBFile" it's going to be "OS.path.join"
5:06
this directory with "db" with some database file.
5:10
Let's just call it "pypi.sqlite".
5:14
The extension doesn't matter but, you know
5:15
maybe it'll kind of tell you what's going on there.
5:18
And then we can just go up here.
5:22
import pypi_org.data.db_session as db_session
5:26
and come down here and just call our global_init().
5:30
Pass the db_file, and that's it
5:32
we should be golden.
5:33
Let's go ahead and run it just to make sure
5:34
everything's hanging together.
5:36
Ha ha, it did, it worked! Off it goes.
5:39
There it is up and running.
5:41
Super super cool. Did anything happen here?
5:43
Probably not yet, no, nothing yet.
5:46
But pretty soon when we ask SQLAlchemy
5:49
to do something, like even just verify
5:51
that the tables are there, you'll see SQLite
5:54
will automatically create a file there for us.
5:57
Okay, great, looks like our database connection is
5:59
all set up and ready to go.
