Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Concept: SQLAlchemy architecture

Login or purchase this course to watch this video and the rest of the course contents.
0:02 Let's talk briefly about the architecture of SQLAlchemy.
0:05 We'll see that it's made up into three basic building blocks, three layers.
0:09 At the very foundation we have the plain Python DBAPI.
0:14 This is the way you access databases built into Python itself,
0:17 and if you work in this level, you are not using SQLAlchemy
0:20 and you are doing dialect-specific queries against a particular database type,
0:26 so if you are talking to Oracle,
0:28 you are using Oracle-flavored SQL to talk directly to it
0:32 and you are getting back just rows of columns that you are working with.
0:35 But if we bring SQLAlchemy into the picture, we can look at the core layer,
0:39 and that's the lowest level that we can work at in SQLAlchemy.
0:42 In here we have this really important concept called the engine.
0:46 It's the engine's job to abstract the way of the database
0:49 and take care of many things for us.
0:51 The engine handles connection pulling and so you'll see that it's super important
0:55 that there is one and only one instance of an engine per connection string.
1:00 if you are talking to multiple databases, you might have multiple engines for that purpose,
1:05 but for any given database you want to access withing your process,
1:08 you are going to have a singleton instance of this engine.
1:11 In its job it's to manage connection pulling and speak
1:13 the dialect of the underlying database.
1:16 So you give it a connection string and part of that connection string
1:19 is the type of database it's going to be talking to, is this SQL server,
1:23 is this Oracle, is this MySQL? And that will adjust the dialect correctly,
1:28 and use underlying drivers to talk to that particular database.
1:32 We also have a SQL expression language, which is a little specific to the core,
1:36 and we can define schemas and types here.
1:39 Where we probably want to spend most of our time is at the ORM.
1:43 This is where you map classes, and objects into databases.
1:47 I think the best way, most efficient way as programmers for us to work,
1:51 not necessarily performance-efficient but mentally-cognitively-load-efficient is
1:56 to work in objects and hierarchies, not in normalized database schemas, right.
2:03 So this lets us come up with classes, decorate them or define them
2:06 in a way that SQLAlchemy knows how to translate them to and from the database,
2:10 and then we just work in these classes, we create the classes
2:13 and we can insert them into the database.
2:16 When we do queries, it's with regards to the fields or attributes of these classes and so on.
2:21 This gives you just a sense of the various pieces
2:24 at play when we are working with SQLAlchemy.