SQLAlchemy ORM - Adding Objects

In the previous chapters of SQLAlchemy ORM, we have learnt how to declare mapping and create sessions. In this chapter, we will learn how to add objects to the table.

We have declared Customer class that has been mapped to customers table. We have to declare an object of this class and persistently add it to the table by add() method of session object.

c1 = Sales(name = 'Ravi Kumar', address = 'Station Road Nanded', email = 'ravi@gmail.com')
session.add(c1)

Note that this transaction is pending until the same is flushed using commit() method.

session.commit()

Following is the complete script to add a record in customers table −

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   
   id = Column(Integer, primary_key=True)
   name = Column(String)
   address = Column(String)
   email = Column(String)
   
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

c1 = Customers(name = 'Ravi Kumar', address = 'Station Road Nanded', email = 'ravi@gmail.com')

session.add(c1)
session.commit()

To add multiple records, we can use add_all() method of the session class.

session.add_all([
   Customers(name = 'Komal Pande', address = 'Koti, Hyderabad', email = 'komal@gmail.com'), 
   Customers(name = 'Rajender Nath', address = 'Sector 40, Gurgaon', email = 'nath@gmail.com'), 
   Customers(name = 'S.M.Krishna', address = 'Budhwar Peth, Pune', email = 'smk@gmail.com')]
)

session.commit()

Table view of SQLiteStudio shows that the records are persistently added in customers table. The following image shows the result −