SQLAlchemy ORM – Adding Objects
Last Updated :
18 Mar, 2022
In this article, we will discuss how to add objects in the SQLAlchemy ORM.
The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables and instances of those classes (objects) with rows in their corresponding tables. For this article, we’re going to use the Postgres database. You can also use an in-memory-only SQL database.
Make sure you’ve properly installed sqlalchemy if not then install it with:
pip install sqlachemy
For example, You’ve designed an API that stores and fetches the posts created by the user in the database, somewhat like GFG, Instagram, etc. This is the class that is mapped to our database table “posts”
Python3
from sqlalchemy import Column, Integer, String, Boolean
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Post(Base):
__tablename__ = "posts"
id = Column(Integer, primary_key = True , nullable = False )
title = Column(String, nullable = False )
content = Column(String, nullable = False )
published = Column(Boolean, server_default = 'true' , nullable = False )
|
Stepwise Implementation
Step 1: Database related configuration
Python3
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine(DB_URL)
local_session = sessionmaker(autoflush = False ,
autocommit = False , bind = engine)
db = local_session()
|
Step 2: To add a new object (post)
Here, we are creating an object, and then with the use of the db.add() function, we have added the created object to the database.
Python3
post = Post(title = "GFG Article" ,
content = "How to add SQL Alchemy objects" ,
published = True )
db.add(post)
|
As you can see above post is not saved to the database till you committed it like,
Python3
db.commit()
db.close()
print ( "Successfully added a new post" )
|
Note: Every time you made changes make sure that you’ve committed the transaction, otherwise the transaction is pending.
After committing the transaction you’ve successfully saved a new object to your database, you can query the database regarding the changes
Step 3: Querying the database
Under this, we are verifying if the object is successfully added or not. If it is added then the database will show the same object else it won’t be present in the database.
SELECT * FROM posts;
And you get all your posts saved in your local database.
Complete Script to add new objects to the database:
Python3
from sqlalchemy import Column, Integer, Boolean, String
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Post(Base):
__tablename__ = "posts"
id = Column(Integer, primary_key = True , nullable = False )
title = Column(String, nullable = False )
content = Column(String, nullable = False )
published = Column(Boolean, server_default = 'true' , nullable = False )
engine = create_engine(DB_URL)
local_session = sessionmaker(autoflush = False , autocommit = False , bind = engine)
db = local_session()
post = Post(title = "GFG Article" ,
content = "How to add SQL Alchemy objects" , published = True )
db.add(post)
db.commit()
db.close()
print ( "Successfully added a new post" )
|
Output:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...