Build a simple web application with SQLAlchemy and Redis support using BottlePy

Table of Contents

• Requirements
• The application stack
• Data used in this example site
• Setup the environmant
• Download sample data
• Start the environment
• Application Overview
• App configuration
• DB configuration
• SQLAlchemy plugin
• Redis Cache
• Data export
• Data import

Requirements

To use this environment you needDocker anDocker compose installed.

The application stack

Backend:

• BottlePy
• SQLAlchemy

Frontend:

• Bootstrap 5
• jQuery

Database:

• PostgreSQL
• Redis

Webserver:

• Nginx - Only Prod env.

Data used

To use this example application we need to import some example data. This example application use the "Stack Exchange Data Dump" available onarchive.org.

All the data used by this site is under thecc-by-sa 4.0 license.

Setup the environmant

First of all clonethis repository

git clone https://github.com/garutilorenzo/simple-bottlepy-application.gitcdsimple-bottlepy-application

Now we are ready to setup our environment. For dev purposes link the docker-compose-dev.yml do docker-compose.yml

ln-s docker-compose-dev.yml docker-compose.yml

For prod environments:

ln-s docker-compose-dev.yml docker-compose.yml

The difference between dev and prod envs are:

Now we can download some sample data.

Download sample data

To dwonload some example data run:

./download_samples.sh

By default the archive with the 'meta' attribute will be downloaded. If you want more data remove in download_samples.sh 'meta' form the archive name.

Small data:

forsampleinworkplace.meta.stackexchange.com.7z unix.meta.stackexchange.com.7z

Big data:

forsampleinworkplace.stackexchange.com.7z unix.stackexchange.com.7z

Note not all the stackexchange sites where imported on this example. After you choose the archives you will download adjust the network.py schema under src/schema/

classSites(enum.Enum):vi='vi.stackexchange.com'workplace='workplace.stackexchange.com'wordpress='wordpress.stackexchange.com'unix='unix.stackexchange.com'tex='tex.stackexchange.com'

Once the data is downloaded we can import the data:

docker-compose run--rm bottle bashweb@4edf053b7e4f:~/src$ python init_db.py# <- Initialize DBweb@4edf053b7e4f:~/src$ python import_data.py# <- Import sample data

Now for each data sample you have downloaded (Eg. tex, unix, vi) a python subprocess is started and will import in order:

• all the tags
• all the users
• all the posts
• all the post history events

Once the import is finished we can start our environment

Start the environment

With our DB populated we can now start our web application:

docker-compose up-dCreating network"bottle-exchange_default" with the default driverCreating postgres ...doneCreating redis    ...doneCreating bottle   ...done

The application will be available athttp://localhost:8080 for the dev andhttp://localhost for the prod

Application Overview

Index

Home page with a search form. On every change on the "Network" select, the "Tags" select is populated by an ajax call on /api/autocomplete/form/get_tags (POST) (for more details see src/bottle/static/asset/js/custom.js).

The POST call is authenticated with a random hard coded string (see src/bottle/app.py, api_get_tags)

Tags

List of all available tags with a pagination nav.
Clicking on the tag name the application will search all questions matching the tag you have selected, by clicking the site name the application will search all questions matching the tag and the site you ave selected.

Users

Table view of all available users with a pagination nav.

Clicking on the username, we enter on the detail's page of the user. In the details user page we see: UP Votes,Views,Down Votes. If the user has populated the "About me" field, we see a button that trigger a modal with the about me details.
If the user ha asked or answered some question we see a list of question in the "Post" section.

Posts

List of all posts with a pagination nav

API/REST endpoint

This application expose one api/rest route: /api/get/tags. You can query this route making a POST call, you have to make the call using a json payload:

curl--header"Content-Type: application/json"\--request POST\--data'{"auth_key":"dd4d5ff1c13!28356236c402d7ada.aed8b797ebd299b942291bc66,f804492be2009f14"}'\  http://localhost:8080/api/get/tags | jq{"data":[{"clean_name":"html5","created_time":"2021-12-29 11:33:06.517152+00:00","id":"1","name":"html5","network_sites":"Sites.wordpress","questions":"91","tag_id":"2","updated_time":"None"},    ...],"errors":[],"items": 5431,"last_page": 27}

The auth_key is hard coded in src/bottle/app.py

App configuration

The application's configuration are loaded by the load_config module (src/load_config.py).

This module will load a .yml file under:

/app/src/<BOTTLE_APP_NAME>/config/<BOTTLE_APP_ENVIRONMENT>

BOTTLE_APP_NAME andBOTTLE_APP_ENVIRONMENT are environment variables.

BOTTLE_APP_NAME is the name of the path where our bottle application lives, in this casebottle. BOTTLE_APP_ENVIRONMENT value is prod or env.

An example configuration is:

---enable_debug:Trueenable_reloader:Truehttp_port:8080pgsql_username:"bottle"pgsql_password:"b0tTl3_Be#"pgsql_db:"bottle_exchange"pgsql_host:"pgsql"pgsql_port:5432create_db_schema:Truedefault_result_limit:50

DB configuration

The database configuration is defined under the src/schema module.

The base.py file contains the engine configuration:

importload_config# <- See App configurationfromsqlalchemyimportcreate_enginefromsqlalchemy.ext.declarativeimportdeclarative_basefromsqlalchemy.ormimportsessionmakermain_config=load_config.load_config()conn_string='postgresql+psycopg2://{pgsql_username}:{pgsql_password}@{pgsql_host}:{pgsql_port}/{pgsql_db}'.format(**main_config)engine=create_engine(conn_string,pool_size=80,pool_recycle=60)Session=sessionmaker(bind=engine)Base=declarative_base()

All the tables are defined in a separate file always under schema:

SQLAlchemy plugin

In this example application is used and insalled a SQLAlchemy plugin (src/bottle/bottle_sa.py). This plugin is used to handle the SQLAlchemy session:

fromschema.baseimportBase,engine# <- Base and engine are defined in the schema module, see "DB configuration"frombottle_saimportSQLAlchemyPluginimportload_configmain_config=load_config.load_config()# Main Bottle app/applicationapp=application=Bottle()# DB PluginsaPlugin=SQLAlchemyPlugin(engine=engine,metadata=Base.metadata,create=main_config['create_db_schema'],config=main_config,)application.install(saPlugin)

This plugin pass an extra parameter on each function defined in src/bottle/app.py. By default this parameter is 'db', but it can be changed by passing the extra parameter 'keyword' on the SQLAlchemyPlugin init.

So an example function will be:

@app.route('/docs')@view('docs')defindex(db):# <- db is our SQLAlchemy sessionreturndict(page_name='docs')

Redis Cache

In this example application we use redis to cache some pages. The caching "approach" is very useful if you have:

• a site with few updates
• a slow page/route
• you have to decrease the load of your DB

The RedisCache is defined in src/bottle/bottle_cache.py and this is an example usage:

frombottle_cacheimportRedisCache# Cachecache=RedisCache()@app.route('/tags')@app.route('/tags/<page_nr:int>')@cache.cached()@view('tags')defget_tags(db,page_nr=1):do_something()returnsomething

You can init RedisCache class with an extra parameter config:

config={'redis_host':'<redis_hostname'>,'redis_port':6379,'redis_db':0,'cache_expiry':86400}cache=RedisCache(config=config)

By default the configurations are:

The @cached decorator can accept some arguments:

Caching json requests

A json caching example would be:

@app.route('/api/get/tags',method='POST')@cache.cached(content_type='application/json')defapi_get_tags(db):do_something()returnsomething

Invalidate cache

To invalidate the cache passinvalidate_cache key as query parameter or in the body request if you make a POST call

Skip/bypass cache

To skip or bypass the cache passskip_cache key as query parameter or in the body request if you make a POST call

Data export

To backup PgSQL data run dump_db.sh

./dump_db.sh

the dump will be placed in the root directory of this repository, the file will be named dump.sql.gz (Gzipped format)

Data import

To import an existing DB uncomment the following line in the docker-compose.yml:

volumes:-type:volumesource:postgrestarget:/var/lib/postgresql/data-./sql:/docker-entrypoint-initdb.d# <- uncomment this line

and plache your dump in gzip or plain text format under sql/ (create the directory first)

Stop the environment

To stop the environment run

docker-compose downStopping bottle   ...doneStopping postgres ...doneStopping redis    ...doneRemoving bottle   ...doneRemoving postgres ...doneRemoving redis    ...doneRemoving network bottle-exchange_default

To clean up all the data (pgsql data) pass the extra argument "-v" to docker-compose down. With this parameter the pgsql volume will be deleted.