SQLAlchemy is an object-relational mapping (ORM) library for Python. It abstracts the database interaction and allows us to work with databases in a more Pythonic and object-oriented way. SQLAlchemy supports various database systems, including PostgreSQL, MySQL, SQLite, and more.
We can install SQLAlchemy using pip
, the Python package manager, with the following command:
pip install SQLAlchemy
Now that we have the prerequisite set up let’s go through the process of connecting to a database using SQLAlchemy. The following steps demonstrate how to connect to a database in SQLAlchemy.
First, let’s import the SQLAlchemy engine in the Python script or application, as shown below:
from sqlalchemy import create_engine
To establish a connection to the database, we need to create an SQLAlchemy engine. The engine is responsible for managing the database connection and executing SQL commands.
# Replace 'database://username:password@hostname/database_name' with your database informationengine = create_engine('database://username:password@hostname/database_name')
Replace the placeholder values with your actual database credentials and connection details. For example, if you are using MySQL, the connection string would start with 'mysql://'
.
We can connect with databases in multiple ways, as described below:
SQLite in-memory database: In the provided example, sqlite:///memory
creates an SQLite database in memory. This means the database is temporary and exists only while the application runs. Any data stored in an in-memory database will be lost when the application exits. We can specify a file path to create a persistent SQLite database on disk, retaining data across application runs.
Here is an example for creating an in-memory database:
engine = create_engine('sqlite:///memory')
SQLite persistent database on disk: To create a persistent SQLite database on disk, we specify a file path in the SQLite URL. This database is stored as a file on the filesystem, and the data persists across application sessions.
Here is an example for creating a persistent database:
engine = create_engine('sqlite:///path/to/your/database.db')
Other relational databases: SQLAlchemy supports various relational databases, not just SQLite. We can use SQLAlchemy with databases like PostgreSQL, MySQL, Oracle, and more. For that, we must provide the appropriate connection URL for the specific database system to connect to these databases.
Here are two examples: one uses PostgreSQL, and the other uses MySQL.
engine = create_engine('postgresql://username:password@localhost/mydatabase')
engine = create_engine('mysql://username:password@localhost/mydatabase')
NoSQL databases: SQLAlchemy also supports some NoSQL databases like MongoDB through extensions. Connecting to NoSQL databases typically involves different configurations and models tailored to the specific database type.
Here is an example of connecting with MongoDB via SQLAlchemy:
engine = create_engine("mongodb:///?Server=ServerName&Port=3000&Database=DBName&User=UserName&Password=UserPassword")
Cloud Databases: Using cloud-based databases like Amazon RDS, Google Cloud SQL, or Azure SQL Database, SQLAlchemy can connect to these services using appropriate connection URLs and configurations. Flask-SQLAlchemy is a Flask-specific extension that makes it simpler to use and interact with SQLAlchemy. We can start using SQLAlchemy by adding the following code:
from flask import Flaskfrom flask_sqlalchemy import SQLAlchemyapp = Flask(__name__)app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://user:password@database.us-east-1.rds.amazonaws.com:PORT_NUMBER/mydatabase"db = SQLAlchemy(app)
These are just a few examples of using databases with SQLAlchemy. The choice of database and configuration depends on your project’s specific needs, scalability requirements, and deployment environment.
Now that we have an engine, we can use it to establish a connection to the database, as shown below:
connection = engine.connect()
The connection
object represents the active connection to the database.
With the established database connection, we can perform various database operations, such as executing SQL queries and creating, updating, and deleting records, using SQLAlchemy’s features.
Here’s a simple example of executing an SQL query to fetch data from a table:
result = connection.execute('SELECT * FROM my_table')for row in result:print(row)
Remember to replace 'my_table'
with the actual name of the table you want to query.
Once we are done with the database operations, it’s essential to close the connection to release resources, as follows:
connection.close()
Here is a complete example of SQLAlchemy ORM connecting with a database:
from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakertry:# Create an SQLite database in memory (you can replace ':memory:' with a file path)engine = create_engine('sqlite:///:memory:', echo=True)# Create a sessionSession = sessionmaker(bind=engine)session = Session()print("Connection is established")# All database related tasks should be performed here.# Close the sessionsession.close()except Exception as e:print(f"Error: {e}")
Lines 1–2: We import the necessary modules from SQLAlchemy, which are required for defining database connections, models, and sessions.
Lines 4–19: We use a try/catch block for errors that might come up due to wrong execution.
Line 6: We create an SQLite database engine connected to an in-memory database and print SQL statements executed with the echo
parameter set to True
for debugging.
Lines 9–10: We create a database session using the sessionmaker
class of SQLAlchemy. We bind the session to the previously created engine
using bind=engine
. This session allows us to interact with the database session.
Line 17: We close the database session using the close()
method. Closing the session is important to release any resources associated with it and ensure proper cleanup after performing database operations.
Unlock your potential: SQLAlchemy fundamentals series, all in one place!
To deepen your understanding of SQLAlchemy, explore our series of Answers below:
What is SQLAlchemy?
Understand SQLAlchemy’s role in Python database management, offering ORM, SQL expressions, and efficient data handling.
How to connect with a database in SQLAlchemy
Learn how to establish a connection with different databases using SQLAlchemy’s engine and session.
How to create a table in SQLAlchemy
Discover how to define and create database tables using SQLAlchemy’s ORM and core functionalities.
How to execute queries in SQLAlchemy
Explore how to perform CRUD operations efficiently using SQLAlchemy’s query execution methods.
How to use relationships and associations in SQLAlchemy?
Master the implementation of table relationships and associations for structured data modeling.
Free Resources