Creating Policies - SELECT

Read Data with SQLModel

Now let's do the same query to read all the policies, but with SQLModel.

Create a Session

The first step is to create a Session, the same way we did when creating the rows.

We will start with that in a new function select_policies():

# Code omitted ☝️

def select_policies():
    with Session(engine) as session:

# Code omitted 👇

Create a `select` Statement

Next, pretty much the same way you write a SQL SELECT statement above, now we'll create a SQLModel select statement.

First we have to import select from sqlmodel at the top of the file:

# Code omitted ☝️

from sqlmodel import Field, Session, SQLModel, create_engine, select

# Code omitted 👇

And then we will use it to create a SELECT statement in Python code:

# Code omitted ☝️

def select_policies():
    with Session(engine) as session:
        statement = select(Policy)

# Code omitted 👇

It's a very simple line of code that conveys a lot of information:

statement = select(Policy)

This is equivalent to the equivalent SQL SELECT statement

SELECT id, class_of_business, broker, premium_oc, curreny, is_cat_exposed
FROM policy

We pass the class model Policy to the select() function. And that tells it that we want to select all the columns necessary for the Policy class.

And notice that in the select() function we don't explicitly specify the FROM part. It is already obvious to SQLModel (actually to SQLAlchemy) that we want to select FROM the table policy, because that's the one associated with the Hero class model.

Tip

The value of the statement returned by select() is a special object that allows us to do other things.

I'll tell you about that in the next chapters.

Execute the Statement

Now that we have the select statement, we can execute it with the session:

# Code omitted ☝️

def select_policies():
    with Session(engine) as session:
        statement = select(Policy)
        results = session.exec(statement)

# Code omitted 👇

This will tell the session to go ahead and use the engine to execute that SELECT statement in the database and bring the results back.

Because we created the engine with echo=True, it will show the SQL it executes in the output.

This session.exec(statement) will generate this output:

INFO Engine BEGIN (implicit)
INFO SELECT policy.id, policy.class_of_business, policy.broker, policy.premium_oc, policy.currency, policy.is_cat_exposed 
FROM policy
INFO Engine [generated in 0.00015s] ()

The database returns the table with all the data, just like above when we wrote SQL directly:

Iterate Through the Results

The results object is an iterable that can be used to go through each one of the rows.

Now we can put it in a for loop and print each one of the heroes:

{ ./docs_src/space_dev/modelling/tutorial001.py ln[34:39] hl[38:39] }

This will print the output:

id=1 class_of_business='Property' broker='London Brokers' currency='USD' premium_oc=50000 is_cat_exposed=True
id=2 class_of_business='Marine' broker='Singapore Brokers' currency='USD' premium_oc=25000 is_cat_exposed=False
id=3 class_of_business='Property' broker='Australia Brokers' currency='AUD' premium_oc=10000 is_cat_exposed=False'

Add `select_policies()` to `main()`

Now include a call to select_policies() in the main() function so that it is executed when we run the program from the command line:

# Code omitted ☝️

def select_policies():
    with Session(engine) as session:
        statement = select(Policy)
        results = session.exec(statement)
        for policy in results:
            print(policy)

# Code omitted 👇

Review The Code

Great, you're now being able to read the data from the database! 🎉

Let's review the code up to this point:

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select  # (1)!


class Policy(SQLModel, table=True):  # (2)!
    id: Optional[int] = Field(default=None, primary_key=True)
    class_of_business: str
    broker: str
    premium_oc: int
    currency: Optional[str] = 'USD'
    is_cat_exposed: Optional[bool] = False


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)  # (3)!


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)  # (4)!


def create_policies():
    policy_1 = Policy(class_of_business='Property', broker='London Brokers', premium_oc=50000, is_cat_exposed=True)  # (5)!
    policy_2 = Policy(class_of_business='Marine', broker='Singapore Brokers', premium_oc=25000)
    policy_3 = Policy(class_of_business='Property', broker='Australia Brokers', premium_oc=10000, currency='AUD')

    with Session(engine) as session:  # (6)!
        session.add(policy_1)
        session.add(policy_2)
        session.add(policy_3)

        session.commit()


def select_policies():
    with Session(engine) as session:  # (7)!
        statement = select(Policy)  # (8)!
        results = session.exec(statement)  # (9)!
        for policy in results:  # (10)!
            print(policy)  # (11)!
    # (12)!


def main():
    create_db_and_tables()
    create_policies()
    select_policies()  # (13)!


if __name__ == "__main__":
    main()

Import from sqlmodel everything we will use, including the new select() function.
Create the Policy class model, representing the policy table.
Create the engine, we should use a single one shared by all the application code, and that's what we are doing here.
Create all the tables for the models registered in SQLModel.metadata.

This also creates the database if it doesn't exist already.
Create each one of the Policy objects.

You might not have this in your version if you had already created the data in the database.
Create a new session and use it to add the policies to the database, and then commit the changes.
Create a new session to query data.

/// tip

Notice that this is a new session independent from the one in the other function above.

But it still uses the same engine. We still have one engine for the whole application.

///
Use the select() function to create a statement selecting all the Policy objects.

This selects all the rows in the policy table.
Use session.exec(statement) to make the session use the engine to execute the internal SQL statement.

This will go to the database, execute that SQL, and get the results back.

It returns a special iterable object that we put in the variable results.

This generates the output:
```
INFO Engine BEGIN (implicit)
INFO SELECT policy.id, policy.class_of_business, policy.broker, policy.premium_oc, policy.currency, policy.is_cat_exposed 
FROM policy
INFO Engine [generated in 0.00015s] ()
```
Iterate for each Policy object in the results.

Print each policy.

The 3 iterations in the for loop will generate this output:

id=1 class_of_business='Property' broker='London Brokers' currency='USD' premium_oc=50000 is_cat_exposed=True
id=2 class_of_business='Marine' broker='Singapore Brokers' currency='USD' premium_oc=25000 is_cat_exposed=False
id=3 class_of_business='Property' broker='Australia Brokers' currency='AUD' premium_oc=10000 is_cat_exposed=False'

At this point, after the with block, the session is closed.

This generates the output:
```
INFO Engine ROLLBACK
```
Add this function select_heroes() to the main() function so that it is called when we run this program from the command line.

/// tip

Check out the number bubbles to see what is done by each line of code.

///

Here it starts to become more evident why we should have a single engine for the whole application, but different sessions for each group of operations.

This new session we created uses the same engine, but it's a new and independent session.

The code above creating the models could, for example, live in a function handling web API requests and creating models.

And the second section reading data from the database could be in another function for other requests.

So, both sections could be in different places and would need their own sessions.

/// info

To be fair, in this example all that code could actually share the same session, there's actually no need to have two here.

But it allows me to show you how they could be separated and to reinforce the idea that you should have one engine per application, and multiple sessions, one per each group of operations.

///