SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper
Tags: python, programming, sql
Published: 2023-12-09 15:22:09

<p>Hi, Community!</p>
<p>This article is an overview of SQLAlchemy, so let's begin!</p>
<p>SQLAlchemy is the Python SQL toolkit that&nbsp;serves as a bridge between your Python code and the relational database system of your choice. Created by Michael Bayer, it is currently available as an open-source library under the MIT License.&nbsp;SQLAlchemy supports a wide range of database systems, including PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server, making it versatile and adaptable to different project requirements.</p>
<p>The SQLAlchemy SQL Toolkit and Object Relational Mapper from a comprehensive set of tools for working with databases and Python. It has several distinct areas of functionality which you can use individually or in various combinations. The major components are illustrated below, with component dependencies organized into layers:</p>
<p><img alt="_images/sqla_arch_small.png" src="https://docs.sqlalchemy.org/en/20/_images/sqla_arch_small.png"><!--break--><br>As you can see above, the two most significant front-facing portions of SQLAlchemy are the&nbsp;<strong>Object Relational Mapper (ORM)</strong>&nbsp;and the&nbsp;<strong>Core</strong>.</p>
<h2><br><strong>Object Relational Mapper(ORM)</strong></h2>
<p>The ORM component provides a way to map Python objects to database tables. It simplifies writing code that interacts with databases.&nbsp;The ORM builds upon Core to provide the means of working with a domain object model mapped to a database schema. When you utilize it, you usually construct SQL statements in almost the same way as with Core. However, the task of DML, which refers to the persistence of business objects in a database here, is automated with the help of a pattern called the unit of work. It translates changes in states against mutable objects into INSERT, UPDATE, and DELETE constructs that then get invoked in terms of those objects. SELECT statements are augmented by ORM-specific automation and object-centric querying capabilities as well.</p>
<p>Below, you can observe a basic example of defining an ORM class in SQLAlchemy:</p>
<pre class="codeblock-container" idlang="2" lang="Python" tabsize="4"><code class="language-python hljs"><span class="hljs-keyword">from</span> sqlalchemy <span class="hljs-keyword">import</span> Column, Integer, String
<span class="hljs-keyword">from</span> sqlalchemy.ext.declarative <span class="hljs-keyword">import</span> declarative_base

Base = declarative_base()

<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">User</span><span class="hljs-params">(Base)</span>:</span>
    __tablename__ = <span class="hljs-string">'users'</span>
    id = Column(Integer, primary_key=<span class="hljs-keyword">True</span>)
    username = Column(String(<span class="hljs-number">50</span>), unique=<span class="hljs-keyword">True</span>)
    email = Column(String(<span class="hljs-number">100</span>))</code></pre>
<p>With this class, you can easily create, query, update, and delete user records in your database.</p>
<p>&nbsp;</p>
<h2><strong>Core</strong></h2>
<p>The Core component&nbsp;provides the underlying functionality for interacting with databases, including connection pooling, transaction management, and SQL expression generation.&nbsp;The central element of both SQLAlchemy Core and ORM is the SQL Expression Language that facilitates fluent and composable construction of SQL queries. The central element of both SQLAlchemy Core and ORM is the SQL Expression Language that facilitates fluent and composable construction of SQL queries.</p>
<p>The SQL Expression Language is a toolkit on its own, independent of the ORM package. It provides a system of constructing SQL expressions represented by composable objects, which can then be “executed” against a target database within the scope of a specific transaction.</p>
<p>The ORM is built upon Core to provide the means of working with a domain object model mapped to a database schema. When you utilize it, you usually construct SQL statements in almost the same way as with Core. However, the task of DML, which refers to the persistence of business objects in a database here, is automated with the help of a pattern called the unit of work. It translates changes in states against mutable objects into INSERT, UPDATE, and DELETE constructs that then get invoked in terms of those objects. SELECT statements are augmented by ORM-specific automation and object-centric querying capabilities as well.</p>
<p>Whereas working with Core and the SQL Expression language offers a schema-centric view of the database, along with an immutability-oriented programming paradigm, the ORM builds a domain-centric view of the database on top of this, with a programming paradigm that is more explicitly object-oriented and reliant upon mutability. Since a relational database is a mutable service itself, the difference is that whereas Core/SQL Expression language is command-oriented, the ORM is state-oriented.</p>
<p>Check out an example of a simple SQLAlchemy query below:</p>
<pre class="codeblock-container" idlang="0" lang="ObjectScript" tabsize="4"><code class="language-cls hljs cos">from sqlalchemy import create_engine, text

engine = create_engine('sqlite:<span class="hljs-comment">///my_database.db')</span>
conn = engine.connect()

query = text(<span class="hljs-string">"SELECT username FROM users WHERE id = :user_id"</span>)
result = conn.execute(query, user_id=<span class="hljs-number">1</span>)
</code></pre>
<p>SQLAlchemy incorporates a connection pooling system that efficiently manages database connections. It automatically handles connection creation, reuse, and release, reducing overhead and improving performance of applications with frequent database interactions.</p>
<p><!--break--></p>
<h2>Simple Use case of SQLAlchemy CRUD operations</h2>
<ol>
<li><strong>Establish Connectivity - the Engine</strong></li>
<li><strong>Define your database tables</strong></li>
<li><strong>Use&nbsp;ORM to save data</strong></li>
<li><strong>Retrieve data by using ORM</strong></li>
</ol>
<h3>1. Establishing Connectivity - the Engine</h3>
<p>Every SQLAlchemy application starts with an object called the Engine. This object acts as a central source of connections to a particular database, providing both a factory and a holding space called a&nbsp;connection pool&nbsp;for these database connections. The Engine is typically a global object that needs to be created only once for a particular database server. It is usually configured using a URL string that describes how it should connect to the database host or backend.</p>
<p>We will use an in-memory-only SQLite database.&nbsp;The Engine we will assemble by using the create_engine() function:</p>
<pre class="codeblock-container" idlang="2" lang="Python" tabsize="4"><code class="language-python hljs"><span class="hljs-keyword">from</span> sqlalchemy <span class="hljs-keyword">import</span> create_engine
engine = create_engine(<span class="hljs-string">"sqlite+pysqlite:///:memory:"</span>, echo=<span class="hljs-keyword">True</span>)</code></pre>
<h3><br>2.&nbsp; Defining&nbsp;your database tables</h3>
<p>To operate the ORM, first, we need to define database tables utilizing Python classes. For example, the following Python class defines a <code>User</code> table:</p>
<pre class="codeblock-container" idlang="2" lang="Python" tabsize="4"><code class="language-python hljs"><span class="hljs-keyword">from</span> sqlalchemy <span class="hljs-keyword">import</span> SQLAlchemy

db = SQLAlchemy()

<span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">User</span><span class="hljs-params">(db.Model)</span>:</span>
    id = db.Column(db.Integer, primary_key=<span class="hljs-keyword">True</span>)
    email = db.Column(db.String(<span class="hljs-number">150</span>), unique=<span class="hljs-keyword">True</span>)
    username = db.Column(db.String(<span class="hljs-number">150</span>), unique=<span class="hljs-keyword">True</span>)
    password = db.Column(db.String(<span class="hljs-number">150</span>))</code></pre>
<h3>3.&nbsp; Using&nbsp;ORM to save data</h3>
<p>To access the User table, we should import the User from our model class and then exploit the session object to add data:</p>
<pre class="codeblock-container" idlang="0" lang="ObjectScript" tabsize="4"><code class="language-cls hljs cos">from models import User
from sqlalchemy import SQLAlchemy

db = SQLAlchemy()

new_user = User(email=<span class="hljs-string">"email address"</span>, username=<span class="hljs-string">"username"</span>, password=<span class="hljs-string">"Password"</span>))
db.session.add(new_user)
db.session.commit()</code></pre>
<h3>4. Retrieving data by using ORM</h3>
<p>We will employ the query object of the session to retrieve data from the database:</p>
<pre class="codeblock-container" idlang="0" lang="ObjectScript" tabsize="4"><code class="language-cls hljs cos">from models import User
from sqlalchemy import SQLAlchemy

db = SQLAlchemy()

# Query the User table
users = db.session.query(User).all()

# Iterate over the records and <span class="hljs-keyword">print</span> them to the console
<span class="hljs-keyword">for</span> user in users:
    <span class="hljs-keyword">print</span>(user.username)</code></pre>
<h2>&nbsp;</h2>
<h2>Use Cases for SQLAlchemy</h2>
<p>SQLAlchemy is widely used in various domains, including the next ones:</p>
<ul>
<li><strong>Web Development</strong>: Many web frameworks, such as Flask and Django, integrate seamlessly with SQLAlchemy for managing databases.</li>
<li><strong>Data Analysis and Reporting</strong>: SQLAlchemy helps data scientists and analysts interact with relational databases when performing data analysis.</li>
<li><strong>Enterprise Applications</strong>: It is a preferred choice for building robust, scalable, and maintainable enterprise-level applications.</li>
<li><strong>Microservices</strong>: SQLAlchemy supports microservices architecture by enabling each service to interact with its database.</li>
</ul>
<h2 style="margin-top: 4px; margin-bottom: 4px;">&nbsp;</h2>
<h2 style="margin-top: 4px; margin-bottom: 4px;"><span style="font-size:11pt"><span style="background:white"><span style="line-height:normal"><span style="font-family:Calibri,sans-serif"><span style="font-size:13.5pt"><span style="font-family:&quot;Arial&quot;,sans-serif"><span style="color:#1f1f1f">Benefits of Using SQLAlchemy</span></span></span></span></span></span></span></h2>
<p><span style="font-size:12.0pt"><span style="line-height:107%"><span style="font-family:&quot;Arial&quot;,sans-serif"><span style="color:#1f1f1f">SQLAlchemy has many benefits, some of which are listed below:</span></span></span></span></p>
<ul>
<li><span style="font-size:11pt"><span style="background:white"><span style="line-height:normal"><span style="font-family:Calibri,sans-serif"><span style="font-size:12.0pt"><span style="font-family:&quot;Arial&quot;,sans-serif"><span style="color:#1f1f1f"><strong>Increased productivity:</strong>&nbsp; SQLAlchemy can help developers to be more productive by providing a high-level abstraction for database access. </span></span></span></span></span></span></span>It lets developers focus on writing application code rather than being worried about the underlying SQL syntax.</li>
<li><strong><span style="font-size:12.0pt"><span style="line-height:107%"><span style="font-family:&quot;Arial&quot;,sans-serif"><span style="color:#1f1f1f">Reduced errors:&nbsp;</span></span></span></span></strong> SQLAlchemy can help us to reduce errors by providing a type system for database access. It allows us to ensure that developers use the correct data types and do not make any mistakes.</li>
<li><strong><span style="font-size:12.0pt"><span style="line-height:107%"><span style="font-family:&quot;Arial&quot;,sans-serif"><span style="color:#1f1f1f">Improved maintainability:</span></span></span></span></strong> SQLAlchemy can assist in the improvement of the maintainability of the code by making it easier to change the database schema. It is possible because SQLAlchemy code is not tightly coupled to the underlying database schema.</li>
<li><strong><span style="font-size:12.0pt"><span style="line-height:107%"><span style="font-family:&quot;Arial&quot;,sans-serif"><span style="color:#1f1f1f">Portability:</span></span></span></span></strong> SQLAlchemy code is portable to any database supported by SQLAlchemy. It simplifies the migration of applications to different databases.</li>
</ul>
<h2>Conclusion</h2>
<p>SQLAlchemy empowers Python developers to work with relational databases in a more&nbsp;<span style="font-size:11pt; font-variant:normal; white-space:pre-wrap"><span style="font-family:Arial,sans-serif"><span style="color:#000000"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none">pythonic</span></span></span></span></span></span> and efficient manner. Its ORM capabilities, SQL expression language, connection pooling, and cross-platform support make it a valuable tool for various applications. Whether you are a beginner or an experienced developer, SQLAlchemy can streamline your database-related tasks and improve your productivity.<br><br>In my next article, we will use SQLAlchemy and InterSystems Caché to demonstrate CRUD operations.</p>
<p><span style="font-size:11pt; font-variant:normal; white-space:pre-wrap"><span style="font-family:Arial"><span style="color:#000000"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none">Thank you for reading!</span></span></span></span></span></span></p>