Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Appendix: 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 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.