Query

Query#

In this page I’ll describe how to load records from the database using sqlalchemy.

Start container#

Container with a postgres database, and creating tables that will be useful for the examples described on this page.

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

CREATE TABLE test_table (
    numeric_var INT PRIMARY KEY,
    text_var VARCHAR
);

INSERT INTO test_table (numeric_var, text_var) VALUES
(1, 'a'),
(2, 'a'),
(3, 'b'),
(4, 'c');

CREATE TABLE
INSERT 0 4

Don’t forget to stop the container when you’ve finished playing with the examples on this page.

!docker stop read_table_example

read_table_example

Creating map#

It’s a preparatory step:

  • Create session;

  • Recreate the data model corresponding to the database you are working in.

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)
LocalSession = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
)
session = LocalSession()

Base = declarative_base()
class TestTable(Base):
    __tablename__ = "test_table"
    numeric_var = Column(Integer, primary_key = True)
    text_var = Column(String)

Extract all records#

You need:

  • Use session.query(<table class>) to extract values from a table related to <table class>;

  • session.query.all() returns a list of instances of <table class> corresponding to each record in the database:

    • In the example, the list was printed;

    • And in the example, all fields were printed.

query_res = session.query(TestTable)

print("list of TestTable -", query_res.all())

print("\nExtract fields:")
for record in query_res.all():
    print(record.numeric_var, record.text_var)

list of TestTable - [<__main__.TestTable object at 0x7f8fd0f6d720>, <__main__.TestTable object at 0x7f8fd0f6da50>, <__main__.TestTable object at 0x7f8fd0f6e530>, <__main__.TestTable object at 0x7f8fd0f6ee30>]

Extract fields:
1 a
2 a
3 b
4 c

Filters#

To add filters to the query, you need to call the fiter method on the result of the query method. As an argument you must pass conditions in syntax such as <table class>.<field name> == <value>.

So in the following example all these tips are used to query all records that have a as the value of the text_var.

for record in (
    session.
    query(TestTable).
    filter(TestTable.text_var=="a").
    all()
):
    print(record.numeric_var, record.text_var)

1 a
2 a
Contents