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 # Declare Mapping Base = declarative_base() # This is the class which is mapped to "posts" # table to our database 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 # Syntax of database url = "<database_vendor_name>: # //<username>:<password>@ip-address/hostname/ # <database_name>" engine = create_engine(DB_URL) local_session = sessionmaker(autoflush=False, autocommit=False, bind=engine) # With this we get a session to do whatever # we want to do 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
# New post created by a user, assumes # you get this from the frontend 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
# To store the object to the database, # otherwise the transaction remains pending db.commit() # After performing transaction, we should # always close our connection to the database # It's a good practice and we must follow it 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 # Declare Mapping Base = declarative_base() # This is the class which is mapped to "posts" # table to our database 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) # Syntax of database url = "<database_vendor_name>:// # <username>:<password>@ip-address/hostname/<database_name>" engine = create_engine(DB_URL) local_session = sessionmaker(autoflush=False, autocommit=False, bind=engine) # With this we get a session to do whatever we # want to do db = local_session() # New post created by a user, assumes you get this # from the frontend post = Post(title="GFG Article", content="How to add SQL Alchemy objects", published=True) db.add(post) db.commit() # After performing transaction, we should always close # our connection to the database db.close() print("Successfully added a new post") |
Output:
