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