What is SQLAlchemy Used For? An Overview with Practical Examples

SQLAlchemy is a open-source toolkit for the Python programming language, designed for efficient and high-performing database access. Its primary role is to help Python applications interact with relational databases by making it easier to create, update, and query tables. This makes SQLAlchemy a powerful tool for any Python developer working with data storage or retrieval.

In this blog, we’ll highlight insights about SQLAlchemy, including its uses, benefits, core functionalities, and practical examples, and explore how to access your data in Python using SQLAlchemy ORMs. Finally, we’ll introduce you to CData Python Connectors and explain how they empower you to build Python applications and scripts leveraging SQLAlchemy ORMs.

What is SQLAlchemy?

SQLAlchemy is an open-source SQL toolkit and Object-Relational Mapping (ORM) system for Python. It provides developers with the flexibility of using SQL databases in a Pythonic way. This means developers can work with Python objects and do not need to write separate SQL queries.

As an ORM, SQLAlchemy can be used to convert data between incompatible type systems such as databases and object-oriented programming (OOP) languages like Python. It gives application developers the full power and flexibility of SQL, providing a full suite of well-known enterprise-level persistence patterns, adapted into a simple and Pythonic domain language.

Why do we use SQLAlchemy?

SQLAlchemy offers several utilities that make it a popular choice among developers. Here are some key benefits:

1. Efficiency: SQLAlchemy allows developers to interact with the database using Python’s objects and methods, eliminating the need to write separate SQL queries. This results in more efficient data management and manipulation, as developers can leverage the full power of Python while working with their data.
2. Code readability: By enabling developers to work with data as Python objects, SQLAlchemy enhances code readability. This makes the code easier to understand, maintain, and debug. It also allows developers to leverage Python’s features to write cleaner and more efficient code.
3. Database independence: One of the key strengths of SQLAlchemy is its database independence. It provides a consistent API that abstracts away differences between specific databases. This means you can switch between different database systems (like SQLite, PostgreSQL, MySQL, etc.) with minimal changes to your code. This flexibility makes SQLAlchemy a versatile tool for a wide range of projects.

Why use SQLAlchemy over PyODBC?

SQLAlchemy and PyODBC are both powerful tools for interacting with databases in Python, but they serve different purposes and offer unique features:

1. Level of abstraction: PyODBC operates at a lower level, allowing you to connect to and use an ODBC driver using the standard DB API 2.0. On the other hand, SQLAlchemy resides one level higher and provides a variety of features such as Object-Relational Mapping (ORM), query constructions, caching, and eager loading.
2. Database independence: SQLAlchemy provides a consistent API that abstracts away differences between specific databases, enabling you to switch between different database systems with minimal changes to your code. PyODBC doesn’t offer this level of database independence.
3. Integration: SQLAlchemy can work with PyODBC or any other driver that supports DB API 2.0. This means you can leverage the benefits of both tools when using SQLAlchemy.
4. Object-relational mapping (ORM): SQLAlchemy provides ORM, which allows developers to interact with their database like they would with SQL. PyODBC doesn’t offer ORM.

Core functionalities of SQLAlchemy

The core functionalities of SQLAlchemy, including connection management, ORM, and data manipulation capabilities, enable seamless interaction with databases in Python applications.

1. SQLAlchemy connection management: SQLAlchemy manages database connections efficiently, ensuring resources are used and released back to the system properly. It supports concurrent connections to multiple databases, unlocking powerful data manipulation capabilities. SQLAlchemy’s Engine and Session objects are central to its connection management. The Engine object provides a source of database connectivity and behavior, while the Session object is the primary interface for persistence operations.
2. SQLAlchemy ORM: SQLAlchemy’s ORM allows developers to interact with their database like they would with SQL. It maps a relational database system to Python objects, enabling you to use Python objects and classes to communicate with the database. The ORM is independent of the specific relational database system used. This means you can switch between different database systems with minimal changes to your code.
3. Data manipulation with SQLAlchemy: SQLAlchemy provides comprehensive support for data manipulation. It simplifies interactions with SQL databases and performs raw queries. SQLAlchemy allows you to work with data as Python objects, which makes data manipulation more intuitive. It also supports advanced functions for data manipulation, such as filtering, ordering, and grouping.

Practical examples using SQLAlchemy

In this segment, we explore a few practical examples employing SQLAlchemy. These include table creation, data analytics, and leveraging SQLAlchemy functions and expressions for effective database interaction and analysis.

First, install SQLAlchemy using pip command. Subsequently, create an SQLite database engine to execute SQL queries using Python code through SQLAlchemy.

Python code

pip install sqlalchemy

import sqlalchemy as db

engine = db.create_engine('sqlite:///users.db', echo=True)


1. Creating tables: SQLAlchemy allows you to create tables in a database using Python code. Here’s an example of creating a table named ‘profile’ with columns ‘email’, ‘name’, and ‘contact’:

Python code

from sqlalchemy import Table, Column, Integer, String, MetaData
meta = MetaData()

profile = Table(
'profile', meta,
Column('email', String, primary_key=True),
Column('name', String),
Column('contact', Integer),
)

meta.create_all(engine)


In this code, meta.create_all(engine) binds the metadata to the engine and creates the ‘profile’ table if it doesn’t exist in the database.

2. Data analytics: SQLAlchemy can be used for data analytics. For example, SQLAlchemy can be used to access and analyze data from an SQLite database.
   
   Python code

from sqlalchemy import create_engine, MetaData, Table, select

engine = create_engine('sqlite:///census.sqlite')
connection = engine.connect()
metadata = MetaData()
census = Table('census', metadata, autoload=True, autoload_with=engine)

query = select([census])
result = connection.execute(query).fetchall()
for row in result:
    print(row)

In this code, Table('census', metadata, autoload=True, autoload_with=engine creates a table object for the ‘census’ table in the SQLite database. Make sure you have the necessary database file and table set up before running this code.

3. SQLAlchemy functions and expressions: SQLAlchemy provides a variety of functions and expressions that you can use to construct SQL queries. For example, you can use the text() function to write SQL expressions:

Python code

from sqlalchemy import text

sql = text('SELECT * FROM BOOKS WHERE BOOKS.book_price > 100')

results = engine.execute(sql)
result = engine.execute(sql).fetchall()
for record in result:
    print("\n", record)

In this code, text('SELECT * from BOOKS WHERE BOOKS.book_price > 100') creates a SQL expression that selects all books with a price greater than 100. The execute() method is then used to execute this SQL expression.

The CData difference

Accessing your data in Python via SQLAlchemy ORMs can be a powerful and efficient method for interacting with your database. However, the capabilities of SQLAlchemy can be significantly enhanced with the help of CData Python Connectors. These connectors provide a standard interface for accessing data from various sources beyond traditional databases, including NoSQL databases, web services, and file formats. This means you can use the same methods and properties to connect to and interact with all of your data, making your Python applications and scripts more versatile and easier to maintain.

To gain a deeper understanding of how to use SQLAlchemy ORMs for accessing a data source in Python using CData Python Connector, refer to our Knowledge Base article.

Whether you’re building a data-driven web application, performing data analysis, or automating data-related tasks, CData Python data connectors and SQLAlchemy ORMs can provide you with the options you need to access and manipulate data from more than 200 data sources efficiently and effectively.

As always, our support team is ready to answer any questions. Have you joined the CData Community? Ask questions, get answers, and share your knowledge in CData connectivity tools. Join us!