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