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. We'll see that it's made up into three basic building blocks, three layers.
0:10 At the very foundation we have the plain Python DBAPI. This is the way you access databases built into Python itself,
0:18 and if you work in this level, you are not using SQLAlchemy and you are doing dialect-specific queries against a particular database type,
0:27 so if you are talking to Oracle, you are using Oracle-flavored SQL to talk directly to it
0:33 and you are getting back just rows of columns that you are working with. But if we bring SQLAlchemy into the picture, we can look at the core layer,
0:40 and that's the lowest level that we can work at in SQLAlchemy. In here we have this really important concept called the engine.
0:47 It's the engine's job to abstract the way of the database and take care of many things for us.
0:52 The engine handles connection pulling and so you'll see that it's super important
0:56 that there is one and only one instance of an engine per connection string.
1:01 if you are talking to multiple databases, you might have multiple engines for that purpose,
1:06 but for any given database you want to access withing your process, you are going to have a singleton instance of this engine.
1:12 In its job it's to manage connection pulling and speak the dialect of the underlying database.
1:17 So you give it a connection string and part of that connection string is the type of database it's going to be talking to, is this SQL server,
1:24 is this Oracle, is this MySQL? And that will adjust the dialect correctly, and use underlying drivers to talk to that particular database.
1:33 We also have a SQL expression language, which is a little specific to the core, and we can define schemas and types here.
1:40 Where we probably want to spend most of our time is at the ORM. This is where you map classes, and objects into databases.
1:48 I think the best way, most efficient way as programmers for us to work,
1:52 not necessarily performance-efficient but mentally-cognitively-load-efficient is
1:57 to work in objects and hierarchies, not in normalized database schemas, right. So this lets us come up with classes, decorate them or define them
2:07 in a way that SQLAlchemy knows how to translate them to and from the database, and then we just work in these classes, we create the classes
2:14 and we can insert them into the database. When we do queries, it's with regards to the fields or attributes of these classes and so on.
2:22 This gives you just a sense of the various pieces at play when we are working with SQLAlchemy.

Talk Python's Mastodon Michael Kennedy's Mastodon