SQLAlchemy

SQLAlchemy is a Python ORM (Object-Relational Mapper) that allows you to work with database tables using Python objects and queries.

Instead of writing SQL for every operation, SQLAlchemy lets you define database tables as Python classes.

Code

from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String
)
from sqlalchemy.orm import declarative_base


DATABASE_URL = (
    "postgresql+psycopg://"
    "postgres:password@localhost/mydb"
)

engine = create_engine(DATABASE_URL)

Base = declarative_base()


class User(Base):

    __tablename__ = "users"

    id = Column(
        Integer,
        primary_key=True
    )

    name = Column(String)

    email = Column(String)

Understanding the Code

1. Import SQLAlchemy

First, import the required SQLAlchemy components:

from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String
)

Also import declarative_base:

from sqlalchemy.orm import declarative_base

Column, Integer, and String are used to define the structure and data types of database columns.

2. Define the Database URL

The PostgreSQL database connection URL is defined as:

DATABASE_URL = (
    "postgresql+psycopg://"
    "postgres:password@localhost/mydb"
)

This specifies that SQLAlchemy will use PostgreSQL with the psycopg driver.

3. Create the Database Engine

Create an SQLAlchemy engine:

engine = create_engine(DATABASE_URL)

The engine manages communication between SQLAlchemy and the database.

4. Create the Base Class

Base = declarative_base()

The base class is used by SQLAlchemy models.

5. Create the User Model

Define a Python class that represents the database table:

class User(Base):

    __tablename__ = "users"

The __tablename__ attribute specifies that this model represents the users table.

6. Define Columns

The id column is defined as an integer and primary key:

id = Column(
    Integer,
    primary_key=True
)

The name and email columns are strings:

name = Column(String)

email = Column(String)

Database Table

The model represents a table like:

users
-------------------------
id | name  | email
-------------------------
1  | Tarun | tarun@example.com

Summary

SQLAlchemy allows you to represent database tables as Python classes.

The basic structure is:

class User(Base):

    __tablename__ = "users"

    id = Column(
        Integer,
        primary_key=True
    )

    name = Column(String)

    email = Column(String)

This makes it easier to work with database tables using Python and SQLAlchemy’s ORM.