Basic SQL via Python: Using InterSystems IRIS as a Relational Database

sql = "SELECT * FROM Sample.Person WHERE ID = ? and Name = ?"
params = [1, 'Doe, John']
cursor.execute(sql, params)
result = cursor.fetchone()
row = result[:]
print(row)

sql = "SELECT * FROM Sample.Person WHERE ID = :id and Name = :name"
params = {'id' : '1', 'name' : 'Doe, John'}
cursor.execute(sql, params)
result = cursor.fetchone()
row = result[:]
print(row)

# engine.begin starts a transaction block with an auto-commit
with engine.begin() as conn:
    result = conn.execute(text("SELECT Name, Age, FROM Sample.Person"))
    for row in result:
        print(f"Name: {row.Name}\nAge: {row.Age}\n")

# engine.connect starts a transaction block with an automatic roll back
with engine.connect() as conn:
    conn.execute(demo_table.insert(), [{"id": 1, "value": "Test"}],)
    conn.commit()		# Need to explictly commit to save changes

# Session starts an ORM session with fine-grained commit/rollback controls
from sqlalchemy.orm import Session

print("Listing rows where Age > 50:")
stmt = text("SELECT Name, Age FROM Sample.Person WHERE Age > :min_age ORDER BY Age, Name")
with Session(engine) as session:
    result = session.execute(stmt, {"min_age", 50})
    for row in result:
        print(f"Name: {row.Name}\nAge: {row.Age}\n")

from sqlalchemy import Column, MetaData, Table
from sqlalchemy.sql.sqltypes import Integer, VARCHAR

# Create a table metadata
metadata = MetaData()

# Define the table structure
demo_table = Table(
    "demo_table",
    metadata,
    Column("id", Integer, primary_key = True, autoincrement = True),
    Column("value", VARCHAR(50)),
)

# Insert sample data
with engine.connect() as conn:
    conn.execute(
        demo_table.insert(),
        [
            {"id": 1, "value": "Test"},
            {"id": 2, "value": "More"},
        ],
    )
    conn.commit()
    result = conn.execute(demo_table.select()).fetchall()
    print("result:", result)

from typing import List, Optional
from sqlalchemy import String, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __table__name = "user_account"

    # 'id' column is the primary key of the table    
    id: Mapped[int] = mapped_column(primary_keys=True)
    
    # 'name' column has a maximum string length of 30 characters
    name: Mapped[str] = mapped_column(String(30))
    
    # 'fullname' column is optional, so it can be NULL in the database
    fullname: Mapped[Optional[str]]

    # String representation of the object; useful for debugging
    def __repr__(self) ->:
        return f"User(id = {self.id!r}, name = {self.name!r}, fullname = {self.fullname!r})"

columnName: Mapped[type] = mapped_column(db.Type, arguments)

Base.metadata.create_all(engine)	# Create the table if it does not exist

with Session(engine) as session:
    # Create new instances of the User class
    frodo = User(
        name = "frodo",
        fullname = "Frodo Baggins"
    )
    samwise = User(
        name = "samwise",
        fullname = "Samwise Gamgee"
    )
    pippin = User(
        name = "pippin", 
        fullname = "Pippin Took")

    # Add all three new User objects to the session (prepares them to be inserted)
    session.add_all([frodo, samwise, pippin])
    
    # Commit the session; writes the changes (inserts) to the database
    session.commit()

from sqlalchemy import select 	# 'select' is used to build SQL SELECT queries

with Session(engine) as session:
    # Print users with specific names
    stmt = select(User).where(User.name.in_(["frodo", "samwise"]))
    
    # 'scalars()' extracts the actual User objects from the result
    for user in session.scalars(stmt):
        print(user)

with Session(engine) as session:
    # List of IDs to delete
    ids_to_delete = [1, 2, 3]

    for user_id in ids_to_delete:
        user = session.get(User, user_id)
        if user:
            session.delete(user)
    session.commit()