Database for layab

Query your databases easily and transparently thanks to this module providing helpers on top of most brilliant python database modules (SQLAlchemy and PyMongo).

Table of Contents

• Features
• How to use

Features

Features:

• Audit
  • Automatic audit support
• Rollback
  • Automatic rollback support (when history is activated)
• History
  • Automatic history management
• Validation
  • Enforce proper values are received (type, restricted choices, required fields)
• Conversion
  • Converting JSON received data to appropriate database data type
  • Converting Database data type to JSON
• Health check
• Smart queries
  • HTTP query parameters are extracted and converted from HTTP query arguments
    • Special parameter: order_by (Feature not available for mongo)
    • Special parameter: limit
    • Special parameter: offset
  • Query on multiple equality via field=value1&field=value2
  • Query on excluded intervals via field=>value1&field=<value2
  • Query on included intervals via field=>=value1&field=<=value2
  • Query on restricted values via field=!=value1&field=!=value2 (Feature not yet available)
  • Query via a mix of all those features if needed as long as it make sense to you
  • Query regex thanks to * character via field=v*lue (Feature not yet available for mongo)

Usage

You will define a class to help you with the manipulation of:

• A collection if this is a MongoDB you are connecting to.
• A table if this is a non-Mongo database you are connecting to.

This class will describe:

• The document fields if this is a MongoDB you are connecting to.
• The table columns if this is a non-Mongo database you are connecting to.

By providing this class to a layabase.CRUDController instance, you will automatically have all features described in the previous section.

CRUD Controller

layabase.CRUDController provides C.R.U.D. methods (and more, as listed in features) on a specified table or mongo collection.

Controller definition

import layabase

# This will be the class describing your table or collection as defined in Table or Collection sections afterwards
table_or_collection = None 

controller = layabase.CRUDController(table_or_collection)

Controller features

Retrieving data

You can retrieve a list of rows or documents described as dictionaries:

import layabase

# This will be the controller as created in Controller definition section
controller: layabase.CRUDController = None

all_rows_or_documents = controller.get({})

filtered_rows_or_documents = controller.get({"value": 'value1'})

You can retrieve a single row or document described as dictionary:

import layabase

# This will be the controller as created in Controller definition section
controller: layabase.CRUDController = None

row_or_document = controller.get_one({"value": 'value1'})

Inserting data

You can insert many rows or documents at once using dictionary representation:

import layabase

# This will be the controller as created in Controller definition section
controller: layabase.CRUDController = None

inserted_rows_or_documents = controller.post_many([
    {'key': 'key1', 'value': 'value1'},
    {'key': 'key2', 'value': 'value2'},
])

You can insert a single row or document using dictionary representation:

import layabase

# This will be the controller as created in Controller definition section
controller: layabase.CRUDController = None

inserted_row_or_document = controller.post({'key': 'key1', 'value': 'value1'})

Updating data

You can update many rows or documents at once using (partial) dictionary representation:

import layabase

# This will be the controller as created in Controller definition section
controller: layabase.CRUDController = None

updated_rows_or_documents = controller.put_many([{'key': 'key1', 'value': 'new value1'}, {'key': 'key2', 'value': 'new value2'}])

You can update a single row or document using (partial) dictionary representation:

import layabase

# This will be the controller as created in Controller definition section
controller: layabase.CRUDController = None

updated_row_or_document = controller.put({'key': 'key1', 'value': 'new value1'})

Removing data

You can remove a subset of rows or documents:

import layabase

# This will be the controller as created in Controller definition section
controller: layabase.CRUDController = None

nb_removed_rows_or_documents = controller.delete({"key": 'key1'})

You can remove all rows or documents:

import layabase

# This will be the controller as created in Controller definition section
controller: layabase.CRUDController = None

nb_removed_rows_or_documents = controller.delete({})

Retrieving table or collection mapping

import layabase

# This will be the controller as created in Controller definition section
controller: layabase.CRUDController = None

# non mongo description = {'table': 'MyTable', 'key': 'key', 'value': 'value'}
# mongo description = {'table': 'MyCollection', 'key': 'key', 'value': 'value'}
description = controller.get_model_description()

Auditing

import layabase

# This will be the controller as created in Controller definition section
controller: layabase.CRUDController = None

all_audit_models_as_dict_list = controller.get_audit({})

filtered_audit_models_as_dict_list = controller.get_audit({"value": 'value1'})

Link to a database

Link to a Mongo database

import layabase


# Should be a list of CRUDController inherited classes
my_controllers = []
layabase.load("mongodb://host:port/server_name", my_controllers)

Link to a Mongo in-memory database

import layabase


# Should be a list of CRUDController inherited classes
my_controllers = []
layabase.load("mongomock", my_controllers)

Link to a non Mongo database

import layabase


# Should be a list of CRUDController inherited classes
my_controllers = []
layabase.load("your_connection_string", my_controllers)

Relational databases (non-Mongo)

SQLAlchemy is the underlying framework used to manipulate relational databases.

To create a representation of a table you will need to create a Mixin.

Table

You can add extra information to a column thanks to the layabase key within info parameter.

If the field should be required on queries:

info={'layabase': {"required_on_query": True}}

If * character in queries values should be interpreted as any characters:

info={'layabase': {"interpret_star_character": True}}

If the field can be queried with comparison signs such as >, <, >=, <=:

info={'layabase': {"allow_comparison_signs": True}}

When querying, provide a single value of a list of values.

if provided in order_by parameter, it will be considered as ascending order, add desc at the end of the value to explicitly order by in descending order.

If the field allow comparison signs (allow_comparison_signs), you can add >, >=, <, <= in front of the value.

from sqlalchemy import Column, String

class MyTable:
    __tablename__ = "my_table"

    key = Column(String, primary_key=True)
    value = Column(String)

MongoDB (non-relational)

PyMongo is the underlying framework used to manipulate MongoDB.

To create a representation of a collection you will need to create a Mixin class.

To link your model to the underlying collection, you will need to provide a connection string.

Collection

from layabase.mongo import Column

class MyCollection:
    __collection_name__ = "my_collection"

    key = Column(str, is_primary_key=True)
    dict_value = Column(dict)

String fields

Fields containing string can be described using layabase.mongo.Column

from layabase.mongo import Column

class MyCollection:
    __collection_name__ = "my_collection"

    key = Column()

As string is considered as the default field type, not providing the type explicitly when creating a column is valid.

The following parameters can also be provided when creating a column of string type:

	Description	Default value
choices	Restrict valid values. Should be a list of string or a function (without parameters) returning a list of string.	None (unrestricted)
default_value	Default field value returned to the client if field is not set. Should be a string or a function (with dictionary as parameter) returning a string.	None
description	Field description used in OpenAPI definition and in error messages. Should be a string value.	None
index_type	If and how this field should be indexed. Value should be one of IndexType enum.	None (not indexed)
allow_none_as_filter	If None value should be kept in queries (GET/DELETE). Should be a boolean value.	False (remove None from queries)
is_primary_key	If this field value is not allowed to be modified after insert. Should be a boolean value.	False (field value can always be modified)
is_nullable	If field value is optional. Should be a boolean value. Note that it is not allowed to force False if field has a default value.	Default to True if field is not a primary key. Default to True if field has a default value. Otherwise default to False.
is_required	If field value must be specified in client requests. Use it to avoid heavy requests. Should be a boolean value.	False (optional)
min_length	Minimum value length.	None (no minimum length)
max_length	Maximum value length.	None (no maximum length)
allow_comparison_signs	If field value should be interpreted to extract >, >=, <, <= prefix.	False (value is kept as provided for equlity comparison)

Dictionary fields

Fields containing a dictionary can be described using layabase.mongo.DictColumn

from layabase.mongo import DictColumn

class MyCollection:
    __collection_name__ = "my_collection"

    key = DictColumn()

List fields

Fields containing a list can be described using layabase.mongo.ListColumn

from layabase.mongo import ListColumn, Column

class MyCollection:
    __collection_name__ = "my_collection"

    key = ListColumn(Column())

How to install

1. python 3.6+ must be installed
2. Use pip to install module:

python -m pip install layabase

Note that depending on what you want to connect to, you will have to use a different module name than layabase:

• Mongo database: layabase[mongo]
• Mongo in-memory database: layabase mongomock
• Other database: layabase[sqlalchemy]