sqlalchemy_mate_bulk_insert_and_smart_insert_quickstart.py

1import sqlalchemy as sa
2from sqlalchemy.orm import declarative_base
3from sqlalchemy_mate import EngineCreator, insert_all, smart_insert
4
5# 1. Define data model
6Base = declarative_base()
7
8class User(Base):
9    __tablename__ = "users"
10    id = sa.Column(sa.Integer, primary_key=True)
11    name = sa.Column(sa.String)
12
13# 2. Create engine and tables
14# This helper creates an in-memory SQLite database
15engine = EngineCreator().create_sqlite()
16Base.metadata.create_all(engine)
17
18# 3. Prepare data
19data = [
20    {"id": 1, "name": "Alice"},
21    {"id": 2, "name": "Bob"},
22]
23
24# 4. Use sqlalchemy-mate CRUD helpers
25# insert_all: inserts multiple records efficiently
26insert_all(engine, User, data)
27
28# smart_insert: only inserts records that don't already exist (prevents IntegrityError)
29new_data = [
30    {"id": 2, "name": "Bob"},      # Already exists, will be skipped
31    {"id": 3, "name": "Charlie"},  # New, will be inserted
32]
33smart_insert(engine, User, new_data)
34
35# 5. Verify results
36with engine.connect() as connection:
37    results = connection.execute(sa.select(User)).all()
38    for row in results:
39        print(f"ID: {row.id}, Name: {row.name}")