SQLAlchemy Development Example

Tacnode supports application development with popular ORM (Object-Relational Mapping) frameworks. SQLAlchemy is a Python object-relational mapping tool. This article explains how to develop effectively using SQLAlchemy.

Preparation

Set up a database in Tacnode.

CREATE DATABASE example;

Create a table in the newly created database.

CREATE TABLE customer (
    id bigint NOT NULL,
    name text NOT NULL,
    email text NOT NULL,
    create_time timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

SQLAlchemy Examples

SQLAlchemy is an object-relational mapping solution for Python.

Import SQLAlchemy dependency.

pip install SQLAlchemy

Define entity classes. Each table corresponds to an entity class. In this case, we have just one customer table, requiring us to create only a Customer class. When defining an entity class, it is essential to establish the relationship between the fields and the database columns.

class Base(DeclarativeBase):
    pass
 
class Customer(Base):
    __tablename__ = "customer"
 
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    email: Mapped[str] = mapped_column(Text, nullable=False)
    create_time: Mapped[datetime] = mapped_column(DATETIME, default=datetime.now)
 
    def __str__(self):
        return f"id: {self.id}, name: {self.name}, email: {self.email}, create_time: {self.create_time}"

Set up the database connection settings.

host = "localhost"
port = 5432
username = "platformadmin"
password = "pOoFOBA1o!P7DfJaLx!"
database = "example"

Use the following code to insert two new records into the customer table. Then, query the table to confirm the changes. Update one of the records and verify the modification by querying the table again. Finally, delete a record and check the table to ensure the deletion was successful.

def insert_func(db_engine):
    with Session(db_engine) as session:
        first_customer = Customer(id=1, name="Jacob Emily", email="jacob.emily@tacnode.io")
        session.add(first_customer)
        second_customer = Customer(id=2, name="Michael Emma", email="michael.emma@tacnode.io")
        session.add(second_customer)
        session.commit()
 
def delete_func(db_engine):
    with Session(db_engine) as session:
        customer = session.get(Customer, 1)
        session.delete(customer)
        session.commit()
 
def update_func(db_engine):
    with Session(db_engine) as session:
        stmt = select(Customer).where(Customer.id == 2)
        customer = session.scalars(stmt).one()
        customer.email = "michael.emma@gmail.com"
        session.commit()
 
def select_func(db_engine):
    with Session(db_engine) as session:
        stmt = select(Customer).where(Customer.id.in_([1, 2]))
        for customer in session.scalars(stmt):
            print(customer)
 
if __name__ == '__main__':
    engine = create_engine(f"postgresql://{username}:{password}@{host}:{port}/{database}?sslmode=disable", echo=False)
 
# Insert
insert_func(engine)
 
# Select
print("Result of the first selection:")
select_func(engine)
 
# Update
update_func(engine)
 
# Select
print("Result of the second selection:")
select_func(engine)
 
# Delete
delete_func(engine)
 
# Select
print("Result of the third selection:")
select_func(engine)

The results are listed below:

Result of the first selection:
id: 1, name: Jacob Emily, email: jacob.emily@tacnode.io, create_time: 2023-10-29 00:46:18.951433
id: 2, name: Michael Emma, email: michael.emma@tacnode.io, create_time: 2023-10-29 00:46:18.951441
Result of the second selection:
id: 1, name: Jacob Emily, email: jacob.emily@tacnode.io, create_time: 2023-10-29 00:46:18.951433
id: 2, name: Michael Emma, email: michael.emma@gmail.com, create_time: 2023-10-29 00:46:18.951441
Result of the third selection:
id: 2, name: Michael Emma, email: michael.emma@gmail.com, create_time: 2023-10-29 00:46:18.951441

SQLAlchemy Development Example

Preparation

SQLAlchemy Examples

On this page