Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Introduction to SQLAlchemy
Login or
purchase this course
to watch this video and the rest of the course contents.
0:02
Are you excited? I know I am, we've been juggling around and working around the fact we don't really have a database
0:10
or data access to make our dynamic websites, so far. I've been trying to tech you the web foundations and now that we are through that section
0:18
it's time to finally have truly data-driven web applications. We are going to use SQLAlchemy. SQLAlchemy is the most flexible, powerful,
0:28
and popular data access toolkit for Python. Here you can see at SQLAlchemy.org you can find it,
0:34
of course, "pip install SQLAlchemy" is how we are going to get started. Let's look at a few reasons why in addition to being powerful, fast and popular
0:42
we might choose SQLAlchemy, a little more nuanced look there. We'll see that SQLAlchemy is more than just an ORM,
0:50
there is several layers to it, and we'll talk about these layers in a moment, so there is a low level core that abstracts
0:57
the differences between different databases, like talking to Microsoft SQL server versus Oracle versus MySql,
1:04
these all have a slightly different syntaxes and you don't have to care or know about those, you could easily switch between them,
1:10
but the core's otherwise similar to something where you express your queries in exactly the scheme of the database itself.
1:19
So you can use this, there is no ORM required, although we are going to be using it in our apps for the most part.
1:25
It's mature and it's very high-performance. Mike Bayer, the creator of SQLAlchemy, has done a lot of work to highly optimize SQLAlchemy.
1:34
It's DBA-approved, so for whatever reason you have a DBA who doesn't like ORMs or automatic query generation or SQL generation,
1:44
you can actually swap out with what is generated by SQLAlchemy with hand-written statements. It does have a very powerful and easy to use ORM
1:55
while the ability to work without an ORM for certain queries and situations is great, you'll see, my philosophy is 80, 90% of the time,
2:03
the ORM saves you so much time and energy and provides flexibility without getting in your way that you can do
2:12
just the raw SQL or core level queries just for the few ones that don't quite fit with what you are doing.
2:19
SQLAlchemy uses the "unit of work" design pattern, this is as opposed to "active record", and this is I think
2:25
a slightly more sophisticated data access design pattern. What that means is you begin unit of work, you make some queries,
2:32
make some changes, make some updates, deletes maybe and you do all of your database work and then you commit the unit of work
2:39
and all those changes are either applied or none of them are applied all in one batch, as opposed to inserting individual records
2:46
and hoping that everything hangs together. It supports many databases, SQLlite, that's the one we are going to be using,
2:54
MySQL, Microsoft SQL, Oracle and so on. It also supports something called Eager-loading, as well as lazy loading.
3:03
So Eager-loading is the ability to say to the query engine "hey, I am actually going to query this collection
3:09
but I am going to do traversal of a relationship over to these two others"
3:13
and instead of doing that lazily, which is a really big problem for performance, in certain circumstances, you can tell it to do that all in one shot
3:21
as one sort of three way join and you have only one database access instead of using lazy loading, which is really cool.
3:28
So SQLAlchemy is a great toolkit, it's what I use on all the Talk Python websites, it's what is driving this website that you are looking at right now.