Metadata-Version: 2.1
Name: szndaogen
Version: 2.4.0
Summary: SZN Database Access Object Generator for MySQL. Generates Models and DataManagers from existing MySQL database structure.
Home-page: https://github.com/seznam/szndaogen
Author: Ales Adamek, Filip Cima, Richard Paprok
Author-email: hpo.sport@firma.seznam.cz
License: UNKNOWN
Description: # SZN DAOGen
        ## Database Access Object Generator by Seznam.cz
        
        Is a simple utility which enables you to generate Models and Data Managers for your project based on MySQL database to access data easily.
        
        ## Description
        DAOGenerator enables you to generate `Model`s and `DataManager`s from existing 
        database structure. For each database table and view the python `Model` will be generated. 
        Each model will contain all table attributes with its datatypes and other meta 
        information. For each `Model` one `base DataManager` for database access will be generated 
        too. For each `base DataManager` one `DataManager` will be generated if it does not 
        exist from previous `szndaogen` run. If `DataManager` exists file generation of it 
        will be skiped. 
        `DataManager` is designed for future custom functionality extending. `Model`s 
        and `base DataManager`s should not be edited it will be overwiten by next 
        `szndaogen` script execution.
        
        ### Advantages
        - changes are needed only on database side
        - you can stop beeing a coding monkey to write python models
        - autogenerated table meta information
        - works with Tables and Views
        - View performance issue solved
        - it is not nessesary to have view defined on production database
        - Models and DataManages templates are written in Jinja2
        - PyCharm code completion works perfectly with generated Models and DataManagers
        - fast and easy change of all Models and DataManager if DB structure changed (it takes less than second)
        
        ## Disadvantages
        - you can feel shamed how easy to use it could be
        
        ## Usage
        ```
        szndaogen --help
        
        Usage of SZN DAOGen v2.3.0 - Database Access Object Generator by Seznam.cz
        szndaogen [options] output_path
            example: szndaogen -a localhost -d my_database -u root -p pass /path/to/data_access
            example: szndaogen -a localhost -d my_database -u root -p pass ./data_access
        
        
        Options:
          -h, --help            show this help message and exit
          -a DB_HOST, --host-address=DB_HOST
                                MySQL database host. (required)
          -r DB_PORT, --port=DB_PORT
                                MySQL database port.
          -d DB_NAME, --database=DB_NAME
                                MySQL database name. (required)
          -u DB_USER, --user=DB_USER
                                User name for MySQL DB authentication. (required)
          -p DB_PASS, --password=DB_PASS
                                Password for MySQL DB authentication.
          -t TEMPLATES_PATH, --templates-path=TEMPLATES_PATH
                                Path to custom templates of Models (model.jinja),
                                DataManagers (manager.jinja) and DataManagerBases
                                (manager_base.jinja).
        ```
        
        ## Installation
        ```bash
        python3 setup.py install
        ```
        or
        ```
        pip3 install szndaogen
        ```
        ## Example
        
        For our example we will use sample database from [MySQLTutorial.org](https://www.mysqltutorial.org/mysql-sample-database.aspx/). Zipped database file is [here](https://sp.mysqltutorial.org/wp-content/uploads/2018/03/mysqlsampledatabase.zip).
        
        Importing database:
        ```bash
        mysql < mysqlsampledatabase.sql
        ```
        Create custom application directory or use `example` directory from our repository.  
        Lets generate Models and DataManagers from imported database by typing command:
        ```bash
        szndaogen
        ```
        It will run simple szndaogen wizzard (if no arguments passed):
        ```
        Required parameters are not satisfied. Would you like to run setup wizard? [Y/n] y
        MySQL host address: localhost
        MySQL port (default 3306): 3306
        MySQL database name: classicmodels
        MySQL username: mysql-user
        MySQL password: mysql-user-password
        Output path where all models and managers will be generated (default "./data_access"): ./example_dao
        Before you proceed, would you like to save this configuration as a bash script in CWD for future use? [Y/n] y
        
        Shortcut script 'szndaogen-localhost-classicmodels.sh' created in current working directory.
        Writing model `Customers` into `example_dao/models/customers_model.py`
        Writing manager `Customers` into `example_dao/managers/customers_manager.py`
        Writing manager `Customers` into `example_dao/managers/base/customers_manager_base.py`
        Writing model `Employees` into `example_dao/models/employees_model.py`
        Writing manager `Employees` into `example_dao/managers/employees_manager.py`
        Writing manager `Employees` into `example_dao/managers/base/employees_manager_base.py`
        Writing model `Offices` into `example_dao/models/offices_model.py`
        Writing manager `Offices` into `example_dao/managers/offices_manager.py`
        Writing manager `Offices` into `example_dao/managers/base/offices_manager_base.py`
        Writing model `Orderdetails` into `example_dao/models/orderdetails_model.py`
        Writing manager `Orderdetails` into `example_dao/managers/orderdetails_manager.py`
        Writing manager `Orderdetails` into `example_dao/managers/base/orderdetails_manager_base.py`
        Writing model `Orders` into `example_dao/models/orders_model.py`
        Writing manager `Orders` into `example_dao/managers/orders_manager.py`
        Writing manager `Orders` into `example_dao/managers/base/orders_manager_base.py`
        Writing model `Payments` into `example_dao/models/payments_model.py`
        Writing manager `Payments` into `example_dao/managers/payments_manager.py`
        Writing manager `Payments` into `example_dao/managers/base/payments_manager_base.py`
        Writing model `Productlines` into `example_dao/models/productlines_model.py`
        Writing manager `Productlines` into `example_dao/managers/productlines_manager.py`
        Writing manager `Productlines` into `example_dao/managers/base/productlines_manager_base.py`
        Writing model `Products` into `example_dao/models/products_model.py`
        Writing manager `Products` into `example_dao/managers/products_manager.py`
        Writing manager `Products` into `example_dao/managers/base/products_manager_base.py`
        ```
        Now your project contains file structure as you can see bellow:
        ```
        tree .
        
        ├── szndaogen-localhost-classicmodels.sh
        ├── example_dao
        │   ├── __init__.py
        │   ├── managers
        │   │   ├── base
        │   │   │   ├── __init__.py
        │   │   │   ├── customers_manager_base.py
        │   │   │   ├── employees_manager_base.py
        │   │   │   ├── offices_manager_base.py
        │   │   │   ├── orderdetails_manager_base.py
        │   │   │   ├── orders_manager_base.py
        │   │   │   ├── payments_manager_base.py
        │   │   │   ├── productlines_manager_base.py
        │   │   │   └── products_manager_base.py
        │   │   ├── __init__.py
        │   │   ├── customers_manager.py
        │   │   ├── employees_manager.py
        │   │   ├── offices_manager.py
        │   │   ├── orderdetails_manager.py
        │   │   ├── orders_manager.py
        │   │   ├── payments_manager.py
        │   │   ├── productlines_manager.py
        │   │   └── products_manager.py
        │   └── models
        │       ├── __init__.py
        │       ├── customers_model.py
        │       ├── employees_model.py
        │       ├── offices_model.py
        │       ├── orderdetails_model.py
        │       ├── orders_model.py
        │       ├── payments_model.py
        │       ├── productlines_model.py
        │       └── products_model.py
        └── requirements.txt
        ```
        Example of auto-generated Model class:
        ```python
        # !!! DO NOT MODIFY !!!
        # Automatically generated Model class
        # Generated by "szndaogen" tool
        
        
        import typing
        from szndaogen.data_access.model_base import ModelBase
        
        
        class EmployeesModel(ModelBase):
            class Meta:
                TABLE_NAME: str = "employees"
                TABLE_TYPE: str = "BASE TABLE"
                # fmt: off
                SQL_STATEMENT: str = "SELECT {PROJECTION} FROM `employees` {WHERE} {ORDER_BY} {LIMIT} {OFFSET}"
                # fmt: on
        
                SQL_STATEMENT_WHERE_BASE: str = "1"
                SQL_STATEMENT_ORDER_BY_DEFAULT: str = ""
        
                PRIMARY_KEYS: typing.List = ["employeeNumber", ]
                ATTRIBUTE_LIST: typing.List = ["employeeNumber", "lastName", "firstName", "extension", "email", "officeCode", "reportsTo", "jobTitle", ]
                ATTRIBUTE_TYPES: typing.Dict = {
                    "employeeNumber": int,
                    "lastName": str,
                    "firstName": str,
                    "extension": str,
                    "email": str,
                    "officeCode": str,
                    "reportsTo": int,
                    "jobTitle": str,
                }
                MODEL_DATA_CONVERTOR: typing.Dict = {
                }
        
                # Class attribute to table attribute name conversion
                employeeNumber: str = "employeeNumber"
                lastName: str = "lastName"
                firstName: str = "firstName"
                extension: str = "extension"
                email: str = "email"
                officeCode: str = "officeCode"
                reportsTo: str = "reportsTo"
                jobTitle: str = "jobTitle"
        
            def __init__(self, init_data: typing.Dict = {}):
                self.employeeNumber: int = None
                """Type: int(11), Can be NULL: NO, Key: PRI"""
                self.lastName: str = None
                """Type: varchar(50), Can be NULL: NO"""
                self.firstName: str = None
                """Type: varchar(50), Can be NULL: NO"""
                self.extension: str = None
                """Type: varchar(10), Can be NULL: NO"""
                self.email: str = None
                """Type: varchar(100), Can be NULL: NO"""
                self.officeCode: str = None
                """Type: varchar(10), Can be NULL: NO, Key: MUL"""
                self.reportsTo: int = None
                """Type: int(11), Can be NULL: YES, Key: MUL"""
                self.jobTitle: str = None
                """Type: varchar(50), Can be NULL: NO"""
                super().__init__(init_data)
        
        ```
        Example of auto-generated Base Manager class:
        ```python
        # !!! DO NOT MODIFY !!!
        # Automatically generated Base Manager class
        # Generated by "szndaogen" tool
        
        import typing
        from szndaogen.data_access.manager_base import TableManagerBase
        from ...models.employees_model import EmployeesModel
        
        
        class EmployeesManagerBase(TableManagerBase):
            MODEL_CLASS = EmployeesModel
        
            @classmethod
            def create_model_instance(cls, init_data: typing.Dict = None) -> EmployeesModel:
                if init_data is None:
                    init_data = {}
        
                return super().create_model_instance(init_data)
        
            def select_one(self, employeeNumber: int, condition: str = "1", condition_params: typing.Tuple = (), projection: typing.Tuple = (), order_by: typing.Tuple = ()) -> EmployeesModel:
                return super().select_one(employeeNumber, condition=condition, condition_params=condition_params, projection=projection, order_by=order_by)
        
            def select_all(self, condition: str = "1", condition_params: typing.Tuple = (), projection: typing.Tuple = (), order_by: typing.Tuple = (), limit: int = 0, offset: int = 0) -> typing.List[EmployeesModel]:
                return super().select_all(condition=condition, condition_params=condition_params, projection=projection, order_by=order_by, limit=limit, offset=offset)
        
        ```
        Example of auto-generated Manager class which could be modified/extended by your own functionality:
        ```python
        # This file can be modified. If file exists it wont be replaced by "szndaogen" any more.
        # Automatically generated Manager class
        # Generated by "szndaogen" tool
        
        from .base.employees_manager_base import EmployeesManagerBase
        
        
        class EmployeesManager(EmployeesManagerBase):
            pass
        
        ```
        
        ### Example application
        Now you can write application like this:
        ```python
        from example_dao.managers.employees_manager import EmployeesManager
        from szndaogen.config import Config
        from szndaogen.data_access.db import DBI
        from szndaogen.tools.log import Logger, StdOutLogger
        
        
        @DBI.transaction("dbi")
        def update_employee_first_name(employee_id: int, new_first_name: str, dbi: DBI = None) -> int:
            manager = EmployeesManager(dbi=dbi)  # tell manager to work with passed DBI instance to keep transaction connection
            model_instance = manager.select_one(employee_id)
            model_instance.firstName = new_first_name
            return manager.update_one(model_instance)
        
        
        if __name__ == '__main__':
            Config.MANAGER_AUTO_MAP_MODEL_ATTRIBUTES = True  # if disabled, you can control attributes mapping by `map_model_attributes()` method on model instance if needed to better performance
            Config.MYSQL_HOST = "localhost"
            Config.MYSQL_DATABASE = "classicmodels"
            Config.MYSQL_USER = "root"
            Config.MYSQL_PASSWORD = ""
        
            Logger.set_external_logger(logger_instance=StdOutLogger())
        
            employee_manager = EmployeesManager()
            employee_result = employee_manager.select_all(order_by=(f"{EmployeesManager.MODEL_CLASS.Meta.employeeNumber} ASC",))
        
            for employee_model_instance in employee_result:
                print(f"{employee_model_instance.firstName} {employee_model_instance.lastName} - {employee_model_instance.employeeNumber}")
        
            # autocommit update
            employee_result = employee_manager.select_all("lastName=%s", ("Thompson",))
            if len(employee_result) == 1:
                employee_model_instance = employee_result[0]
                print(f"Trying to update record id: {employee_model_instance.employeeNumber} - {employee_model_instance.firstName} {employee_model_instance.lastName}")
                employee_model_instance.firstName = "New Leslie"
                employee_manager.update_one(employee_model_instance)
        
            employee_result = employee_manager.select_all("lastName=%s", ("Thompson",))
            employee_model_instance = employee_result[0]
            print(f"Updated record id: {employee_model_instance.employeeNumber} - {employee_model_instance.firstName} {employee_model_instance.lastName}")
        
            # transaction update
            update_employee_first_name(1166, "Leslie forever")
        
            # new item
            new_employee = employee_manager.create_model_instance()
            new_employee.employeeNumber = 9999
            new_employee.firstName = "John"
            new_employee.lastName = "Doe"
            new_employee.extension = "xxx"
            new_employee.email = "a@b.c"
            new_employee.officeCode = 4
            new_employee.jobTitle = "Incognito"
            employee_manager.insert_one(new_employee)
        
            # delete item
            employee_manager.delete_one(new_employee)
            # OR
            employee_manager.delete_all(f"{EmployeesManager.MODEL_CLASS.Meta.employeeNumber}=%s", (9999,))
            # OR simply
            employee_manager.delete_all("employeeNumber=%s", (9999,))
        
        ```
        
        ### Working with Views
        `szndaogen` could process defined complicated database views too. There is no performance issue with MySQL views. Because view is parsed by `szndaogen` analyser and stored into `Model` definition. View declaration is executed on python application side. So all indexes and database optimalisations are used. Lets define sample view for out application defined as bellow `select` with a few joins:
        ```sql
        SELECT o.`orderNumber`, od.`productCode`, od.`quantityOrdered`, p.`productName`, p.`quantityInStock`,
        IF(p.`quantityInStock` > od.`quantityOrdered`, "enough", "to_stock_needed") AS productInStockStatus
        FROM orders AS o
        LEFT JOIN orderdetails AS od ON od.`orderNumber`=o.`orderNumber`
        LEFT JOIN products AS p ON p.`productName`
        WHERE o.`status`="In Process"
        ```
        create view `view_orders_to_be_processed` from it:
        ```sql
        DELIMITER $$
        
        CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_orders_to_be_processed` AS (
        SELECT
          `o`.`orderNumber`      AS `orderNumber`,
          `od`.`productCode`     AS `productCode`,
          `od`.`quantityOrdered` AS `quantityOrdered`,
          `p`.`productName`      AS `productName`,
          `p`.`quantityInStock`  AS `quantityInStock`,
          IF((`p`.`quantityInStock` > `od`.`quantityOrdered`),'enough','to_stock_needed') AS `productInStockStatus`
        FROM ((`orders` `o`
            LEFT JOIN `orderdetails` `od`
              ON ((`od`.`orderNumber` = `o`.`orderNumber`)))
           LEFT JOIN `products` `p`
             ON (`p`.`productName`))
        WHERE (`o`.`status` = 'In Process'))$$
        
        DELIMITER ;
        ```
        run `szndaogen` again by stored bash script:
        ```bash
        szndaogen-localhost-classicmodels.sh
        
        Writing model `Customers` into `example_dao/models/customers_model.py`
        Skipping manager `Customers` exists `example_dao/managers/customers_manager.py`
        Writing manager `Customers` into `example_dao/managers/base/customers_manager_base.py`
        Writing model `Employees` into `example_dao/models/employees_model.py`
        Skipping manager `Employees` exists `example_dao/managers/employees_manager.py`
        Writing manager `Employees` into `example_dao/managers/base/employees_manager_base.py`
        Writing model `Offices` into `example_dao/models/offices_model.py`
        Skipping manager `Offices` exists `example_dao/managers/offices_manager.py`
        Writing manager `Offices` into `example_dao/managers/base/offices_manager_base.py`
        Writing model `Orderdetails` into `example_dao/models/orderdetails_model.py`
        Skipping manager `Orderdetails` exists `example_dao/managers/orderdetails_manager.py`
        Writing manager `Orderdetails` into `example_dao/managers/base/orderdetails_manager_base.py`
        Writing model `Orders` into `example_dao/models/orders_model.py`
        Skipping manager `Orders` exists `example_dao/managers/orders_manager.py`
        Writing manager `Orders` into `example_dao/managers/base/orders_manager_base.py`
        Writing model `Payments` into `example_dao/models/payments_model.py`
        Skipping manager `Payments` exists `example_dao/managers/payments_manager.py`
        Writing manager `Payments` into `example_dao/managers/base/payments_manager_base.py`
        Writing model `Productlines` into `example_dao/models/productlines_model.py`
        Skipping manager `Productlines` exists `example_dao/managers/productlines_manager.py`
        Writing manager `Productlines` into `example_dao/managers/base/productlines_manager_base.py`
        Writing model `Products` into `example_dao/models/products_model.py`
        Skipping manager `Products` exists `example_dao/managers/products_manager.py`
        Writing manager `Products` into `example_dao/managers/base/products_manager_base.py`
        Writing model `ViewOrdersToBeProcessed` into `example_dao/models/vieworderstobeprocessed_model.py`
        Writing manager `ViewOrdersToBeProcessed` into `example_dao/managers/vieworderstobeprocessed_manager.py`
        Writing manager `ViewOrdersToBeProcessed` into `example_dao/managers/base/vieworderstobeprocessed_manager_base.py`
        ```
        and all work is done in a second. Now you can use `ViewOrdersToBeProcessedManager` with custom `WHERE` conditions, `ORDER BY` sorts, limits, etc.
        ```python
        from example_dao.managers.view_orders_to_be_processed_manager import ViewOrdersToBeProcessedManager
        
        manager = ViewOrdersToBeProcessedManager()
        results = manager.select_all(order_by=("`od`.`quantityOrdered` DESC",), limit=10)
        
        print("Top 10 ordered quauntities waiting for processing")
        for item in results:
            print(f"{item.orderNumber} - {item.productCode}: {item.productName}, {item.quantityOrdered}/{item.quantityInStock}")
        ```
        
        # Grouping tools
        Package`szndaogen` also comes with a set of helpful auto grouping tools placed in `szndaogen/tools/auto_group.py`.
        
        These tools solve a problem where you have M:N relations between two entities and need to join them together.  Their functionality is also helpful when working with SQL views.
        
        ```mysql
        SELECT name, surname, job.name as job___name
        FROM person
        JOIN
            works ON works.person_id = person.id
            job ON job.id = works.job_id
        WHERE
            person.id = 2;
        ``` 
        
        For a single person, who has two jobs, DB might output something like this:
        
        | name        | surname        | job___name |
        | ----------- | -------------- | ---------- |
        | Jane        | Doe            | Accountant |
        | Jane        | Doe            | Developer  |
        
         
        Which might be OK when fetching only one person and his jobs. There are use cases, when you need to fetch more and more people. Output will be much larger. This is the place, where auto grouping tools come handy.
         
        ## auto_group_dict
        This function groups dict keys with same prefix under one dict key. Groups used as group keys are identified by group separator `___`.
         
        ```python
        person = {
            "name": "Jane",
            "surname": "Doe",
            "job___name": "Accountant",
            "job___established": 2001
        }
        
        ret = auto_group_dict(person)
        
        # Returns in
        ret = {
            "name": "Jane",
            "surname": "Doe",
            "job": {
                "name": "Accountant",
                "established": 2001,
            }
        }
        ```
         
        ## auto_group_list
        __IMPORTANT__: All items which are inside lists are sorted exactly the same as they came from the DB.
        
        Let's say that we have want to retrieve a new person from our DB. Jane Doe now has two jobs: an accountant and a developer.
        
        Database returns two rows as specified above. But in object oriented world, it would be better for us to have it in one dict. This is where `auto_group_list` comes handy.
        
        ```sql
        SELECT person.name, person.surname, job.name as jobs__name
        FROM person
        JOIN
            works ON works.person_id = person.id
            job ON job.id = works.job_id
        WHERE
            person.id = 2;
        ```
        
        Assuming our SQL query returns 2 rows like this: 
        
        ```python
        rows = [
            {
                "name": "Jane",
                "surname": "Doe",
                "jobs__name": "Accountant",
            },
            {
                "name": "Jane",
                "surname": "Doe",
                "jobs__name": "Developer",
            }
        ]
        
        ret = auto_group_list(rows)
        
        # Returns in
        ret = {
            "name": "Jane",
            "surname": "Doe",
            "jobs": [
                {
                    "name": "Accountant"
                },
                {
                    "name": "Developer"
                }
            ]
        }
        ```
        
        This is kind of handy, isn't it? But what if we want to omit our WHERE statement? This is where `auto_group_list_by_pkeys` comes in place.
        
        ## auto_group_list_by_pkeys
        Next and the last useful is handy when you want to for example fetch multiple people from DB, keep m..n relations and have everything grouped nicely. Like so:
        ```sql
        SELECT person.id as _id, person.name, person.surname, job.name as jobs__name
        FROM person
        JOIN
            works ON works.person_id = person.id
            job ON job.id = works.job_id
        WHERE
            person.id IN (2, 3);
        ```
        Our person no. 2 is Jane Doe, who works as an accountant and a developer. Person no. 3 is John Doe, works as an DevOps Engineer and a developer.
        
        Let's say our grouping key is `_id`.
        
        Our fetched data converted to python might look something like this:
        ```python
        rows = [
            {
                "_id": 2,
                "name": "Jane",
                "surname": "Doe",
                "jobs__name": "Accountant"
            },
            {
                "_id": 2,
                "name": "Jane",
                "surname": "Doe",
                "jobs__name": "Developer"
            },
            {
                "_id": 3,
                "name": "John",
                "surname": "Doe",
                "jobs__name": "DevOps Engineer"
            },
            {
                "_id": 3,
                "name": "John",
                "surname": "Doe",
                "jobs__name": "Developer"
            }
        ]
        ```
        Let's make it prettier!
        ```python
        ret = auto_group_list_by_pkeys(("_id",), rows, use_auto_group_dict=True)
        
        # Returns dict with 2 items, grouped by key "_id"
        ret = {
            "2": {
                "_id": 2,
                "name": "Jane",
                "surname": "Doe",
                "jobs": [
                    {
                        "name": "Accountant"
                    },
                    {
                        "name": "Developer"
                    }
                ]
            },
            "3": {
                "_id": 3,
                "name": "John",
                "surname": "Doe",
                "jobs": [
                    {
                        "name": "DevOps Engineer"
                    },
                    {
                        "name": "Developer"
                    }
                ]
            }
        }
        ```
        Now we have all our cases covered, ready to go.
        
Platform: UNKNOWN
Classifier: Programming Language :: Python :: 3
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Requires-Python: >=3.6
Description-Content-Type: text/markdown
