Intro to SqlAlchemy

Last updated on July 27, 2020

SQLAlchemy is a defacto framework for working with relational databases in Python. It was created by Mike Bayer in 2005. SQLAlchemy allows you to work with databases from different vendors like MySQL, MS-SQL, PostgreSQL, Oracle, SQLite, and many others.

Why use SQLAlchemy #

The most exciting feature of SQLAlchemy is its ORM. An ORM or Object Relational Mapper allows us to work with database using Object Oriented code rather than writing SQL queries. Another great benefit we get from using a framework like SQLAlchemy is that no matter which database we use our underlying code will remain the same. This makes it easy to migrate from one database from another without rewriting the application code.

SQLAlchemy has another component called SQLAlchemy Core. The Core is just a smooth abstraction over the traditional SQL. The Core provides a SQL Expression Language which allows us to generate SQL statements using Python constructs. Unlike ORM which revolves around model and objects, SQL Expression revolves around tables, columns, indexes etc (just like plain old SQL). SQL Expression Language closely resembles SQL but it is standardized so that you can use it across many different databases. You can use SQLAlchemy ORM and SQLAlchemy Core independently or together depending upon what you want to accomplish. Behind the scenes, SQLAlchemy ORM uses SQLAlchemy Core.

So Which one should you use SQLAlchemy Core or ORM?

The whole point of having an ORM is to make working with database easy. In the process, it also adds some additional overhead. However, for most applications, this overhead is not much unless you are working with large amounts of data. For most project SQLAlchemy ORM would be enough but If you writing an application where you will be handling a huge amount of data (like in a database warehouse) or you want to have more control over query or you are an SQL purist and prefer to work directly with rows and columns use core.

Who Uses SQLAlchemy #

  1. Reddit
  2. Hulu
  3. Fedora Project
  4. Dropbox
  5. OpenStack

and many more.

Prerequisites: #

In order to go through this tutorial, you should have a basics understanding of Python and SQL. For a quick refresher on Python checkout our Python tutorial. For SQL search the web.

Note: The source code of this tutorial is available at https://github.com/overiq/sqlalchemy.