SQL Alchemy


Updated Jul 31st, 2023

SQLAlchemy is a popular Python library that provides an Object-Relational Mapping (ORM) tool for working with relational databases. It allows you to interact with databases using Python objects and provides a high-level, abstracted interface to perform CRUD (Create, Read, Update, Delete) operations without writing raw SQL queries.

Overview of SQLAlchemy:

  1. Core and ORM: SQLAlchemy consists of two main components – the Core and the ORM. The Core provides a low-level SQL abstraction and allows you to write SQL expressions and execute raw SQL queries. On the other hand, the ORM provides a high-level, object-oriented interface for interacting with databases using Python classes and objects.
  2. Connection Management: SQLAlchemy manages database connections efficiently, including connection pooling, so you don’t have to worry about managing connections manually.
  3. Database Support: SQLAlchemy supports various relational databases like MySQL, PostgreSQL, SQLite, Oracle, and more, allowing you to switch between different database backends with minimal changes to your code.
  4. Declarative Syntax: The ORM component of SQLAlchemy allows you to define your database models using Python classes and declarative syntax. This makes it easy to define the structure of your database and relationships between different tables.
  5. Query Building: SQLAlchemy provides a powerful query builder API, allowing you to construct complex SQL queries using Python syntax and chaining methods.
  6. Transactions: SQLAlchemy allows you to work with database transactions, ensuring that a series of database operations either all succeed or all fail.

Most common methods in SQLAlchemy (ORM):

  1. session.add(): Adds an object to the session’s unit of work, meaning it will be persisted to the database upon committing the session.
  2. session.query(): Creates a new query object that allows you to fetch data from the database using Pythonic syntax.
  3. session.commit(): Commits the current transaction to the database, making the changes permanent.
  4. session.rollback(): Rolls back the current transaction, undoing any changes made since the last commit.
  5. session.delete(): Marks an object for deletion in the database. The object will be deleted upon committing the session.
  6. session.merge(): Merges changes from a detached object into the session’s unit of work, updating the corresponding object in the database.
  7. session.expunge(): Removes an object from the session, making it “detached” from the session and no longer tracked for changes.
  8. session.query.filter(): Adds filtering criteria to the query to retrieve specific data from the database.
  9. session.query.join(): Performs a join operation between two or more tables in the database.

These are just a few of the common methods provided by SQLAlchemy. The library is quite extensive and offers many more features for working with databases in Python.