How to connect with a database in SQLAlchemy

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
Command to install SQLAlchemy

Connecting to a database

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.

1. Importing SQLAlchemy

First, let’s import the SQLAlchemy engine in the Python script or application, as shown below:

from sqlalchemy import create_engine
Importing the SQLAlchemy engine

2. Creating a database connection

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 information
engine = create_engine('database://username:password@hostname/database_name')
Configuring the SQLAlchemy engine for the PostgreSQL database

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')
Creating an SQLite in-memory database engine with SQLAlchemy

  • 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')
Creating an SQLite for a persistent database engine with SQLAlchemy

  • 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')
Configuring SQLAlchemy engine for PostgreSQL database
engine = create_engine('mysql://username:password@localhost/mydatabase')
Configuring SQLAlchemy engine for MySQL database

  • 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 Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://user:password@database.us-east-1.rds.amazonaws.com:PORT_NUMBER/mydatabase"
db = SQLAlchemy(app)
Configuring the SQLAlchemy engine for the Amazon RDS PostgreSQL database connection

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.

3. Establishing the connection

Now that we have an engine, we can use it to establish a connection to the database, as shown below:

connection = engine.connect()
Establishing a database connection with SQLAlchemy

The connection object represents the active connection to the database.

4. Perform database operations

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)
Executing SQL queries and printing results with SQLAlchemy

Remember to replace 'my_table' with the actual name of the table you want to query.

5. Closing the connection

Once we are done with the database operations, it’s essential to close the connection to release resources, as follows:

connection.close()
Closing a database connection with SQLAlchemy

Code

Here is a complete example of SQLAlchemy ORM connecting with a database:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
try:
    # Create an SQLite database in memory (you can replace ':memory:' with a file path)
    engine = create_engine('sqlite:///:memory:', echo=True)
    # Create a session
    Session = sessionmaker(bind=engine)
    session = Session()
    print("Connection is established")
    # All database related tasks should be performed here.
    # Close the session
    session.close()
except Exception as e:
    print(f"Error: {e}")

Code explanation

  • 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:

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved