Python-powered chat apps with Twilio and SendGrid 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
Well, we've got our connection set
0:01
we've got our model, at least for the package, all set up
0:04
we've got our base class.
0:06
Let's go ahead and create the tables.
0:07
Now, notice we've got no database here
0:09
even though over in our db.session
0:13
we've talked to the database.
0:14
We haven't actually asked SQLAlchemy
0:17
to do any interaction with it, so nothing's happened.
0:19
One way we could create the tables
0:21
is we could create a file, create a database
0:23
and open up some database design tools
0:26
and start working with it.
0:27
That would the wrong way.
0:29
We have SQLite.
0:30
We've already defined exactly
0:32
what things are supposed to look like
0:34
and it would have to match this perfectly anyway.
0:37
So, why not let SQLAlchemy create for us?
0:40
And, you'll see it's super easy to do.
0:42
So, you want to use SQLAlchemyBase.
0:44
Remember, this is the base class we created.
0:46
Just import that up above.
0:48
This has a metadata on there.
0:50
And here we can say create_all.
0:54
Now, notice there wasn't intellisense or auto complete here.
0:57
Anyway, some stuff here, but create_all wasn't
0:59
don't worry, it's there.
1:02
So, all we got to is pass it, the engine.
1:05
That's it!
1:06
SQLAlchemy will go and create all of the models
1:10
that it knows about
1:11
it will go create their corresponding tables.
1:13
However, there's an important little caveat
1:15
that's easy to forget.
1:17
All the tables or classes it knows about
1:19
and knows about means this file has been imported
1:24
and here's a class that drives from it.
1:26
So, in order to make sure
1:27
that SQLAlchemy knows about all the types
1:29
we need to make sure that we import this.
1:31
So, because it's only for this one purpose
1:35
let's say from pypi.org.data.package
1:41
import package, like this.
1:44
Now, PyCharm says that has no effect
1:46
you don't need to do that.
1:47
Well, usually true, not this time.
1:51
Let's say, look, we need this to happen
1:52
and normally you also put this at the top of the file
1:55
but I put it right here
1:56
because this is the one and only reason
1:58
we're doing that in this file
1:59
is so that we can actually show it to the SQLAlchemyBase.
2:04
So, first of all, let's run this
2:06
and then I'll show you a little problem here
2:07
and we'll have one more fix to make things
2:09
a little more maintainable and obvious.
2:12
So, notice over here db folder empty.
2:15
We run it, everything starts out well
2:18
and if we ask it to refresh itself, oo, look!
2:22
There's our little database, and better than that
2:25
it even has a database icon.
2:27
It does not have an icon because of the extension
2:30
it has an icon 'cause PyCharm looked in the binary files
2:33
and said that looks like a database I understand.
2:36
So, let's see what's in there.
2:37
Over here we can open up our database tab.
2:39
This only works in Pro version of PyCharm.
2:42
I'll show you an option
2:43
for if you only have the Community in a moment.
2:46
If we open this up, and look!
2:47
It understands it, we can expand it
2:49
and it has stuff going on in here.
2:52
If, if, if, if, this is a big if
2:54
so go over here, and you say new data source, SQLite
2:59
by default, it might say you have to download the drivers
3:03
or maybe it says it down here
3:04
it kind of has moved around over time.
3:07
Apparently, there's an update, so I can go and click mine
3:09
but if you don't go in here and click download the drivers
3:11
PyCharm won't understand this
3:13
so make sure you do this first.
3:16
Cool, now we can test the connection.
3:18
Of course, it looks like it's working fine
3:19
because we already opened it
3:21
and now here we have, check that out!
3:23
There's our packages with our ID which is a string
3:26
create a date, which is the date, time
3:28
all that good stuff, our primary keys, jump to console
3:31
and now, I can say select star from packages, where?
3:37
All through email, homepage, ID, license
3:39
there's all the stuff, right?
3:40
Whatever, I don't need a where clause
3:42
and actually it's not going to be super interesting
3:45
because it's empty.
3:46
Obviously, it's empty, we haven't put anything in there
3:49
but, how cool!
3:50
We've had SQLAlchemy generate our table
3:54
using the schema that we defined over here
3:57
and it's here up in the database tools, looking great right?
4:01
Well, that pretty much rounds it out
4:03
for all that we have to do.
4:04
We do have some improvements
4:06
that we can make on this side
4:07
but that's a pretty awesome start.
4:09
I did say there was another tool that you can use.
4:12
So, if you don't have the Pro version of PyCharm
4:16
you can use a DB Browser for SQLite that's free.
4:19
And if I go over to this here
4:23
I can open up the DB Browser here
4:26
and say open database, and give it this file.
4:31
And check it out, pretty much the same thing.
4:34
I don't know really how good this tool is
4:37
I haven't actually used it for real projects
4:40
but it looks pretty cool, and it definitely gives you
4:42
a decent view into your database.
4:44
So, if you don't have the Pro version of PyCharm
4:46
here's a good option. Alright, so pretty awesome.
4:49
I did say there's one other thing
4:50
I would like to do here just for a little debugging now
4:53
let's just do print connecting to DB with
5:00
just so we see the connection string when it starts up
5:02
so you can see, you know
5:03
a little bit of what is happening here, and that will help.
5:07
And the other thing is, I said that this
5:08
was a little error prone, why is this error prone?
5:10
Well, in real projects you might have 10, 15, 20
5:14
maybe even more of these package type files, right?
5:18
These models.
5:19
And if you add another one, what do you have to do?
5:22
You have to absolutely remember
5:23
to dig into this function, and who knows where it is
5:26
and what you thought about it, and how long?
5:28
And make sure you add an import statement right here
5:30
and if you don't, that table may not get created
5:33
it's going to be a problem.
5:35
So, what I like to do as just a convention
5:37
to make this more obvious
5:38
is create another file over here called __all_models
5:42
and basically, put exactly this line right there.
5:47
And we'll just put a note, and all the others
5:50
all the new ones.
5:52
So, whenever we add a new model
5:54
just put it in this one file.
5:55
It doesn't matter where else this file gets used
5:58
or imported, or whatever, if it's here, it's going to be done.
6:01
So, to me, this makes it a little cleaner
6:03
then I can just go over here
6:04
and just say import __all_models
6:09
and that way, like, this function
6:11
deep down in it's gut, doesn't have to change.
6:13
It should still run the same, super cool.
6:16
Okay, so that's good.
6:17
I think it's a little more cleaned up.
6:19
We've got our print statement
6:20
so we got a little debugging
6:22
and then we've got our all models
6:24
so it makes it easier to add new ones.