# db-able
Framework to implement basic CRUD operations with DB for [DataObject](https://github.com/do-py-together/do-py).
## Quick start
### Setup in-line
Set up your connection string to your database.
```python
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.
```bash
export DB_CONN_STR={dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?{query_args}
```
### Usage
Implement the mixins into your DataObject to inject CRUD methods.
```python
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.
```python
from db_able.utils.sql_generator import print_all_sps
from examples.a import A
print_all_sps(A)
```
## Examples
### "A" Python implementation
```python
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
```mysql
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
```mysql
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.
```python
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
```mysql
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
```mysql
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](https://codecov.io/gh/timdaviss/db-able).
Raw data
{
"_id": null,
"home_page": "https://github.com/timdaviss/db-able",
"name": "db-able",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "development,OO",
"author": "Tim Davis",
"author_email": "timdavis.3991@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/c8/fe/948906c153afc51b74415e263cac8bf5d59f23997d3910e35afbd3df6d0c/db-able-2.1.6.tar.gz",
"platform": null,
"description": "# db-able\n\n\n\n\n\nFramework to implement basic CRUD operations with DB for [DataObject](https://github.com/do-py-together/do-py).\n\n## Quick start\n### Setup in-line\nSet up your connection string to your database.\n```python\nfrom db_able import client\n\n\nclient.CONN_STR = '{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?{query_args}'\n```\n### Environment Variable\nSet up connection string with an environment variable.\n```bash\nexport DB_CONN_STR={dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?{query_args}\n```\n\n### Usage\nImplement the mixins into your DataObject to inject CRUD methods.\n```python\nfrom do_py import R\nfrom db_able import Creatable, Deletable, Loadable, Savable\n\n\nclass MyObject(Creatable, Deletable, Loadable, Savable):\n db = '{schema_name}'\n _restrictions = {\n 'id': R.INT,\n 'key': R.INT\n }\n load_params = ['id']\n create_params = ['key']\n delete_params = ['id']\n save_params = ['id', 'key']\n\n\nmy_obj = MyObject.create(key=555)\nmy_obj = MyObject.load(id=my_obj.id)\nmy_obj.key = 777\nmy_obj.save()\nmy_obj.delete()\n```\nClassmethods `create`, `load`, and methods `save` and `delete` are made available\nto your DataObject class.\n\nUse provided SQL Generating utils to expedite implementation.\n```python\nfrom db_able.utils.sql_generator import print_all_sps\nfrom examples.a import A\n\nprint_all_sps(A)\n```\n\n## Examples\n### \"A\" Python implementation\n```python\nfrom do_py import DataObject, R\n\nfrom db_able import Creatable, Loadable, Savable, Deletable\n\n\nclass Json(DataObject):\n \"\"\" Nested Json object for A. \"\"\"\n _restrictions = {\n 'x': R.INT,\n 'y': R.INT\n }\n\n\nclass A(Creatable, Loadable, Savable, Deletable):\n \"\"\" Basic DBAble implementation for unit tests. \"\"\"\n db = 'testing'\n _restrictions = {\n 'id': R.INT,\n 'string': R.NULL_STR,\n 'json': R(Json, type(None)),\n 'int': R.NULL_INT,\n 'float': R.NULL_FLOAT,\n 'datetime': R.NULL_DATETIME\n }\n load_params = ['id']\n create_params = ['string', 'json', 'int', 'float', 'datetime']\n save_params = ['id', 'string', 'json', 'int', 'float', 'datetime']\n delete_params = ['id']\n```\n\n### \"A\" MySQL Table structure\n```mysql\nCREATE TABLE IF NOT EXISTS `testing`.`a`\n(\n `id` INT NOT NULL AUTO_INCREMENT,\n `string` VARCHAR(45) NULL,\n `json` JSON NULL,\n `int` INT(11) NULL,\n `float` FLOAT NULL,\n `datetime` TIMESTAMP NULL,\n PRIMARY KEY (`id`)\n);\n```\n\n### \"A\" MySQL CRUD Stored Procedures\n```mysql\nUSE `testing`;\nDROP PROCEDURE IF EXISTS `testing`.`A_create`;\n\nDELIMITER $$\nCREATE\n DEFINER = `root`@`localhost` PROCEDURE `testing`.`A_create`\n(\n IN `_string` VARCHAR(45),\n IN `_json` JSON,\n IN `_int` INT,\n IN `_float` FLOAT,\n IN `_datetime` TIMESTAMP\n)\nBEGIN\n\n INSERT INTO\n `testing`.`a`\n (\n `string`,\n `json`,\n `int`,\n `float`,\n `datetime`\n )\n VALUES\n (\n `_string`,\n `_json`,\n `_int`,\n `_float`,\n `_datetime`\n );\n CALL `testing`.`A_load`(LAST_INSERT_ID());\n\nEND;\n$$\nDELIMITER ;\n\nUSE `testing`;\nDROP PROCEDURE IF EXISTS `testing`.`A_delete`;\n\nDELIMITER $$\nCREATE\n DEFINER = `root`@`localhost` PROCEDURE `testing`.`A_delete`\n(\n IN `_id` INT\n)\nBEGIN\n\n DELETE\n FROM\n `testing`.`a`\n WHERE\n `id` = `_id`;\n SELECT ROW_COUNT() AS `deleted`;\n\nEND;\n$$\nDELIMITER ;\n\nUSE `testing`;\nDROP PROCEDURE IF EXISTS `testing`.`A_load`;\n\nDELIMITER $$\nCREATE\n DEFINER = `root`@`localhost` PROCEDURE `testing`.`A_load`\n(\n IN `_id` INT\n)\nBEGIN\n\n SELECT *\n FROM\n `testing`.`a`\n WHERE\n `id` = `_id`;\n\nEND;\n$$\nDELIMITER ;\n\nUSE `testing`;\nDROP PROCEDURE IF EXISTS `testing`.`A_save`;\n\nDELIMITER $$\nCREATE\n DEFINER = `root`@`localhost` PROCEDURE `testing`.`A_save`\n(\n IN `_id` INT,\n IN `_string` VARCHAR(45),\n IN `_json` JSON,\n IN `_int` INT,\n IN `_float` FLOAT,\n IN `_datetime` TIMESTAMP\n)\nBEGIN\n\n UPDATE\n `testing`.`a`\n SET\n `string`=`_string`,\n `json`=`_json`,\n `int`=`_int`,\n `float`=`_float`,\n `datetime`=`_datetime`\n WHERE\n `id`=`_id`;\n CALL `testing`.`A_load`(`_id`);\n\nEND;\n$$\nDELIMITER ;\n```\n\n## Advanced Use Cases\n### User\nThis implementation requires extension of core functionality\nto support salting, hashing, and standard password security practices.\n```python\nimport crypt\nimport hashlib\n\nfrom do_py import R\n\nfrom db_able import Loadable, Creatable, Savable, Deletable\n\n\nclass User(Loadable, Creatable, Savable, Deletable):\n \"\"\"\n User DataObject with DB CRUD implementation.\n Customized to handle password encryption and security standards.\n \"\"\"\n db = 'testing'\n _restrictions = {\n 'user_id': R.INT,\n 'username': R.STR,\n 'salt': R.STR,\n 'hash': R.STR\n }\n _extra_restrictions = {\n 'password': R.STR,\n }\n load_params = ['user_id']\n create_params = ['username', 'salt', 'hash'] # password is required. salt and hash are generated.\n save_params = ['user_id', 'username', 'salt', 'hash']\n delete_params = ['user_id']\n\n @classmethod\n def generate_salt(cls):\n \"\"\"\n :rtype: str\n \"\"\"\n return crypt.mksalt(crypt.METHOD_SHA512)\n\n @classmethod\n def generate_hash(cls, password, salt):\n \"\"\"\n :type password: str\n :type salt: str\n :rtype: str\n \"\"\"\n salted_password = password + salt\n return hashlib.sha512(salted_password.encode()).hexdigest()\n\n @classmethod\n def create(cls, password=None, **kwargs):\n \"\"\"\n Overloaded to prevent handling raw password in DB.\n :type password: str\n :keyword username: str\n :rtype: User\n \"\"\"\n password = cls.kwargs_validator('password', password=password)[0][1]\n salt = cls.generate_salt()\n kwargs.update({\n 'salt': salt,\n 'hash': cls.generate_hash(password, salt)\n })\n return super(User, cls).create(**kwargs)\n\n def save(self, password=None):\n \"\"\"\n Overloaded to support updating password with security.\n :type password: str\n :rtype: bool\n \"\"\"\n if password:\n password = self.kwargs_validator('password', password=password)[0][1]\n self.salt = self.generate_salt()\n self.hash = self.generate_hash(password, self.salt)\n return super(User, self).save()\n```\n\n### User MySQL Table Structure\n```mysql\nCREATE TABLE IF NOT EXISTS `user` (\n `user_id` int NOT NULL AUTO_INCREMENT,\n `username` varchar(255) NOT NULL,\n `salt` varchar(255) NOT NULL,\n `hash` varchar(255) NOT NULL,\n PRIMARY KEY (`user_id`)\n);\n```\n\n### User MySQL CRUD Stored Procedures\n```mysql\nUSE `testing`;\nDROP PROCEDURE IF EXISTS `testing`.`User_load`;\n\nDELIMITER $$\nCREATE\n DEFINER = `root`@`localhost` PROCEDURE `testing`.`User_load`\n(\n IN `_user_id` VARCHAR(255)\n)\nBEGIN\n\n SELECT * FROM `testing`.`user` WHERE `user_id` = `_user_id`;\n\nEND;\n$$\nDELIMITER ;\n\n\nUSE `testing`;\nDROP PROCEDURE IF EXISTS `testing`.`User_create`;\n\nDELIMITER $$\nCREATE\n DEFINER = `root`@`localhost` PROCEDURE `testing`.`User_create`\n(\n IN `_username` VARCHAR(255),\n IN `_salt` VARCHAR(255),\n IN `_hash` VARCHAR(255)\n)\nBEGIN\n\n INSERT INTO `testing`.`user` (`username`, `salt`, `hash`) VALUES (`_username`, `_salt`, `_hash`);\n CALL `testing`.`User_load`(LAST_INSERT_ID());\n\nEND;\n$$\nDELIMITER ;\n\n\nUSE `testing`;\nDROP PROCEDURE IF EXISTS `testing`.`User_save`;\n\nDELIMITER $$\nCREATE\n DEFINER = `root`@`localhost` PROCEDURE `testing`.`User_save`\n(\n IN `_user_id` VARCHAR(255),\n IN `_username` VARCHAR(255),\n IN `_salt` VARCHAR(255),\n IN `_hash` VARCHAR(255)\n)\nBEGIN\n\n \n UPDATE `testing`.`user` SET `username`=`_username`, `salt`=`_salt`, `hash`=`_hash` WHERE `user_id` = `_user_id`;\n CALL `testing`.`User_load`(`_user_id`);\n\nEND;\n$$\nDELIMITER ;\n\n\nUSE `testing`;\nDROP PROCEDURE IF EXISTS `testing`.`User_delete`;\n\nDELIMITER $$\nCREATE\n DEFINER = `root`@`localhost` PROCEDURE `testing`.`User_delete`\n(\n IN `_user_id` VARCHAR(255)\n)\nBEGIN\n\n DELETE FROM `testing`.`user` WHERE `user_id` = `_user_id`;\n SELECT ROW_COUNT() AS `deleted`;\n\n\nEND;\n$$\nDELIMITER ;\n```\n\n## Best Practices\n* It is recommended to store your SQL files within your code repository for ease of reference. Refer to\n`do-able/tests/sql` for an example of code organization.\n* Generally, explicitly defining the columns for your %s_load stored procedures is better for forward compatibility as\nchanges are implemented in the long run.\n\n### Testing & Code Quality\nCode coverage reports for master, branches, and PRs \nare posted [here in CodeCov](https://codecov.io/gh/timdaviss/db-able).\n\n\n",
"bugtrack_url": null,
"license": "",
"summary": "Implement basic CRUD operations into DataObject framework with generalized DB access.",
"version": "2.1.6",
"project_urls": {
"Homepage": "https://github.com/timdaviss/db-able"
},
"split_keywords": [
"development",
"oo"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "e9bca29f32f13f0c82c01b51dae593fcce845fd1fc3349ec11bff7c1783446ac",
"md5": "6ee8078357150b7664a4efdc7cff66a4",
"sha256": "6c615f02f0cc16eff2b7dba293aad8d70ed6184ef6628704eac4090c3ea940d8"
},
"downloads": -1,
"filename": "db_able-2.1.6-py3-none-any.whl",
"has_sig": false,
"md5_digest": "6ee8078357150b7664a4efdc7cff66a4",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 28864,
"upload_time": "2023-05-20T15:45:01",
"upload_time_iso_8601": "2023-05-20T15:45:01.992671Z",
"url": "https://files.pythonhosted.org/packages/e9/bc/a29f32f13f0c82c01b51dae593fcce845fd1fc3349ec11bff7c1783446ac/db_able-2.1.6-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "c8fe948906c153afc51b74415e263cac8bf5d59f23997d3910e35afbd3df6d0c",
"md5": "aad07083a845fc0ea4492556eeea3fce",
"sha256": "03898001891351abab8e524ab74e2b4aa5eb5af8805c94f2f380c6655dd338a3"
},
"downloads": -1,
"filename": "db-able-2.1.6.tar.gz",
"has_sig": false,
"md5_digest": "aad07083a845fc0ea4492556eeea3fce",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 21729,
"upload_time": "2023-05-20T15:45:05",
"upload_time_iso_8601": "2023-05-20T15:45:05.462289Z",
"url": "https://files.pythonhosted.org/packages/c8/fe/948906c153afc51b74415e263cac8bf5d59f23997d3910e35afbd3df6d0c/db-able-2.1.6.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-05-20 15:45:05",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "timdaviss",
"github_project": "db-able",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "db-able"
}
JSON
