SQL Alchemy ORM for Postgresql

Flask-Philo uses SQLAlchemy as its Object Relational Mapper (ORM). For mode detail and documentation on SQL Alchemy, visit https://www.sqlalchemy.org/

Where is Flask-Philo’s PostgreSQL ORM implementation?

Flask-Philo’s PostgreSQL ORM can be found at:

https://github.com/Riffstation/flask-philo/tree/dev/flask_philo/db/postgresql

Database Settings

The first thing you need to do is to add the relevant configuration to your application’s settings file, typically <your_app>/config/development.py :

DATABASES = {
    'POSTGRESQL': {
        'DEFAULT': 'postgresql://user:password@host:port/database_name',
    },
}

Models

In order to create Flask-Philo Models, simply create classes that inherit from Flask-Philo’s BaseModel class:

flask_philo.db.postgresql.orm.BaseModel

BaseModel exposes a number of useful methods for retrieving and manipulating data:

  • add() - create a new Flask-Philo class instance (ORM object)
  • update() - modify an existing ORM object
  • delete() - delete an ORM object
  • objects.get(key=value) - retrieve an ORM object by a specified key
  • objects.filter_by(key=value) - retrieve a collection of filtered objects by a specified key/keys
  • objects.count() - count all object instances of a Flask-Philo class
  • objects.raw_sql(sql_query_string) - run direct SQL queries on your application’s database

…examples for each of these methods are included in the Data Manipulation Examples section below

Example Models

Here are 3 examples of models with some simple properties and examples of how you can use them in your application:

from flask_philo.db.postgresql.orm import BaseModel

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship


class Artist(BaseModel):
    __tablename__ = 'artist'
    name = Column(String(256), nullable=False)
    description = Column(String(256))
    albums = relationship('Album', backref='artist')
    is_famous = Column(Boolean, default=False)
    genre_id = Column(Integer, ForeignKey('genre.id'))


class Album(BaseModel):
    __tablename__ = 'album'
    name = Column(String(256), nullable=False)
    description = Column(String(256))
    artist_id = Column(Integer, ForeignKey('artist.id'))


class Genre(BaseModel):
    __tablename__ = 'genre'
    name = Column(String(256), nullable=False)
    description = Column(String(256))

Flask-Philo’s ORM automatically handles the creation of each model’s integer id property, along with automatically creating and updating the timestamp fields created_at and updated_at

Foreign Keys may be defined using the ForeignKey(<key_name>) syntax, as is the case in our example Album model above. Here, the Album model references the Artist model (DB table artist) as a foreign key using artist.id

Fields

Field types

The most common field types are:

  • Column: defines the properties of a given column
  • relationship: defines the relationship between two tables

Examples:

class Artist(BaseModel):
    __tablename__ = 'artist'
    name = Column(String(256))
    description = Column(String(256))
    albums = relationship('Album', backref='artist')
    genre_id = Column(Integer, ForeignKey('genre.id'))

Supported data types

  • String: stores string format data
name = Column(String(256))
  • Integer: stores integer format data
amount = Column(Integer)
  • Boolean: stores boolean format data
is_famous = Column(Boolean, default=False)
  • Numeric: store numbers with a very large number of digits. Scale is the count of decimal digits in the fractional part. Precision refers to the total count of digits in the whole number.
tempo = Column(Numeric(precision=32, scale=16))
  • ARRAY: store array data
possible_names = Column(ARRAY(String(256)))
  • JSON: stores JSON format data
config_dict = Column(JSON)
  • Enum: provides a set of possible string values that work as constraints for the given column.
day = Column(
    Enum(
        'sunday', 'monday', 'tuesday', 'wednesday', 'thursdat', 'fruday',
        'saturday', name="days_of_the_week"))

Field options

The following ORM constraints can be set in your Flask-Philo Model:

  • PrimaryKey: specifies that a given column is a primary key. As such, it is unique and not nullable.
id = Column(Integer, primary_key=True)
  • ForeignKey: specifies a column that acts as foreign key, thereby defining a relationship with another table
genre_id = Column(Integer, ForeignKey('genre.id'))
  • unique: specifies that a column must have a unique value for each record
name = Column(String(256), unique=True)
  • nullable: specifies if a column accepts null values or not
name = Column(String(256), nullable=False, unique=True)
  • default: defines a default value in the case that it is not specified
is_famous = Column(Boolean, default=False)

Database DML Operations

Postgresql Connection Pool

As a design decision, management of the PostgreSQL connection is the responsability of the developer, but this is made simple with Flask-Philo’s built-in connection management methods.

  • to instantiate a DB session, we use Flask-Philo’s get_pool() method
  • after modifying, creating or removing data in a session, we must commit or rollback the session using Flask-Philo’s pool.commit() or pool.rollback() methods

Opening a Flask-Philo DB session

from flask_philo.db.postgresql.connection import get_pool
pool = get_pool()
# Do some ORM operations here
pool.commit()

The following examples demonstrate each of the core ORM operations you will commonly use to query the PostgreSQL database

Data Manipulation Examples

Adding a new record

In this example, we create a new Genre using the same model defined in the Example Models section:

pool = get_pool()
rock = Genre(name='Rock', description='Rock and Roll')
rock.add()

At this point, we have added a new instance of the Genre model to our DB session, but we still need to either commit() or rollback() the insert operation

To commit the operation and create a new record:

pool.commit()

…alternatively, if the record is not needed the transaction can be rolled-back, and nothing will be changed in the PostgreSQL database:

pool.rollback()

Retrieving a specific record

Now that we’ve created and committed our new ‘Rock’ genre, we can retrieve the record directly from the database by using the objects.get() function:

genre_obj = Genre.objects.get(name="Rock")
genre_name = genre_obj.name
genre_id = genre_obj.id
print("Genre", genre_id, ":", genre_name)   # Will print "Genre 13 : Rock"

…we can also retrieve a record that matches multiple field values:

genre_obj = Genre.objects.get(id=13, name="Rock")
print("Genre", genre_obj.id, ":", genre_obj.name)   # Will print "Genre 13 : Rock"

Filtering records

We may also use Flask-Philo’s filter_by() function to filter records and retrieve a collection of all matching instances of the desired model.

Continuing our Genre example from earlier sub-sections:

genre_collection = Genre.objects.filter_by(name="Rock")
genre_obj = genre_collection.first()
print("Genre", genre_obj.id, ":", genre_obj.name)   # Will print "Genre 13 : Rock"

Updating a record

Just as we can retrieve a record, we can update records in a similar manner:

genre_obj = Genre.objects.filter_by(name="Rock").first()
genre_obj.name = "Metal"
genre_obj.update()
pool.commit()

updated_genre_obj = Genre.objects.filter_by(name="Metal").first()
print("Genre", updated_genre_obj.id, ":", updated_genre_obj.name)   # Will print "Genre 13 : Metal"

Deleting a record

In the same way we’ve added and updated a record, we can also delete it:

genre_obj = Genre.objects.filter_by(name="Metal").first()
genre_obj.delete()
pool.commit()

genre_obj = Genre.objects.filter_by(name="Metal").first()   # genre_obj == None

..once we have committed the delete() operation, this record no longer exists in our PostgreSQL DB.

Counting records

To count the number of instances of a given Model, we can use the objects.count() method.

genre_count = Genre.objects.count()
print(genre_count, "Genres present")  # Will print "13 Genres present"

Querying using Raw SQL

While the use of SQLAlchemy ORM will automatically translate Flask-Philo method calls to their corresponding PostgreSQL queries, we also provide a means of directly querying our underlying PostgreSQL database with a raw SQL query.

By passing a valid SQL query-string to the objects.raw_sql() method, we can retrieve or update data explicitly, as is the case in the following examples:

Retrieving data by raw SQL:

raw_sql_genre_result = Genre.objects.raw_sql("SELECT description FROM genre WHERE name='Rock';").fetchone()
genre_description = raw_sql_genre_result.description
genre_name = raw_sql_genre_result.name
print(genre_name, "genre description :", genre_description) # Will print "Rock genres description : Rock and Roll"

Modifying data by raw SQL:

query_string = "UPDATE genre SET name='Indie' WHERE id = 13"
Genre.objects.raw_sql(query_string)

Data manipulation with Relationships

The following example demonstrates the creation and retrieval of objects for two related models, Album and Artist, as defined in the Example Models section above

# Create and commit an artist record
floyd_artist_obj = Artist(name='Pink Floyd')
floyd_artist_obj.commit()
pink_floyd_id = floyd_artist_obj.id
pool.commit()

# Create and commit a related album
dark_album_obj = Album(
    artist_id=pink_floyd_id, name='Dark side of the moon')
dark_album_obj.add()
pool.commit()

# Create and commit another related album by the same artist
wall = Album(
    artist_id=pink_floyd_id, name='The Wall',
    description='Interesting')
wall.add()
pool.commit()

# Retrieve all albums by Pink Floyd
album_results = Album.objects.filter_by(artist_id=pink_floyd_id)
for album_obj in album_results:
    print("Pink Floyd album :", album_obj.name)

# Will print:
# Pink Floyd album : Dark side of the moon
# Pink Floyd album : The Wall

Using multiple Postgresql databases

Flask-Philo allows you to connect to multiple PostgreSQL database instances from the same application.

To take advantage of this feature, simply add a DATABASES block in an application configuration file in src/config.

Here’s an example of a typical configuration file:

DATABASES = {
    'POSTGRESQL': {
        'DEFAULT': 'postgresql://user:password@host:port/database_name',
        'MUSIC_CATALOG': 'postgresql://user:password@host:port/songs_database_name',
    }
}

…with this configuration in place, we can now access a specific database while using Flask-Philo’s get_pool() method:

from flask_philo.db.postgresql.connection import get_pool
pool = get_pool()

# Add a Genre object to our session
blues_obj = Genre(name='Blues', description='Still got the blues')
blues_obj.add(connection_name='MUSIC_CATALOG')

# Commit changes to the MUSIC_CATALOG database
pool.commit(connection_name='MUSIC_CATALOG'))

The connection_name==DB_NAME parameter may be specified for all other common ORM methods in Flask-Philo