Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Demo: Part 1 Creating the engine and base class
Login or
purchase this course
to watch this video and the rest of the course contents.
0:02
Are you ready to write some code? Start talking to the database? I am. This is going to be awesome.
0:08
Over the next couple of videos, we are going to build up everything that we need to talk to a real proper database.
0:15
So I've created this folder called data, and you can see it's empty, it just has this placeholder.txt in it right now.
0:21
We are going to put all of our files and classes here to work with SQLAlchemy,
0:25
so just like controllers are where we do all of our processing of our request,
0:30
data is going to be our database stuff, and templates of course are view. So just one more component or thing here on the left.
0:39
We are going to start with a few core pieces of SQLAlchemy and then we are going to model the albums and the tracks in our website.
0:45
We'll begin with this thing I am going to call modelbase. Now, a lot of times people put everything all into one file here
0:51
and there are some advantages to that, but I personally find it much easier as your application grows larger and larger
0:57
and more complex to have stuff spread across multiple files so you know where to go look for the relevant data,
1:04
imagine you've got a database with 50 tables, so 50 classes potentially, you don't want those all in one huge file,
1:11
you want those broken into little pieces, so you can do a quick little find in PyCharm or wherever, and just jump into just the right file.
1:17
So we are going to start with this thing called a modelbase, we are going to come over here now, and as you can imagine,
1:21
we are going to start by importing SQLAlchemy. This thing that we are going to work with,
1:27
this thing called the declarative base, is actually a base class that is shared across all of the classes we want to map into our database,
1:35
so basically by virtue of our classes we create deriving from this class we are about to state here, this will teach SQLAlchemy
1:43
about their schemas and their their relationships. We are going to import SQLAlchemy and here we have ext and then there is a declarative section.
1:51
And I'll just shorten it like that so you don't have to type the whole thing out, Now, what we are going to do is we are going to create this class
1:56
and I am going to call it SqlAlchemyBase, you can name it whatever you want,
2:00
you probably should uppercase the first name because it is technically a class,
2:04
we are going to go here to this and we are going to say declarative_base; now declarative base will actually create a type here like so,
2:13
this is all we have to do in order to create this base class. And, like I said, you can mix it together with other files,
2:19
you'll find that you need to include this in a lot of places and it's easy to get circular references and stuff
2:23
so I am just going to put it in its own file right here. Now, PyCharm is warning us that if we look over here, in our requirements,
2:32
we are not listing SQLAlchemy as a requirement, so that means when we move to a new server, when we say "Python setup.py develop or install",
2:41
it's not going to include SQLAlchemy and that is going to be bad, so we can let PyCharm just add this requirement for us.
2:48
The only thing that is mildly unfortunate, is that it doesn't order or alphabetize and it's not misspelled, PyCharm, it's how it spells SQLAlchemy.
2:58
OK, so first thing we needed is this will be our single base class. Now, basically this is mapped to a particular database,
3:06
so if you are talking to multiple databases, like one thing I do on Talk Python is I've got a core database and then I have the analytics database,
3:13
because the analytics data is huge, it's like near gigabyte. The core data is much smaller and I kind of want to keep those things separate.
3:21
So you would have two separate bases, one for the core of a database and one for the classes that map into the analytics database.
3:29
But our world here, we are just going to have one for the app we're building. Now the next thing we are going to need to work with is this class
3:36
whose job is to basically manage the connection, create the classes, and the database if we need to, as well as process the unit of work stuff
3:46
that we are going to get to later, we are not going to talk about it yet but let me go ahead and get the foundations of that class ready,
3:52
so we are going to add another type here, called DbSessionFactory and this is going to be a class and actually let me rename that to... like so,
4:01
OK, so we've got our class here, and for a moment, we are just going to do a pass, now this class is going to need
4:09
to work with that model base that we are using above, so let's do this, let's say "from", remember, we can't just say "modelbase",
4:16
this is the package, so we've got to say the full package name, we will do a relative import,
4:24
so we are going to use our SQLAlchemy base here in just a moment. We are going to need to pass data over to this class at startup,
4:32
so for example, if we put into our configuration file, the name and the location or even type of database we want to talk to,
4:38
we want to pass that information on, so I am going to create a function that can be called during app startup.
4:45
We'll just call this "global init" or something like that and it's going to take a single parameter, which is going to be the db_file.
4:53
Now we are going to use SQLite for this web application, that is just an embedded database that is file-based,
5:01
it's not a proper server, like MySql or SQL Server, something like this, all we have to provide to create the database,
5:09
the connection if you will, is the db file. OK, so maybe we want to throw in some error handling
5:14
and we could say something like this: "if not db_file: raise Exception ('you must specify database')".
5:23
OK, so after we get started we got some validated data here, the first thing we need to do to talk to the database is to create a connection string.
5:33
SQLAlchemy connection strings are like regular connection strings but they have a little extra scheme in the front and that scheme tells SQLAlchemy
5:42
what dialect, what database it's going to be talking to, instead of just having the file like you would in SQLite
5:48
we'll say "sqlite:///" and then the database file. So this bit right here tells SQLAlchemy we are using SQLite
5:58
and then the remaining part is actually passed along directly to SQLite itself, so this is the connection string.
6:06
And you saw in the architecture section that the thing that manages connections and dialects is the thing called an engine.
6:13
So we are going to create an engine here and for that we are going to need to get a hold of SQLAlchemy.
6:18
So again, we'll import SQLAlchemy and down here we'll just say sqlalchemy.create_engine().
6:25
One of the things that bugs me a little bit about SQLAlchmey is so many of the functions and methods take *args, **kwargs
6:34
and it's like what the heck is supposed to go in here? Right. Well, there are many things we can put in here,
6:39
one of them is the connection string, like so. Remember this is one and only one instance of this engine
6:45
we are supposed to create per database connection string. You might think that we would need to store it or something,
6:51
but actually the things we are going to store have implicit references to the engine,
6:55
so we are not going to have to do something special to hang on to it but realize this is the one and only time we are creating this engine here.
7:02
Alright, so the engine is ready to roll, the next thing we need to work with is the SQLAlchemy base connect it to the engine,
7:10
so that it knows how to create the tables and things like that, But let's put this part on pause for a moment and let's go create some classes
7:17
so we actually have something to put or model in our database.