Skip to content

sqlalchemy.rst

Latest commit

 

History

History
161 lines (113 loc) · 4.79 KB

sqlalchemy.rst

File metadata and controls

161 lines (113 loc) · 4.79 KB

SQLAlchemy

SQLAlchemy is a Python-SQL-Toolkit and object-relational mapper.

SQLAlchemy is known for its ORM, whereby it provides different patterns for object-relational mapping, whereby classes can be mapped to the database in different ways. The object model and the database schema are cleanly decoupled from the start.

SQLAlchemy differs fundamentally from other ORMs, as SQL and details of the object relation are not abstracted away: all processes are represented as a collection of individual tools.

SQLAlchemy supports PostgreSQL as well as other dialects of relational databases:

Dialects Python package import Docs
postgresql psycopg2-binary psycopg2 Installation
mysql mysqlclient MySQLdb README
mssql pyodbc pyodbc Wiki
oracle cx_oracle cx_Oracle cx_Oracle

Database connection

from sqlalchemy import create_engine

engine = create_engine("postgresql:///example", echo=True)

Data model

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Address(Base):
    __tablename__ = "address"

    id = Column(Integer, primary_key=True)
    street = Column(String)
    zipcode = Column(String)
    country = Column(String, nullable=False)


class Contact(Base):
    __tablename__ = "contact"

    id = Column(Integer, primary_key=True)


firstname = Column(String, nullable=False)
lastname = Column(String, nullable=False)
email = Column(String, nullable=False)
address_id = Column(Integer, ForeignKey(Address.id), nullable=False)
address = relationship("Address")

Create tables

Base.metadata.create_all(engine)

Create Session

session = Session(engine)
address = Address(street="Birnbaumweg 10", zipcode="79115", country="Germany")

contact = Contact(
    firstname="Veit", lastname="Schiele", email="veit@cusy.io", address=address
)

session.add(contact)
session.commit()

Read

contact = session.query(Contact).filter_by(email="veit@cusy.io").first()
print(contact.firstname)

contacts = session.query(Contact).all()
for contact in contacts:
    print(contact.firstname)

contacts = session.query(Contact).filter_by(email="veit@cusy.io").all()
for contact in contacts:
    print(contact.firstname)

Update

contact = session.query(Contact).filter_by(email="veit@cusy.io").first()
contact.email = "info@veit-schiele.de"

session.add(contact)
session.commit()

Delete

contact = (
    session.query(Contact).filter_by(email="info@veit-schiele.de").first()
)

session.delete(contact)
session.commit()

Extensions

SQLAlchemy-Continuum
Versioning and revision extension for SQLAlchemy
SQLAlchemy-Utc
SQLAlchemy type for storing datetime.datetime values
SQLAlchemy-Utils
Various utility functions, new data types and utilities for SQLAlchemy
DEPOT
Framework for easy storage and retrieval of files in web applications
SQLAlchemy-ImageAttach
RSQLAlchemy extension for attaching images to entity objects
SQLAlchemy-Searchable
Full-text searchable models for SQLAlchemy
.. seealso::

   * `Awesome SQLAlchemy <https://github.com/dahlia/awesome-sqlalchemy>`_