Building data-driven web apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: Creating tables

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