Using Mimer SQL with SQLAlchemy: Modern Database Access for Python Developers

Introduction

Python is a very popular languages for database-driven applications, ranging from simple analytics scripts to full-scale web services. For developers who value structure, flexibility, and database independence, SQLAlchemy has become the de-facto standard for working with relational databases in Python.

With the release of sqlalchemy-mimer, Mimer SQL now has a native dialect for SQLAlchemy — enabling seamless integration with both SQLAlchemy Core and its powerful Object Relational Mapper (ORM). This means that you can use Mimer SQL with the same expressive Pythonic abstractions available for other major databases.

In this article, we will introduce SQLAlchemy, explain how it works with Mimer SQL, and show practical examples for both Core and ORM programming styles.

What is SQLAlchemy?

At its core, SQLAlchemy is a Python library that provides a flexible and efficient way to interact with relational databases.

It offers two complementary layers:

1. SQLAlchemy Core — a lower-level API that lets you build and execute SQL statements using Python objects instead of manually writing SQL strings. It’s perfect for performance-sensitive applications or when you need fine-grained control over SQL.
2. SQLAlchemy ORM — a higher-level abstraction built on top of Core that maps Python classes to database tables, enabling developers to work with objects instead of raw SQL rows. It is ideal for large applications where maintainability and readability are key values.

The sqlalchemy-mimer package acts as the glue between SQLAlchemy and the Mimer SQL database. It implements all the necessary dialect and driver logic so that you can use SQLAlchemy’s full feature set, including auto-incrementing columns, schema reflection, transactions, and relationships.

Getting Started

To use Mimer SQL with SQLAlchemy, simply install the Mimer SQL dialect for SQLAlchemy. The following command will install SQLAlchemy and MimerPy if they are not already installed (a Mimer SQL database is presumed to be installed, in this case named mimerdb):

python -m pip install sqlalchemy-mimer

Then create an SQLAlchemy engine using the mimer:// connection URL format:

from sqlalchemy import create_engine
engine = create_engine("mimer://SYSADM:SYSPASS@mimerdb", echo=True)

The echo=True flag enables SQL logging so you can see exactly what SQLAlchemy sends to the database — useful for debugging and learning.

SQLAlchemy Core Example

The Core layer lets you construct SQL expressions programmatically.

Here is a simple example with two related tables, authors and books, demonstrating foreign keys and auto-incrementing primary keys.

from sqlalchemy import create_engine
from sqlalchemy import (
    Table, Column, Integer, String, ForeignKey, MetaData, select
)
engine = create_engine("mimer://SYSADM:SYSPASS@mimerdb", echo=True)
metadata = MetaData()
authors = Table(
"authors", metadata,
    Column("id", Integer, primary_key=True, autoincrement=True),
    Column("name", String(50), nullable=False)
)

books = Table(
    "books", metadata,
    Column("id", Integer, primary_key=True, autoincrement=True),
    Column("title", String(100), nullable=False),
    Column("author_id", Integer, ForeignKey("authors.id"))
)

# Create tables

metadata.create_all(engine)

# Insert sample data

with engine.begin() as conn:
    conn.execute(authors.insert(), [{"name": "Isaac Asimov"}, {"name": "Arthur C. Clarke"}])
    conn.execute(books.insert(), [{"title": "Foundation", "author_id": 1},
                                  {"title": "2001: A Space Odyssey", "author_id": 2}])

    # Query with a JOIN

    stmt = (
        select(books.c.title, authors.c.name)
        .join(authors, books.c.author_id == authors.c.id)
)

for row in conn.execute(stmt):
    print(f"{row.title} by {row.name}")

This produces:

Foundation by Isaac Asimov
2001: A Space Odyssey by Arthur C. Clarke

SQLAlchemy ORM Example

While Core is great for raw control, the ORM makes it even easier to work with data in an object-oriented way.

Here is an equivalent ORM example using mapped classes, relationships, and automatic key generation:

from sqlalchemy import Integer, String, ForeignKey
from sqlalchemy.orm import DeclarativeBase, relationship, Session
from sqlalchemy import Column, create_engine

class Base(DeclarativeBase):
    pass

class Author(Base):
    __tablename__ = "authors"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)
    books = relationship("Book", back_populates="author")

class Book(Base):
    __tablename__ = "books"
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(100), nullable=False)
    author_id = Column(Integer, ForeignKey("authors.id"))
    author = relationship("Author", back_populates="books")

if __name__ == "__main__":
    engine = create_engine("mimer://SYSADM:SYSPASS@mimerdb", echo=True)

    # Create tables

    Base.metadata.create_all(engine)

    # Insert and query using ORM

    with Session(engine) as session:
        herbert = Author(name="Frank Herbert")
        dick = Author(name="Philip K. Dick")
        session.add_all([
            herbert,
            dick,
            Book(title="Dune", author=herbert),
            Book(title="Do Androids Dream of Electric Sheep?", author=dick),
        ])
        session.commit()

# Query using relationships

    with Session(engine) as session:
        for book in session.query(Book).join(Book.author):
            print(f"{book.title} by {book.author.name}")

This produces:

Dune by Frank Herbert
Do Androids Dream of Electric Sheep? by Philip K. Dick

Integration Details

The sqlalchemy-mimer dialect is built on top of MimerPy, the official Python driver for Mimer SQL (PEP-249 compliant), and it supports the following:

• Auto-incrementing primary keys using Mimer SQL sequences.
• Schema reflection (reading existing table definitions).
• Transaction management via SQLAlchemy’s standard interfaces.
• Unicode and binary data types.
• Parameterized queries for efficient and secure database access.

Developers can mix SQLAlchemy Core and ORM within the same project, giving the flexibility to optimize where needed.

Why Use SQLAlchemy with Mimer SQL?

SQLAlchemy offers a powerful combination of abstraction and transparency — you get the benefits of clean, maintainable Python code while retaining full control over SQL execution.

For Mimer SQL users, this means:

• Modern Pythonic access to Mimer SQL databases.
• Compatibility with frameworks like Flask, FastAPI, and Alembic.
• Consistent syntax across multiple database backends.
• Fine-grained performance control when needed.

With sqlalchemy-mimer, Mimer SQL fully participates in the Python data ecosystem — enabling developers to build everything from lightweight data tools to enterprise-scale applications with confidence.

Learn More

• MimerPy on Github: https://github.com/mimersql/mimerpy
• SQLAlchemy-Mimer on GitHub: https://github.com/mimersql/sqlalchemy-mimer
• SQLAlchemy Documentation: https://docs.sqlalchemy.org
• Mimer SQL Developer: https://developer.mimer.com