From the course: Advanced SQL for Application Development

Introduction to SQLAlchemy

From the course: Advanced SQL for Application Development

Start my 1-month free trial

Introduction to SQLAlchemy

“

- Now we're going to take a look at a particular Object relational mapping platform or ORM. And this one is called SQLAlchemy. SQLAlchemy is a Python SQL toolkit and it's designed to map between objects and relations. Now, basically the way we often think about it is that tables, or more specifically relations, because it could include views for example. Those tables are mapped to classes. And SQLAlchemy is designed to perform many of the low-level tasks associated with mapping from a class to SQL statements. But SQLAlchemy also allows developers to use SQL statements so you can explicitly write a SQL statement and use that as well when you're using SQLAlchemy. The reason we can do this is because SQLAlchemy has two basic components. One is called Core and that does the basic database operations. And then, there is the ORM component and that's the part that does the mapping between object and relational models. Now, SQLAlchemy provides functions that do several kinds of high-level operations like connecting to a database or declaring a mapping which is basically a specification of how you map rows in a table to a particular instances. There are functions for creating sessions as well as, for updating, adding, and deleting data. And of course, you can also query data using SQLAlchemy. And one thing that's important when you're working with transaction processing systems, is the ability to perform commits and rollbacks. And that's also supported with SQLAlchemy. So, some things to keep in mind about SQLAlchemy is that it's built on the industry standard DBAPI. So, it's very easy to work with different databases. And SQLAlchemy allows you to work in an object-oriented fashion and then generates SQL for you. So, it does the mapping, but literal mapping in terms of mapping object statements into SQL statements which can execute and actually return the data or operate on the data as you specify. And one of the nice benefits of this is that it abstracts away database-specific implementation details. So, for example, if you are writing an application, and you want to be able to easily port your application to use either ISQL or a postgreSQL backend, then using SQlAlchemy can actually make that easier because you don't have to worry about implementing maybe a mySQL-specific SQL statement in your application. You'll let SQLAlchemy do the mapping from your object structure to whatever specific SQL statement is required for the backend you're using. So, that's one of the big advantages of using an object relational mapper is that in addition to allowing you to work in your object... In addition to allowing you to work in your object modeling paradigm, which you're maybe doing across your application, it also abstracts away some of the database specifics so you can more easily move your application and use it with different backends.

Contents