Implement basic CRUD operations into DataObject framework with generalized DB access.
Project description
db-able
Framework to implement basic CRUD operations with DB for DataObject.
Quick start
Setup in-line
Set up your connection string to your database.
from db_able import client
client.CONN_STR = '{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?{query_args}'
Environment Variable
Set up connection string with an environment variable.
export DB_CONN_STR={dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?{query_args}
Usage
Implement the mixins into your DataObject to inject CRUD methods.
from do_py import R
from db_able import Creatable, Deletable, Loadable, Savable
class MyObject(Creatable, Deletable, Loadable, Savable):
db = '{schema_name}'
_restrictions = {
'id': R.INT,
'key': R.INT
}
load_params = ['id']
create_params = ['key']
delete_params = ['id']
save_params = ['id', 'key']
my_obj = MyObject.create(key=555)
my_obj = MyObject.load(id=my_obj.id)
my_obj.key = 777
my_obj.save()
my_obj.delete()
Classmethods create
, load
, and methods save
and delete
are made available
to your DataObject class.
Use provided SQL Generating utils to expedite implementation.
from db_able.utils.sql_generator import print_all_sps
from examples.a import A
print_all_sps(A)
Examples
"A" Python implementation
from do_py import DataObject, R
from db_able import Creatable, Loadable, Savable, Deletable
class Json(DataObject):
""" Nested Json object for A. """
_restrictions = {
'x': R.INT,
'y': R.INT
}
class A(Creatable, Loadable, Savable, Deletable):
""" Basic DBAble implementation for unit tests. """
db = 'testing'
_restrictions = {
'id': R.INT,
'string': R.NULL_STR,
'json': R(Json, type(None)),
'int': R.NULL_INT,
'float': R.NULL_FLOAT,
'datetime': R.NULL_DATETIME
}
load_params = ['id']
create_params = ['string', 'json', 'int', 'float', 'datetime']
save_params = ['id', 'string', 'json', 'int', 'float', 'datetime']
delete_params = ['id']
"A" MySQL Table structure
CREATE TABLE IF NOT EXISTS `testing`.`a`
(
`id` INT NOT NULL AUTO_INCREMENT,
`string` VARCHAR(45) NULL,
`json` JSON NULL,
`int` INT(11) NULL,
`float` FLOAT NULL,
`datetime` TIMESTAMP NULL,
PRIMARY KEY (`id`)
);
"A" MySQL CRUD Stored Procedures
USE `testing`;
DROP PROCEDURE IF EXISTS `testing`.`A_create`;
DELIMITER $$
CREATE
DEFINER = `root`@`localhost` PROCEDURE `testing`.`A_create`
(
IN `_string` VARCHAR(45),
IN `_json` JSON,
IN `_int` INT,
IN `_float` FLOAT,
IN `_datetime` TIMESTAMP
)
BEGIN
INSERT INTO
`testing`.`a`
(
`string`,
`json`,
`int`,
`float`,
`datetime`
)
VALUES
(
`_string`,
`_json`,
`_int`,
`_float`,
`_datetime`
);
CALL `testing`.`A_load`(LAST_INSERT_ID());
END;
$$
DELIMITER ;
USE `testing`;
DROP PROCEDURE IF EXISTS `testing`.`A_delete`;
DELIMITER $$
CREATE
DEFINER = `root`@`localhost` PROCEDURE `testing`.`A_delete`
(
IN `_id` INT
)
BEGIN
DELETE
FROM
`testing`.`a`
WHERE
`id` = `_id`;
SELECT ROW_COUNT() AS `deleted`;
END;
$$
DELIMITER ;
USE `testing`;
DROP PROCEDURE IF EXISTS `testing`.`A_load`;
DELIMITER $$
CREATE
DEFINER = `root`@`localhost` PROCEDURE `testing`.`A_load`
(
IN `_id` INT
)
BEGIN
SELECT *
FROM
`testing`.`a`
WHERE
`id` = `_id`;
END;
$$
DELIMITER ;
USE `testing`;
DROP PROCEDURE IF EXISTS `testing`.`A_save`;
DELIMITER $$
CREATE
DEFINER = `root`@`localhost` PROCEDURE `testing`.`A_save`
(
IN `_id` INT,
IN `_string` VARCHAR(45),
IN `_json` JSON,
IN `_int` INT,
IN `_float` FLOAT,
IN `_datetime` TIMESTAMP
)
BEGIN
UPDATE
`testing`.`a`
SET
`string`=`_string`,
`json`=`_json`,
`int`=`_int`,
`float`=`_float`,
`datetime`=`_datetime`
WHERE
`id`=`_id`;
CALL `testing`.`A_load`(`_id`);
END;
$$
DELIMITER ;
Advanced Use Cases
User
This implementation requires extension of core functionality to support salting, hashing, and standard password security practices.
import crypt
import hashlib
from do_py import R
from db_able import Loadable, Creatable, Savable, Deletable
class User(Loadable, Creatable, Savable, Deletable):
"""
User DataObject with DB CRUD implementation.
Customized to handle password encryption and security standards.
"""
db = 'testing'
_restrictions = {
'user_id': R.INT,
'username': R.STR,
'salt': R.STR,
'hash': R.STR
}
_extra_restrictions = {
'password': R.STR,
}
load_params = ['user_id']
create_params = ['username', 'salt', 'hash'] # password is required. salt and hash are generated.
save_params = ['user_id', 'username', 'salt', 'hash']
delete_params = ['user_id']
@classmethod
def generate_salt(cls):
"""
:rtype: str
"""
return crypt.mksalt(crypt.METHOD_SHA512)
@classmethod
def generate_hash(cls, password, salt):
"""
:type password: str
:type salt: str
:rtype: str
"""
salted_password = password + salt
return hashlib.sha512(salted_password.encode()).hexdigest()
@classmethod
def create(cls, password=None, **kwargs):
"""
Overloaded to prevent handling raw password in DB.
:type password: str
:keyword username: str
:rtype: User
"""
password = cls.kwargs_validator('password', password=password)[0][1]
salt = cls.generate_salt()
kwargs.update({
'salt': salt,
'hash': cls.generate_hash(password, salt)
})
return super(User, cls).create(**kwargs)
def save(self, password=None):
"""
Overloaded to support updating password with security.
:type password: str
:rtype: bool
"""
if password:
password = self.kwargs_validator('password', password=password)[0][1]
self.salt = self.generate_salt()
self.hash = self.generate_hash(password, self.salt)
return super(User, self).save()
User MySQL Table Structure
CREATE TABLE IF NOT EXISTS `user` (
`user_id` int NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`salt` varchar(255) NOT NULL,
`hash` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`)
);
User MySQL CRUD Stored Procedures
USE `testing`;
DROP PROCEDURE IF EXISTS `testing`.`User_load`;
DELIMITER $$
CREATE
DEFINER = `root`@`localhost` PROCEDURE `testing`.`User_load`
(
IN `_user_id` VARCHAR(255)
)
BEGIN
SELECT * FROM `testing`.`user` WHERE `user_id` = `_user_id`;
END;
$$
DELIMITER ;
USE `testing`;
DROP PROCEDURE IF EXISTS `testing`.`User_create`;
DELIMITER $$
CREATE
DEFINER = `root`@`localhost` PROCEDURE `testing`.`User_create`
(
IN `_username` VARCHAR(255),
IN `_salt` VARCHAR(255),
IN `_hash` VARCHAR(255)
)
BEGIN
INSERT INTO `testing`.`user` (`username`, `salt`, `hash`) VALUES (`_username`, `_salt`, `_hash`);
CALL `testing`.`User_load`(LAST_INSERT_ID());
END;
$$
DELIMITER ;
USE `testing`;
DROP PROCEDURE IF EXISTS `testing`.`User_save`;
DELIMITER $$
CREATE
DEFINER = `root`@`localhost` PROCEDURE `testing`.`User_save`
(
IN `_user_id` VARCHAR(255),
IN `_username` VARCHAR(255),
IN `_salt` VARCHAR(255),
IN `_hash` VARCHAR(255)
)
BEGIN
UPDATE `testing`.`user` SET `username`=`_username`, `salt`=`_salt`, `hash`=`_hash` WHERE `user_id` = `_user_id`;
CALL `testing`.`User_load`(`_user_id`);
END;
$$
DELIMITER ;
USE `testing`;
DROP PROCEDURE IF EXISTS `testing`.`User_delete`;
DELIMITER $$
CREATE
DEFINER = `root`@`localhost` PROCEDURE `testing`.`User_delete`
(
IN `_user_id` VARCHAR(255)
)
BEGIN
DELETE FROM `testing`.`user` WHERE `user_id` = `_user_id`;
SELECT ROW_COUNT() AS `deleted`;
END;
$$
DELIMITER ;
Best Practices
- It is recommended to store your SQL files within your code repository for ease of reference. Refer to
do-able/tests/sql
for an example of code organization. - Generally, explicitly defining the columns for your %s_load stored procedures is better for forward compatibility as changes are implemented in the long run.
Testing & Code Quality
Code coverage reports for master, branches, and PRs are posted here in CodeCov.
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.