Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Introduction to SQLAlchemy
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.