Django stored procedures

Todo

Extend REGEXP and executor to validate arguments

Todo

Add support for multiple databases

Todo

Add support for serverside cursors

Settings

There just one setting — SP_DIR. It is the name of the directories inside apps, that contains files with stored procesures, custom indexes and other stuff. By default it is /sp/.

Procedures files

Files with database stuff can have any extention and contain any number of procedures and statements.

So stored procedure can be called via helper, its defenition must starts with CREATE OR REPLACE FUNCTION <name> where <name> is procedure’s name. Case is important.

Upload procedures

$ ./manage.py upload_sp

Usage

>>> from django_sp import sp_loader
>>> sp_loader.some_procedure(arg1, arg2, ret='all')
[{'column1': 'value1', 'column2': 'value2}, ... ]
>>> sp_loader.other_procedure(arg1, arg2, ret='one')
{'column1': 'value1', 'column2': 'value2'}
>>> sp_loader.list()
['some_procedure', 'other_procedure', 'else_one_procedure']

Loader class

class django_sp.loader.Loader(db_name=’default’)

EXECUTORS = {‘function’: ‘_execute_sp’, ‘view’: ‘_execute_view’}

REGEXP = re.compile(‘CREATE (?:OR REPLACE)? (?P<type>(?:VIEW)|(?:FUNCTION)) (?P<name>[^_]\w+)’, re.MULTILINE)

static columns_from_cursor(cursor: Cursor) → typing.List

commit()

list() → typing.Tuple

load_sp_into_db()

populate_helper()

static row_to_dict(row: typing.Tuple, columns: typing.List) → typing.Dict