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