10 min read Author: klaas

Working with SQL relations

It's really simple to work with SQL relations in PythonOnWheels

Just add a decorator to your model class, generate the migrations and that's it.

@relation.has_many_and_belongs_to("comments")
class Post(Base):
# All your Post model code below here ..
.....

In this case the Post models has many Comments and you have a backtracking from the Comments model to the Posts they belong to.  The schema change will be automatically mapped to the DB (SQLite, Postgres, MySQL, MariaDb, Oracle, MSSQL ...) and to all related comment Models. 

The best thing is that this will also create the new attributes in the Todo (comments list) and Comment (todo ref) model classes that we can use to programmatically access the relation in python. The todo.comments attribute is a python list where you can easily append comments like this:

todo.comments.append(a_comment)

 If you save the todo using todo.upsert() the comments will be extracted and placed in the comments table, linked to the right todo.id. We will test this in a practical example below. 

Generating the migrations as well as applying the changes to the DB are, just as usual, done by scripts.

By default PythonOnWheels supports the following relations:

  • has_many() 
  • many_to_many()
  • has_many_and_belongs_to()
  • one_to_one()
  • tree() 

Background

Database relations are a pretty common use case when you're working with SQL Databases.  The provide

a logical connection between different tables, established on the basis of interaction among these tables. 

So usually you link the information stored in two tables together. Examples are common things like:

  • attaching comments to posts (has_many_and_belongs_to)
  • attaching users to roles (many_to_many)

So why not just adding a list[] of comments to a Post ?

Since relational DBs do not support a native list[] type the only thing you cany do is to use a workaround. Like storing a separated list of Strings in a table column <comments>. But this has many drawbacks. The most important one is that you cannot easily search for particular parts of comments in this case. Think of updates, links to users who posted that comment. This is exaclty where relationships come into play

Managing relationships is not so easy and sometimes painful to do. 

  • You have to create table schemas.
  • Add the right relationship attributes (Foreign_keys..)
  • Add the right (bak) references to the models
  • Apply the schemas to the Database
  • Construct the actual instances linked together when working with python object ..

So this is all done for you in the background by PythonOnWheels!

Of course it relies on superb and proven python libraries like sqlalchemy, alembic and cerberus schemas. But the handling is automated by PoW for you.  The vision is:

Make you focus on your app and not on the boilerplate

So let's work through a simple real life example.

As you probably know from the other docs. These are all working examples, so if you want to, you can just copy & paste the code snippets to get a working real life application.

We will use SQlite for our example here because this comes integrated without any need for installations. 

So let us enhance our Todo App from the getting_started tutorial with attached comments.

We will start from scratch since this is only a matter of minutes (That's the idea of PoW)

The Basics

Install PythonOnWheels

pip install -U pythononwheels

Generate a new app (we wil call it relationstest)

generate_app -n relationstest -p <the_path_you_prefer>
virtualenv <the_path_you_prefer>/relationstest 

cd into the new project, activate the virtualenv and install the requirements.

pip install -r requirements.txt

Generate the Todo and Comments Model

We could do this step by step and apply the migrations one after another but to keep this hands-on tutorial focused we generate both now.

python generate_model -n todo -t sql

This generates an PythonOnWheels model named todo.py in your relationstest/models/sql directory.

It looks like this. We will leave it that way for this test.

@relation.setup_sql_schema()
class Todo(Base):
schema = {
'title' : { 'type' : 'string', 'maxlength' : 35 },
'text' : { 'type' : 'string' },
"votes" : { "type" : "integer", "default" : 0 }
}

Now the Comment Model

python generate_model.py -n comment -t sql

Generates the same default model schema as above, named Comment. Just look at relationstest/models/sql/comment.py

Normally you can specify the specific Database you want to use, Server, port,  user and stuff in you app/config.py file. But for SQlite everything is pre configured. The default DB name is db.sqlite, that's fine. So no need to change anything for this test. The only thing we have to do is to init the SQL environment once. 

python init_sqldb_environment.py

This will setup the right environment for sqlachemy/alembic. You should see the following output

----------------------------------------------------------------------
updated migration environment: sqlite:///your/path/relationstest/db.sqlite
----------------------------------------------------------------------

Now let's create the actual relations between our two models

Open the Todo model (app/models/sql/todo.py) in the editor of your choice. You will see that there is already a commented example for a relation, to give you an easy start.

#@relation.has_many("<plural_other_models>")
@relation.setup_sql_schema()
class Todo(Base):
...

We are looking at the @relation.has_many() here. But since we want a two-way relation between Todos and Comments uncomment and change it to: has_many_and_belongs_to(),  like this:

@relation.has_many_and_belongs_to("comments")
@relation.setup_sql_schema()
class Todo(Base):
...

What the decorator does for us in the background is:

  • create the concrete SQL relation using FOREIGN_KEY and todo.id
  • link the two python model classes 
  • adds a comments attribute (list) to the python Todo model
  • adds a todo.id attribute to the Comment model

And thats's it. As simple as that!

Now we can generate our first DB migration to apply the schema to the DB.

python generate_migration.py -n "added a todo and comments model"

You see the according output.

['comment', 'todo']
setup_schema:comment
setup_schema:todo
RELATION: I see a: Todo has many: comments
.. and comments belongs_to todo
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'todos'
INFO [alembic.autogenerate.compare] Detected added table 'comments'
Generating c:\khz\devel\relationstest\migrations\versions\fb2d7c0eb83f_added_a_todo_and_comments_model.py ... done
rev: fb2d7c0eb83f
path: c:\khz\devel\relationstest\migrations\versions\fb2d7c0eb83f_added_a_todo_and_comments_model.py

You can see that the relation was auto detected:

RELATION: I see a: Todo has many: comments
.. and comments belongs_to todo

And this is how the (comments part) of the generated SQL schema looks like:

op.create_table('comments',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('last_updated', sa.DateTime(), nullable=True),
sa.Column('title', sa.String(length=35), nullable=True),
sa.Column('text', sa.String(), nullable=True),
sa.Column('votes', sa.Integer(), nullable=True),
sa.Column('todo_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['todo_id'], ['todos.id'], ),
sa.PrimaryKeyConstraint('id')
)

We can see the FOREIGN_KEY reference sa.ForeignKeyConstraint(['todo_id'], ['todos.id'] pointing to the todos table.

You can find the actual generated migrations file in the path above app/migrations/versions but we'll leave that for now.

Last step is to apply the schema to the Database.

We use the update_db.py script to apply the schema changes to the DB. You can give it a direction (-d) up or down. We migrate up (forward) and apply all unprocessed migrations. Which is in our case the one we just created.

python update_db.py -d up

Output:

['comment', 'todo']
setup_schema:comment
setup_schema:todo
RELATION: I see a: Todo has many: comments
.. and comments belongs_to todo
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> fb2d7c0eb83f, added a todo and comments model

Ready to save Todos and Comments

Open the python interpreter and import the todo model. 

>>> from relationstest.models.sql.todo import Todo

And again, the output show immediately that there is a relation. PythonOnWheels also makes sure that the related model schema is also loaded.

setup_schema:todo
setup_schema:comment
RELATION: I see a: Todo has many: comments
.. and comments belongs_to todo

Create a Todo instance

>>> t=Todo()

And print the model. (Just type t <return> in the console)

>>> t
id : None (primary key)
created_at : None
last_updated : None
title :
text :
votes : 0
comments : ONETOMANY relationship with <model Comment>

As already mentioned you can see the todo.comments attribute which we have not defined in our schema. It also shows you that it's a relation attribute. If we check the actual python type we can see that it's a (sqlalchemy) list. 

>>> type(t.comments)
<class 'sqlalchemy.orm.collections.InstrumentedList'>

So let's create a Comment as well and add it to our Todo

>>> from relationstest.models.sql.comment import Comment
>>> c=Comment()

>>> c

id                  : None (primary key)
created_at : None
last_updated : None
title :
text :
votes : 0
todo_id : None (todos.id)

You can see the automatically created Comment.todo_id attribute giving us the backref to the Todo model this comment belongs to. Let's give out comment a title.

>>> c.title="a first comment"

Now add the comment to our Todo

>>> t.comments.append(c)

Lets check if it worked. Print the Todo again 

>>> t.comments
[id : None (primary key)
created_at : None
last_updated : None
title : a first comment
text :
votes : 0
todo_id : None (todos.id)
todo : MANYTOONE relationship with <model Todo>

]

You can see that the comment instance is printed (compare the title we just added)

Finally give our Todo a title and save it to the Database.

>>> t.title="Our first todo"

Save it:

>>> t.upsert()

Since we appended our comment to the todo the comment will be saved here as well. That is the important point of this.

Check the DB: (I use SQLite Viewer /Chrome)

Open your relationstest/db.sqlite file with the viewer of your choice (or just belive me ;). You can see that both models are saved to their own tables and the comment has also the Foreign_key reference to the correct todo.id

Of course this also works the other way round. So when you query for the todo with id==1 the related comment will also be loaded and appended to the todo.comments[] list.

So that's really it.

You can read more about SQL models here, add an API by generating a handler or go to the PythonOnWheels documentation.

Enjoy.

Especially feel very free to tweet questions, bugs .. anything to @pythononwheels.