Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: 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
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.