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 guitary.data.guitar 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 app.run 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.