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.