What transaction management commands do you know?

In SQL:

• BEGIN TRANSACTION (or BEGIN WORK, START TRANSACTION): Initiates a new transaction.
• COMMIT: Saves all changes made in the current transaction and ends it.
• ROLLBACK: Cancels all changes made in the current transaction and returns the database to the state before the transaction started.
• SAVEPOINT: Sets a save point within a transaction, to which you can roll back using ROLLBACK TO SAVEPOINT.

In Python, when working with databases (for example, using modules like sqlite3 or psycopg2), transaction management is often handled through the connection object:

# Example with sqlite3
import sqlite3

conn = sqlite3.connect('mydatabase.db')
try:
    conn.execute("INSERT INTO users (name) VALUES ('Alice')")
    conn.commit() # Commit changes
except Exception as e:
    print(f"Error: {e}")
    conn.rollback() # Roll back changes on error
finally:
    conn.close()

# Example with psycopg2
import psycopg2

conn = psycopg2.connect("dbname=mydatabase user=myuser password=mypassword")
try:
    with conn.cursor() as cur:
        cur.execute("INSERT INTO products (name) VALUES (%s)", ('Laptop',))
    conn.commit() # Commit changes
except Exception as e:
    print(f"Error: {e}")
    conn.rollback() # Roll back changes on error
finally:
    conn.close()

Some ORMs (like SQLAlchemy) provide their own abstractions for transaction management through sessions.

# Example with SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

engine = create_engine('sqlite:///mydatabase.db')
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

try:
    new_user = User(name='Bob')
    session.add(new_user)
    session.commit() # Commit changes
except Exception as e:
    print(f"Error: {e}")
    session.rollback() # Roll back changes on error
finally:
    session.close()