SQLAlchemy: How to Insert a Record into a Table

Introduction

SQLAlchemy is a powerful and flexible ORM tool for Python that enables developers to work with relational databases in a more pythonic manner. This tutorial walks you through the basics of inserting records into a database table using SQLAlchemy.

Setting up the Environment

Before we begin, ensure you have the following prerequisites installed:

• Python
• SQLAlchemy
• A database engine (e.g. sqlite, postgresql, mysql)

You can install SQLAlchemy using pip:

pip install sqlalchemy

Defining your Models

To start with, you need to define the schema of your database by creating models that represent tables:

from sqlalchemy import create_engine, Column, Integer, String, Numeric
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    price = Column(Numeric)

#create an engine
engine = create_engine('sqlite:///:memory:')

# create tables in the database
Base.metadata.create_all(engine)

Creating a Session

Next, you will need to create a session to interact with the database:

from sqlalchemy.orm import sessionmaker

# Session factory, bound to the engine
Session = sessionmaker(bind=engine)

# Create a new session
session = Session()

Inserting Records – Basic Example

With your session and models set up, you can now insert new records:

new_product = Product(name='Laptop', price=999.99)
session.add(new_product)
session.commit()

This inserts a single record into the ‘products’ table.

Inserting Multiple Records

SQLAlchemy lets you insert multiple records at once:

session.add_all([
    Product(name='Keyboard', price=29.99),
    Product(name='Mouse', price=19.99),
    Product(name='Monitor', price=199.99),
])
session.commit()

Dynamic Inserts with Dictionaries

If the input data is in a dictionary format, you can unpack it while creating an object:

data = { 'name': 'Desk', 'price': 120.00 }
new_product = Product(**data)
session.add(new_product)
session.commit()

Inserting with Relationships

When working with related tables, you can insert records that are connected:

class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    products = relationship('Product', backref='category')



#create category
new_category = Category(name='Electronics')
session.add(new_category)



#add product to category
new_product = Product(name='Smartphone', price=499.99, category=new_category)
session.add(new_product)
session.commit()

Inserting using the Core

SQLAlchemy also offers a lower-level ‘Core’ interface, which can be useful when working with bulk inserts:

from sqlalchemy import insert



stmt = insert(Product).values(name='Printer', price=149.99)
engine.connect().execute(stmt)

Advanced Techniques

Furthermore, SQLAlchemy supports advanced insert techniques including on-conflict updates (upserts), returning the primary key of the inserted record, and server-side defaults detection:

# Construct an insert statement with on-conflict update clause (for PostgreSQL)
stmt = insert(Product).values(name='Tablet', price=249.99).on_conflict_do_update(
    index_elements=['id'],
    set_={'price': insert(Product).excluded.price}
)

engine.connect().execute(stmt)

Handling Exceptions

It’s also important to handle exceptions when inserting to avoid crashes and to ensure data integrity:

from sqlalchemy.exc import IntegrityError



try:
    new_product = Product(name=None, price=59.99) # Violates the Non-Null constraint
    session.add(new_product)
    session.commit()
except IntegrityError as e:
    session.rollback()
    print("Insert failed: ", e)

Conclusion

In this tutorial, you’ve learned several methods to insert records into a table using SQLAlchemy, including the use of the ORM and Core interface. Understanding these basics paves the way for more complex database operations using Python and SQLAlchemy.