RESTful and HTTP APIs in Pyramid Transcripts
Chapter: Adding a database backend with SQLAlchemy
Lecture: Introducing SQLAlchemy

Login or purchase this course to watch this video and the rest of the course contents.
0:01 To this point, we've worked with something that acted kind of like a database,
0:04 we could query it and it would give us things like give me this car by this id
0:08 we could create new ones that would be there, but that would all vanish
0:12 when we would restart and that's just because we were basically
0:14 using dictionaries in memory as if they were databases.
0:16 It's time to get real about databases and actually create a real database
0:21 that has persistence, potentially cross process, access, things like that.
0:27 So we're going to look at using sql ite and sqlalchemy
0:31 to have a persistent back end for our web API.
0:35 Sqlalchemy is open source, it's been around since about 2005 if I recall correctly,
0:40 and it's certainly one of the most powerful, flexible
0:45 and popular ORMs or object relational mapper for Python,
0:49 so very, very popular especially if you're not using Django ORM,
0:53 then it's very likely that if you're talking to
0:55 relational databases, you're using sqlalchemy.
0:58 So, that's what we're going to use here in this course
1:01 just beware this is not a comprehensive course on sqlalchemy,
1:04 we're just going to cover just enough sqlalchemy so that you can follow along,
1:08 just enough sqlalchemy to basically have
1:10 cars and users in our application in the end.
1:15 So we're not doing much with sqlalchemy, but certainly this is a good jumpstart.
1:19 So let's look at the architecture really quick;
1:21 now in Python, we have this thing called the db api
1:25 and this is just the built in way, the built in API for talking to databases
1:30 and it's consistent across different types of databases
1:34 like sqlalchemy, microsoft sql server, oracle, mysql, all these things
1:38 can be talked to with the right implementation of db api.
1:42 Now of course, sqlalchemy itself is built upon db api
1:46 and talks to many different types of databases,
1:49 we'll see that sqlalchemy is made up of two parts
1:51 there's what's called the sqlalchemy core
1:54 which manages schemas, tables, there's this thing called engine
1:57 that does connection pulling and translates the queries that you sent to it,
2:01 into the dialect of the database you're talking to, like for example,
2:05 microsoft sql server and oracle have
2:08 different ways to express parameterized queries,
2:10 when you are in sqlalchemy, you don't care,
2:12 it translates that into the various styles of sql
2:15 that each of these database engines use.
2:18 And so that's really helpful, and you can work just in the core layer
2:21 like an example of a website that just uses the core is Reddit,
2:24 they just use the core, they don't use all the other
2:27 higher order features of sqlalchemy, but we are going to;
2:30 we're going to use the ORM, we're going to create a car object
2:34 and a user object and model what we're doing in our application
2:38 in sqlalchemy and it will automatically create the tables from this,
2:41 it will automatically translate queries in terms of the objects to and from sq l,
2:45 the language, to the particular dialect of the database that we're talking to.
2:50 So there's these three levels that you'll be working at
2:54 and we're going to pretty much focus on the ORM part,
2:57 but you'll see we'll have to work in the core layer a little bit to get things set up.