Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: Creating tables
0:00 With our connection and engine set up it's time to create the tables. One way the wrong way would be to go to the database
0:09 and actually start using the database design and modeling tools to just go and model the stuff. Because we've already got that right here.
0:18 In fact, we need those to match exactly. So it's better if we just let SQLAlchemy do it. So, it's actually super easy here.
0:26 Let's go down after we've done this bit and if we can import our SQLAlchemyBase which we can, it has metadata right there. And on the metadata
0:39 there's no help from PyCharm here but it does exist when we say, create_all and pass the engine. So, if the database doesn't exist
0:47 whatever connection string info we gave it here it will create it and then it's going to create the tables and the primary keys and the relationships
0:57 and all that stuff based on all of the various types that derive from this. There's one really big caveat that's easy to miss here.
1:06 By the time line 27 is run every single class that derives from it must have already been imported, so SQLAlchemy has seen it.
1:17 If you import those afterwards, too late those tables are not getting created. So I'm pretty sure right now, the way this is working
1:25 is it's going to be failing So we need to do something kind of like this. We need to go over here and say from pypi.data.packages
1:36 import package, release and user, and whatever. Alright, so we got to do this for all of 'em. So we got to do it like this.
1:46 Now that, expanding out of all the ones you need turns out to be super error prone so let's add one little trick here. And add one more file.
1:55 Now let's call this all_models kay, and over here I'll say, well exactly that. So we're just going to list everyone here from packages, import package
2:10 and we define release and we're going to import that and we define languages, we're going to import that and let's tell PyCharm
2:15 "Hold on, no this, I know it looks like this does nothing," "but for the moment, we need to have this here."
2:21 "It actually has an effect that you don't realize." "So thanks for the help, but not this time."
2:25 And then over here, we can just make this a little simpler. We can just import it like that, and we'll put that little, you know
2:36 "PyCharm, please ignore this." So that way, we can just model it right here and it doesn't matter where where we do this import
2:43 but that's a really not obvious thing but you have to basically, before you run this import every single model or it's not going to work.
2:50 However, with this in place and if we call this function, it should work. So let's go to our __init__ down here we're going to include the net routing
3:00 let's have a init DB config. And we'll come down here and we'll just put that at the end. And this should be pretty simple so we'll say, "DB session,"
3:14 import that and we'll just say, "global_init," and we'll have to give it a file. What file do we give it? Well, let's make a little folder
3:22 'cause this is SQLite, we'll just make a little folder called DB and we'll put it in here, okay? If we were using, say PostCraft, like I said
3:30 that we'd just give it a regular connection string like here's the server. But we're going to need this, so how do we do that? Let's say, "DB folder."
3:40 We'll use the os module, yeah? So come over here and say, "path.absolutepath" "of some stuff, we're going to say, os.path.join"
3:50 And what are we going to put in it? We'll say, "os.path.dirname of wherever we are." So, we're going to be in the PyPI folder just __init__.
4:02 So we want to be in the PyPI directory and then down here, we're going to say "The next thing we want is DB."
4:09 And then the next thing we want is pypi.bin or let's call SQLite, how's that? Want that right there. And we'll just pass DB let's call that file.
4:23 'Cause it's not just the folder, but it's the file. Great, now if we run it we're not using the config for now so let's put an underscore
4:32 probably need it later if we say "Store different DB settings in production" "and development" here. But now we don't need it.
4:40 Alright, so when we run this we should see it print out a path that's located here and actually create the database files.
4:48 You'll notice there's no little expando chevron thing here. There's no files in there. Let's run it. Well it didn't crash, that's a good start.
4:58 It's creating this, you can see way down there. Now, if I go over here say "Synchronize." Oh look, look look look what we got! How cool is that?
5:11 Okay, so, it's really nice that this is here how do we know what's in it? Did it actually create that packages table or did it not?
5:18 Well, in PyCharm that's easy to answer. So we come over here and we can drag and drop this and it'll open up if, if if if it's a big if.
5:28 So you got to go here once on your machine and go "datasource SQLite serial" and then notice here it has driver.
5:37 It looks like it can update the driver files but sometimes it'll say you have to download the driver files 'cause there's no file.
5:43 So we can download 'em again just to show you what that looks like. Alright, apparently that's updated. That's cool.
5:52 We can come down here and expand this out in the schema and main and look! There's our packages. Primary key with an index ID. Right, there's that.
6:01 We got a create date, which is a datetime. Our summary which is varchar. Our license which is varchar. There's a lot of varchar happenin' around here.
6:08 But there is our database with our one table and notice it's named packages plural not upper case P package singular. You go over here, say "Select,"
6:19 "star from Packages where author email" look at that, beautiful. Beautiful beautiful. If for some reason you don't have the pro version of PyCharm
6:29 you can use this app DB Browser for SQLite. Come over here and find the file. And you get the same thing, right? So here you go, it's not.
6:45 I dunno, maybe it's nicer or maybe it's not quite as nice. I haven't used it that much but this is also another thing you can get for free.
6:51 Just google DB Browser for SQLite. Either way, our database is all up and running. Now this is only the beginning of course
6:59 but we've created our database and we've got a lot of stuff in place. Really all we have left to do is just to expand out all the other various tables
7:08 we're going to work with.