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