sqlalchemy

THIS IS NOT THE OFFICIAL REPO - PLEASE SUBMIT PRs ETC AT: http://github.com/sqlalchemy/sqlalchemy

3,461

886

3,461

View on GitHub

Top Related Projects

alembic

2,714

A database migrations tool for SQLAlchemy.

databases

3,817

Async database support for Python. 🗄

peewee

11,053

a small, expressive orm -- supports postgresql, mysql, sqlite and cockroachdb

tortoise-orm

4,609

Familiar asyncio ORM for python, built with relations in mind

Quick Overview

SQLAlchemy is a powerful and flexible SQL toolkit and Object-Relational Mapping (ORM) library for Python. It provides a full suite of well-known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.

Pros

Highly flexible and adaptable to various database systems
Powerful ORM capabilities with intuitive query construction
Excellent documentation and community support
Supports both high-level ORM and low-level SQL operations

Cons

Steep learning curve for beginners
Can be overkill for simple database operations
Performance overhead compared to raw SQL in some cases
Complex configurations may be required for advanced use cases

Code Examples

Creating a table and inserting data:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, Session

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)

with Session(engine) as session:
    new_user = User(name='John Doe')
    session.add(new_user)
    session.commit()

Querying data:

from sqlalchemy import select

with Session(engine) as session:
    stmt = select(User).where(User.name == 'John Doe')
    user = session.scalars(stmt).first()
    print(user.name)

Updating data:

with Session(engine) as session:
    user = session.get(User, 1)
    user.name = 'Jane Doe'
    session.commit()

Getting Started

To get started with SQLAlchemy, first install it using pip:

pip install sqlalchemy

Then, create an engine and define your models:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, Session

engine = create_engine('sqlite:///example.db')
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

Base.metadata.create_all(engine)

Now you can create sessions and interact with your database:

with Session(engine) as session:
    new_user = User(name='John Doe')
    session.add(new_user)
    session.commit()

Competitor Comparisons

alembic

2,714

A database migrations tool for SQLAlchemy.

Pros of Alembic

Specialized for database migrations, offering a more focused toolset
Provides a command-line interface for easier migration management
Supports auto-generation of migration scripts based on model changes

Cons of Alembic

Limited to migration functionality, lacking SQLAlchemy's full ORM capabilities
Requires additional setup and configuration alongside SQLAlchemy
May have a steeper learning curve for users new to database migrations

Code Comparison

SQLAlchemy (ORM usage):

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

Alembic (migration script):

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table('users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(), nullable=True),
        sa.PrimaryKeyConstraint('id')
    )

While SQLAlchemy focuses on ORM and database interactions, Alembic specializes in managing database schema changes through migrations. Alembic is often used in conjunction with SQLAlchemy to handle evolving database structures in applications.

databases

3,817

Async database support for Python. 🗄

Pros of Databases

Async-first design, built for modern async Python frameworks
Simpler API, easier to get started for basic database operations
Supports multiple database backends with a unified interface

Cons of Databases

Less mature and feature-rich compared to SQLAlchemy
Smaller community and ecosystem of extensions/plugins
Limited support for complex ORM operations and advanced SQL features

Code Comparison

SQLAlchemy:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

Databases:

import databases
import sqlalchemy

metadata = sqlalchemy.MetaData()
users = sqlalchemy.Table(
    "users",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("name", sqlalchemy.String),
)

Both libraries provide ways to define database schemas and models, but SQLAlchemy offers a more declarative approach with its ORM, while Databases relies on SQLAlchemy's core functionality for table definitions. SQLAlchemy's approach is more verbose but offers greater flexibility and power for complex scenarios.

pony

3,606

Pony Object Relational Mapper

Pros of Pony

More intuitive and Pythonic syntax for defining models and queries
Automatic query optimization and generation
Built-in support for database schema generation and migrations

Cons of Pony

Smaller community and ecosystem compared to SQLAlchemy
Less flexibility for complex queries and database operations
Limited support for advanced database features and non-relational databases

Code Comparison

SQLAlchemy:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

Pony:

from pony.orm import *

db = Database()

class User(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)

Both SQLAlchemy and Pony are powerful ORM libraries for Python, but they differ in their approach and syntax. SQLAlchemy offers more flexibility and extensive support for various databases, while Pony focuses on simplicity and ease of use. The choice between them depends on the specific requirements of your project and personal preferences.

peewee

11,053

a small, expressive orm -- supports postgresql, mysql, sqlite and cockroachdb

Pros of Peewee

Lightweight and simple to use, with a smaller learning curve
Faster for simple queries and basic CRUD operations
More compact and readable code for straightforward database interactions

Cons of Peewee

Less feature-rich compared to SQLAlchemy, especially for complex queries
Smaller community and ecosystem, with fewer extensions and plugins
Limited support for advanced database features and optimizations

Code Comparison

SQLAlchemy:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

Peewee:

from peewee import *

db = SqliteDatabase('my_database.db')
class User(Model):
    name = CharField()
    class Meta:
        database = db

Both SQLAlchemy and Peewee are popular Python ORMs, but they cater to different needs. SQLAlchemy offers more power and flexibility for complex database operations, while Peewee provides a simpler and more lightweight approach for basic database interactions. The choice between the two depends on the project's requirements, complexity, and the developer's familiarity with ORM concepts.

tortoise-orm

4,609

Familiar asyncio ORM for python, built with relations in mind

Pros of Tortoise-ORM

Designed specifically for asyncio, providing native support for asynchronous operations
Simpler API and easier to learn for developers familiar with Django ORM
Lightweight and focused on async use cases, potentially offering better performance in certain scenarios

Cons of Tortoise-ORM

Less mature and with a smaller community compared to SQLAlchemy
Limited support for advanced SQL features and complex queries
Fewer database backends supported, primarily focused on PostgreSQL, MySQL, and SQLite

Code Comparison

SQLAlchemy (synchronous):

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

Tortoise-ORM (asynchronous):

from tortoise import Model, fields

class User(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=50)

    class Meta:
        table = "users"

Both ORMs provide similar functionality for defining models, but Tortoise-ORM's syntax is more concise and resembles Django's ORM. SQLAlchemy offers more flexibility and control over the underlying database operations, while Tortoise-ORM focuses on simplicity and async support.

orm

1,780

An async ORM. 🗃

Pros of ORM

Lightweight and designed for async usage with Python's asyncio
Integrates well with FastAPI and other modern async frameworks
Simple and intuitive API for basic CRUD operations

Cons of ORM

Less mature and feature-rich compared to SQLAlchemy
Smaller community and ecosystem of extensions/plugins
Limited support for complex queries and advanced database features

Code Comparison

SQLAlchemy:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

ORM:

import orm

class User(orm.Model):
    __tablename__ = 'users'
    id = orm.Integer(primary_key=True)
    name = orm.String(max_length=100)

Both ORMs provide similar syntax for defining models, but ORM's approach is slightly more concise. SQLAlchemy offers more flexibility and advanced features, while ORM focuses on simplicity and async support. SQLAlchemy is better suited for complex applications with intricate database interactions, whereas ORM shines in modern async-first projects, especially when used with FastAPI or similar frameworks.

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot

README

SQLAlchemy

.. |PyPI| image:: https://img.shields.io/pypi/v/sqlalchemy :target: https://pypi.org/project/sqlalchemy :alt: PyPI

.. |Python| image:: https://img.shields.io/pypi/pyversions/sqlalchemy :target: https://pypi.org/project/sqlalchemy :alt: PyPI - Python Version

.. |Downloads| image:: https://static.pepy.tech/badge/sqlalchemy/month :target: https://pepy.tech/project/sqlalchemy :alt: PyPI - Downloads

The Python SQL Toolkit and Object Relational Mapper

Introduction

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. SQLAlchemy provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.

Major SQLAlchemy features include:

An industrial strength ORM, built from the core on the identity map, unit of work, and data mapper patterns. These patterns allow transparent persistence of objects using a declarative configuration system. Domain models can be constructed and manipulated naturally, and changes are synchronized with the current transaction automatically.
A relationally-oriented query system, exposing the full range of SQL's capabilities explicitly, including joins, subqueries, correlation, and most everything else, in terms of the object model. Writing queries with the ORM uses the same techniques of relational composition you use when writing SQL. While you can drop into literal SQL at any time, it's virtually never needed.
A comprehensive and flexible system of eager loading for related collections and objects. Collections are cached within a session, and can be loaded on individual access, all at once using joins, or by query per collection across the full result set.
A Core SQL construction system and DBAPI interaction layer. The SQLAlchemy Core is separate from the ORM and is a full database abstraction layer in its own right, and includes an extensible Python-based SQL expression language, schema metadata, connection pooling, type coercion, and custom types.
All primary and foreign key constraints are assumed to be composite and natural. Surrogate integer primary keys are of course still the norm, but SQLAlchemy never assumes or hardcodes to this model.
Database introspection and generation. Database schemas can be "reflected" in one step into Python structures representing database metadata; those same structures can then generate CREATE statements right back out - all within the Core, independent of the ORM.

SQLAlchemy's philosophy:

SQL databases behave less and less like object collections the more size and performance start to matter; object collections behave less and less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accommodate both of these principles.
An ORM doesn't need to hide the "R". A relational database provides rich, set-based functionality that should be fully exposed. SQLAlchemy's ORM provides an open-ended set of patterns that allow a developer to construct a custom mediation layer between a domain model and a relational schema, turning the so-called "object relational impedance" issue into a distant memory.
The developer, in all cases, makes all decisions regarding the design, structure, and naming conventions of both the object model as well as the relational schema. SQLAlchemy only provides the means to automate the execution of these decisions.
With SQLAlchemy, there's no such thing as "the ORM generated a bad query" - you retain full control over the structure of queries, including how joins are organized, how subqueries and correlation is used, what columns are requested. Everything SQLAlchemy does is ultimately the result of a developer-initiated decision.
Don't use an ORM if the problem doesn't need one. SQLAlchemy consists of a Core and separate ORM component. The Core offers a full SQL expression language that allows Pythonic construction of SQL constructs that render directly to SQL strings for a target database, returning result sets that are essentially enhanced DBAPI cursors.
Transactions should be the norm. With SQLAlchemy's ORM, nothing goes to permanent storage until commit() is called. SQLAlchemy encourages applications to create a consistent means of delineating the start and end of a series of operations.
Never render a literal value in a SQL statement. Bound parameters are used to the greatest degree possible, allowing query optimizers to cache query plans effectively and making SQL injection attacks a non-issue.

Documentation

Latest documentation is at:

https://www.sqlalchemy.org/docs/

Installation / Requirements

Full documentation for installation is at Installation <https://www.sqlalchemy.org/docs/intro.html#installation>_.

Getting Help / Development / Bug reporting

Please refer to the SQLAlchemy Community Guide <https://www.sqlalchemy.org/support.html>_.

Code of Conduct

Above all, SQLAlchemy places great emphasis on polite, thoughtful, and constructive communication between users and developers. Please see our current Code of Conduct at Code of Conduct <https://www.sqlalchemy.org/codeofconduct.html>_.

License

SQLAlchemy is distributed under the MIT license <https://www.opensource.org/licenses/mit-license.php>_.

Top Related Projects

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot