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