Create table

Create table#

On this page I will show how to move the data model declared in sqlalchemy to a database.

Create container#

For example, the postgres container is used. It also shows that in the initial list of tables is emtpy.

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

docker exec create_table_example \
    psql -U postgres -d postgres -c "\dt"

Did not find any relations.

Python code#

In the following cell, the data model is defined and moved to the database. Key code is Base.metadata.create_all(...) - duplicate data model in the database.

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)

Check result#

The following cell from the container with database runs:

  • \dt to list created tables;

  • SELECT * FROM test_table; - to get head of the created table.

The results in the database correspond to the declared data model.

%%bash
docker exec -i create_table_example \
    psql -U postgres -d postgres

\dt
SELECT * FROM test_table;

           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | test_table | table | postgres
(1 row)

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

Stop the container#

!docker stop create_table_example

create_table_example
Contents