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