Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Demo: Part 3 Creating the tables

Login or purchase this course to watch this video and the rest of the course contents.
0:01 OK, so how do we create the schema in the database from this?
0:04 Well, what we are going to do is we are going to back to this session factory
0:07 and pick up with this engine here,
0:09 so it turns out that our SqlAlchemyBase class here has a metadata property.
0:15 Now, metadata does not appear in PyCharm's IntelliSense or autocomplete,
0:20 which is kind of annoying but just run with it.
0:22 Now on here we have a method called create_all.
0:25 and this will go to the database and actually inspect it and if it sees that
0:30 there is a type it's supposed to manage
0:32 that's not in there or there is no database at all,
0:35 it will create the database, create the tables, set up the relationships;
0:38 be careful, once this is done, it will not update existing tables
0:43 it only creates new ones if they are missing.
0:46 So, there are limitations here but it is quite cool how this gets you started.
0:49 If I run it like this, it's going to crash.
0:52 And the reason it is going to crash is it says "OK I know enough
0:57 about the models to create the database,
0:59 but I don't even know what database you want me to create
1:02 so how am I supposed to do that?". The way we do that is we pass the engine.
1:05 And this is one of the places where this implicit relationship gets wired together,
1:11 so we don't actually have to track the engine,
1:13 there is one other pace that we are going to get to later.
1:16 Let's go ahead and call this, we can come down here
1:19 and we'll do it in our init, we have init routing,
1:21 let's go and add one more init_db let's say, we're going to give it the config.
1:27 So for now we are just going to put it like this,
1:31 so we are going to come over here and we want to call this
1:34 global_init with the db_file, well, what is the db_file?
1:37 Let's make a place where this data is going to go,
1:40 OK, so we'll make another folder, remember,
1:43 maybe a full-featured server this would just...
1:46 you wouldn't have to have a file location, you would just say go to that server,
1:50 that server is configured to store data, but in SQLite there is,
1:53 it's only file base, right, so we have to put it somewhere,
1:56 so I am going to say right here in this db folder we want to put it there,
2:00 so how do we get that? Well, a few simple tricks.
2:03 We can come over here and we can "import OS",
2:06 which gives us access to the path functionality.
2:09 So we can go and say "I would like to know where this folder is", right,
2:12 that's out package, so we can say top_folder let's say,
2:16 use OS.path.dirname, so dirname is just the directory stripping the filename,
2:24 and what we can give it here is
2:27 let's do a quick import up here... "import blue_yellow_app", right,
2:31 so it seems like why would you do that but there is a __file__ right here
2:36 and this is going to be the top level, this is going to basically be this folder,
2:40 technically it should be like this, right, but when we grab the dirname
2:44 it's going to be this folder, and then, we can have rel_folder,
2:49 so if we know that we're in here, it's going to be db, so it's going to be db
2:54 and then what do we want to call it, well, we just make up a name,
2:57 let's call it blue_yellow.sqlite, extension doesn't matter,
3:03 it could be called anything but it will help us understand oh that's our sqlite file
3:06 and then finally we'll say db_file is os.path.join(top_folder, rel_folder).
3:16 so this should be passed along here now,
3:18 this information here, that's coming along here,
3:22 we probably want to pass that in the configuration file,
3:26 maybe we want something different for production,
3:28 so from the production.ini to development.ini.
3:32 So we'll come back and deal with that in a little bit, but,
3:35 let's go and run this and see what happens, we are going to call,
3:38 when we say run it's going to fire main, it's going to call init_db,
3:41 over here that's going to look through the relative location where our stuff is
3:46 and create a path over here at this folder it's going to go call global_init,
3:50 global_init is going to create a connection string based on that file,
3:54 let's go and do a little print, "connecting to db with conn string"
4:03 and we'll just put it here, like so. So we should see this come out,
4:09 and then we are going to call engine.create_all
4:12 and over here, that should actually, because there is no database file,
4:15 that should actually create the database itself, well,
4:18 actually this creates the engine, create_all is going to do that.
4:21 Now, this is not going to be as cool as you think,
4:24 the first thought would be of course if this works, like, as you can dream,
4:29 right, this would create a database which has albums and tracks.
4:33 Don't think that's going to work, if something could have triggered it
4:37 in a way that will make this work,
4:39 but let's try I am pretty sure it's going to be an empty database
4:41 and then I'll explain why.
4:43 So it runs, it didn't crash, that's cool, here is the SQLite path it looks like,
4:47 I have a pretty long path here don't I, but blue_yellow/db, blue_yellow.sqlite,
4:52 that's cool and if we go over here and refresh it, we have a blue_yellow.sqlite.
4:59 Now this database is empty. How do I know?
5:02 Because that right there is a white file, not a database-looking thing.
5:07 Why is this empty? Let me prove to you it's empty,
5:09 you can actually look at this databases in PyCharm,
5:12 so we come over here, you may need to download the drivers for the serial SQLite,
5:19 since I think I've already done it, let's see we'll find out.
5:22 Put it over here, no, it doesn't take because it doesn't know what it is,
5:27 come over here, notice, "no driver files found", so let me install that really quick.
5:31 OK, browse to the blue_yellow.sqlite and it figured it all out,
5:36 so if I hit OK, you should see it will pull it up, however, it's empty, nothing happens.
5:41 Like I said, there is no data in this database, so what's going on here?
5:45 Let's put this away, we'll come back to it when it's got something to look at.
5:49 This is a little bit tricky, it's easy to solve
5:51 but you just got to get used to it. When we run this line here,
5:54 the SqlAlchemyBase basically says "I would like to...
5:57 I will look to all the classes that derive from me,
6:01 and then I will map them to the database"
6:04 But in Python, the way that a class gets defined or created it's not a compiled time,
6:09 it's when the module gets loaded.
6:12 Well nowhere in the application, prior to this code being run,
6:16 were tracks or albums loaded into memory.
6:21 A lot of times what I find I have to do in order to get this to work just right,
6:25 if I am not cramming it all into one file, which I do not recommend,
6:28 is to just do an empty sort of import up here,
6:31 so blue_yellow.data.album and then do one for track.
6:35 Now PyCharm is going to whine, because we are not doing anything with these
6:38 and you can just tell it you can say "disable this for this particular statement,
6:43 like, don't complain to me about this, I know what I am doing,
6:46 I have to do this or it's not working", so look at the color of this icon here.
6:50 Now if I run it again, because those tables don't exist,
6:53 it should discover these and create them.
6:56 Let's find out. OK, so it didn't work, why didn't it work, it says you know,
7:00 there is no way we are going to be able to do queries against this album,
7:03 let's go and fix that first.
7:05 So over here, we've got our id, and it's easy to make it a primary key,
7:08 you just say primary_key=True, that's not enough really for it
7:12 to be like auto-generating or have a default value, or anything like that,
7:15 so we can also say autoincrement=True.
7:18 And so this creates the simplest possible primary key for a database
7:22 and auto-incrementing integer, that works great,
7:26 similarly this one is going to fail, if we don't give it the same features.
7:30 Let's try again.
7:32 Ooo, now it's running, let's go over here and do a synchronize again,
7:35 notice the icon changed, now we have a database looking thing, oh that is sweet,
7:40 so now we can go over here and refresh this,
7:43 and there is a little chevron that opens up and look at that,
7:46 we have data or not really data, we have schema in our database, right,
7:51 we have albums and we have tracks, how cool is that?
7:55 so what did we do? We came over here, I'll put this away for a moment,
7:59 we come over here and we after creating our classes
8:03 and as you saw required to give them a primary key,
8:05 we basically just called SqlAlchemyBase.metadata.create_all
8:12 and because these had been imported,
8:15 the SqlAlchemyBase now knows about our album,
8:18 now knows about track and it was able to create
8:20 the schema because it didn't exist,
8:22 it knew it needed it and we asked it to do so.