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.