Add record

Add record#

In this page I’ll show you how to add a record to a database using sqlachemy.

Create database#

For this page postgres docker container is used. In the following cell I will create database schema just in sqlalchemy and push it into database.

!docker run -d --rm\
    --name add_record_example\
    -e POSTGRES_PASSWORD=postgres\
    -p 5000:5432\
    postgres:15.4 &> /dev/null
!sleep 5

from sqlalchemy import (
    create_engine, 
    Column, 
    Integer,
    String
)
from sqlalchemy.orm import sessionmaker, declarative_base

URL = "postgresql://postgres:postgres@localhost:5000/postgres"
engine = create_engine(URL)

SessionLocal = sessionmaker(
    autoflush=False,
    autocommit=False,
    bind=engine
)

# defining data model
Base = declarative_base()
class TestTable(Base):
    __tablename__="test_table"
    id = Column(Integer, primary_key=True)
    numeric_var = Column(Integer)
    text_var = Column(String)

# duplicate datamodel in the database
Base.metadata.create_all(engine)

Make sure that the created table is initially empty.

!docker exec add_record_example psql -U postgres -d postgres -c "SELECT * FROM test_table;"

 id | numeric_var | text_var 
----+-------------+----------
(0 rows)

Example#

To add a record to the database, you must use the session’s add method. As an argument you must pass an instance of the <table class> that describes the record.

In the following example, a few records with random content have been added to the database.

Note In this example, I don’t set a value for the id field of TestTable because it is the primary key and the table will set it itself.

Note At the end, session.commit is called - it is needed because of features of the session declaration.

from random import randint
session = SessionLocal()
for i in range(10):
    session.add(
        TestTable(
            numeric_var=randint(0,100),
            text_var="".join([chr(randint(97, 107)) for i in range(10)])
        )
    )
session.commit()

Let’s check the contents of the test_table.

!docker exec add_record_example psql -U postgres -d postgres -c "SELECT * FROM test_table;"

 id | numeric_var |  text_var  
----+-------------+------------
  1 |           6 | ejkagfkdgi
  2 |           3 | ddfaaedjfd
  3 |           4 | gjchfeahhi
  4 |          54 | kcfkbfjhjj
  5 |          56 | gbfejdaecf
  6 |          14 | cigagdihha
  7 |          49 | kfcfcjgkej
  8 |          65 | ajhahgcgff
  9 |          20 | jgchadeedj
 10 |          89 | jdkhfdefdc
(10 rows)

Stop container#

!docker stop add_record_example

add_record_example
Contents