Python Sqlalchemy

  • Python sqlalchemy. Follow edited Sep 11 '13 at 5:34. Asked Dec 24 '09 at 12:42. Anurag Uniyal Anurag Uniyal. 76.7k 39 39 gold badges 163 163.
  • SQLAlchemy is an open source SQL toolkit and ORM for the Python programming language released under the MIT license. It was released initially in February 2006 and written by Michael Bayer.
  • A SQLAlchemy.createall and SQLAlchemy.dropall methods to create and drop tables according to the models. A Model baseclass that is a configured declarative base. The Model declarative base class behaves like a regular Python class but has a query attribute attached that can be used to query the model. (Model and BaseQuery).
  • SQLAlchemy Tutorial

SQLAlchemy (source code) is a well-regarded database toolkit and object-relational mapper (ORM) implementation written in Python. SQLAlchemy provides a generalized interface for creating and executing database-agnostic code without needing to write SQL statements. Using SQLAlchemy’s declarativebase and Base.metadata.createall allows you to write just one class per table to use in the app, to use in Python outside of the app and to use in the database. With a separate and file, we establish our database table classes and connection a single time, then call them later as needed.

  • SQLAlchemy Core
  • SQLAlchemy ORM
  • SQLAlchemy Useful Resources
  • Selected Reading

SQLAlchemy is a popular SQL toolkit and Object Relational Mapper. It is written in Python and gives full power and flexibility of SQL to an application developer. It is an open source and cross-platform software released under MIT license.

SQLAlchemy is famous for its object-relational mapper (ORM), using which, classes can be mapped to the database, thereby allowing the object model and database schema to develop in a cleanly decoupled way from the beginning.

As size and performance of SQL databases start to matter, they behave less like object collections. On the other hand, as abstraction in object collections starts to matter, they behave less like tables and rows. SQLAlchemy aims to accommodate both of these principles.

For this reason, it has adopted the data mapper pattern (like Hibernate) rather than the active record pattern used by a number of other ORMs. Databases and SQL will be viewed in a different perspective using SQLAlchemy.

Michael Bayer is the original author of SQLAlchemy. Its initial version was released in February 2006. Latest version is numbered as 1.2.7, released as recently as in April 2018.

Python Sqlalchemy

What is ORM?

ORM (Object Relational Mapping) is a programming technique for converting data between incompatible type systems in object-oriented programming languages. Usually, the type system used in an Object Oriented (OO) language like Python contains non-scalar types. These cannot be expressed as primitive types such as integers and strings. Hence, the OO programmer has to convert objects in scalar data to interact with backend database. However, data types in most of the database products such as Oracle, MySQL, etc., are primary.


In an ORM system, each class maps to a table in the underlying database. Instead of writing tedious database interfacing code yourself, an ORM takes care of these issues for you while you can focus on programming the logics of the system.

SQLAlchemy - Environment setup


Let us discuss the environmental setup required to use SQLAlchemy.

Any version of Python higher than 2.7 is necessary to install SQLAlchemy. The easiest way to install is by using Python Package Manager, pip. This utility is bundled with standard distribution of Python.

Using the above command, we can download the latest released version of SQLAlchemy from and install it to your system.

In case of anaconda distribution of Python, SQLAlchemy can be installed from conda terminal using the below command −

Python Sqlalchemy Data Types

It is also possible to install SQLAlchemy from below source code −

SQLAlchemy is designed to operate with a DBAPI implementation built for a particular database. It uses dialect system to communicate with various types of DBAPI implementations and databases. All dialects require that an appropriate DBAPI driver is installed.

Python Sqlalchemy Tutorial

The following are the dialects included −

Python Sqlalchemy Oracle

  • Firebird
  • Microsoft SQL Server
  • MySQL
  • Oracle
  • PostgreSQL
  • SQLite
  • Sybase

Python Sqlalchemy Flask

To check if SQLAlchemy is properly installed and to know its version, enter the following command in the Python prompt −