Adding a CMS to Your Pyramid Web App Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: Creating tables
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 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.