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