Python for .NET Developers Transcripts
Chapter: Database access and ORMs in Python
Lecture: Creating the tables with SQLAlchemy

Login or purchase this course to watch this video and the rest of the course contents.
0:00 So we can initialize our connection in our session factory which is great. The next thing that we want to do is create tables.
0:08 Now, you can't call create tables until you can connect to the data base. So we'll say, if not __engine, or __factory I mean we know it's the engine
0:18 raise exception you must call global_init first so you haven't initialized the connection so you can't call this function.
0:26 C# we have throw, in Python we have raise. The next thing we need to do is we need to show SQLAlchemy all of the classes that derive from it.
0:37 It's a little bit weird but that's how SQLAlchemy knows what tables it should create so we have to say
0:41 something like this. from import Guitar Now PyCharm's going to say that does nothing cause we're not going to actually work with it
0:53 well I want to tell it no, no, no this time I actually really need this here please don't change that.
0:58 We're going to make this better in a sec but just roll with it for a moment. We also need this thing right here.
1:06 And on here it's super easy for us to go and create one of these tables. We'll just go to our base class and there's a metadata
1:14 and we can say create all, and pass the __engine. Again, for some reason, this doesn't come up in auto complete but it's fine
1:21 it's there, it's going to work. So let's go ahead and actually try to run this. So let's go up here. Let's import like this.
1:33 Down here, before we call let's say global_init, this is going to be guitary.sqlite, the file extension
1:41 doesn't actually matter but when I look at it I want to know hey this is a SQLite database and we can say here create_tables.
1:48 If the tables already exist there's no harm, it's just like okay well nothing to do we'll keep on keep on going. So watch over here.
1:56 Right now there's nothing in that folder but if I run it, first of all hooray it didn't crash that's cool, secondly now we have this guitary.sqlite
2:08 which is pretty cool, huh? Notice it has a data base icon as well. This is not because of the SQLite extension the extension doesn't matter
2:16 this actually is because we can come over here and throw this in and expando it out and it knows that there's this table.
2:26 It actually looked at the file and said Oh that looks like a SQLite file. In order for that to work you have to do one thing really quick.
2:32 You have to go over here, I think you're going to try to add a data source the very first time. It only happens once when you install
2:38 PyCharm on your machine which is why I don't do it often. You see SQLite and then right there where it says jdbc, SQLite, et cetera, we could test it.
2:47 It looks like it works, when you first run this you'll see that it'll have a button here. It says you must download the drivers
2:53 you got to click that button until you do that it won't identify this file and it won't be able to open it here so don't forget to do that.
3:00 But once you do PyCharm has an insane number of cool cool features around the database. So like for example, watch this.
3:06 If I just wrote plain queries like query = select * from look at this, guitars, that's auto complete within a string. What if I go over here and I say
3:21 What I kind of select it's like name, price, this just blows my mind it is so incredibly cool that we get
3:28 both color highlighting and auto complete within strings within Python. Anyway, you get a bunch of benefits by doing that.
3:35 We don't need that auto complete when we're doing SQLAlchemy but nonetheless, here we have our tables created, our connections set up
3:44 and then our tables created by just going base class metadata create all. Remember it's super important you show it by importing
3:51 like loading the guitar stuff before you call that line or it won't create the table.

Talk Python's Mastodon Michael Kennedy's Mastodon