Metadata-Version: 2.1
Name: aiopyql
Version: 0.3
Summary: Simple python database orchestration utility which makes it easy to add tables, insert, select, update, delete items with tables
Home-page: https://github.com/codemation/aiopyql
Author: Joshua Jamison
Author-email: joshjamison1@gmail.com
License: UNKNOWN
Platform: UNKNOWN
Classifier: Programming Language :: Python :: 3
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Requires-Python: >=3.6, <4
Description-Content-Type: text/markdown
Requires-Dist: aiosqlite
Requires-Dist: aiomysql

# aioaiopyql
Asyncio ORM(Object-relational mapping) for accessing, inserting, updating, deleting data within RBDMS tables using python, based on the synchronus ORM [pyql](https://github.com/codemation/pyql)

### Instalation

    $ virtualenv -p python3.7 aiopyql-env

    $ source aiopyql-env/bin/activate

#### Install with PIP

     (aiopyql-env)$ pip install aiopyql

#### Download & install Library from repo:

    (aiopyql-env)$ git clone https://github.com/codemation/aiopyql.git

Use install script to install the aiopyql into the activated environment libraries

    (aiopyql-env)$ cd aiopyql; sudo ./install.py install

### Compatable Databases - Currently

- mysql
- sqlite

## Getting Started 

A Database object can be created both in and out of an event loop, but the Database.create() factory coro ensures
load_tables() is processed to load existing tables. 

### DB connection

Sqlite3: Default

        from aiopyql import data
        import asyncio


        async def main():

            #sqlite connection
            sqlite_db = await data.Database.create(
                database="testdb" # if no type specified, default is sqlite
            )

            # mysql connection
            mysql_db = await data.Database.create(
                database='mysql_database',
                user='mysqluser',
                password='my-secret-pw',
                host='localhost',
                type='mysql'
                )

            # more db logic goes here

        loop = asyncio.new_event_loop()
        loop.run_until_complete(main())

Existing tables schemas within databases are loaded when database object is instantiated via .create and ready for use immedielty. 

If created using db = data.Database(database='testdb'), which is synchronus, the .load_tables() coro must be run manually within an event loop or _run_async_tasks utility func

        # synchronus 
        db = data.Database(database='testdb')
        db._run_async_tasks(db.load_tables())

_run_async_tasks cannot be used within running event loop


### Table Create
Requires List of at least 2 item tuples, max 3

('column_name', type, 'modifiers')

- column_name - str - database column name exclusions apply
- types: str, int, float, byte, bool, None # JSON dumpable dicts fall under str types
- modifiers: NOT NULL, UNIQUE, AUTO_INCREMENT

Note Some differences may apply for column options i.e AUTOINCREMENT(sqlite) vs AUTO_INCREMENT(mysql) - 
See DB documentation for reference.

Note: Unique constraints are not validated by aiopyql but at db, so if modifier is supported it will be added when table is created.

    # Table Create    
    await db.create_table(
        'stocks', 
        [    
            ('order_num', int, 'AUTO_INCREMENT'),
            ('date', str),
            ('trans', str),
            ('symbol', str),
            ('qty', float),
            ('price', str)
        ], 
        'order_num' # Primary Key 
    )

    mysql> describe stocks;
    +-----------+---------+------+-----+---------+----------------+
    | Field     | Type    | Null | Key | Default | Extra          |
    +-----------+---------+------+-----+---------+----------------+
    | order_num | int(11) | NO   | PRI | NULL    | auto_increment |
    | date      | text    | YES  |     | NULL    |                |
    | trans     | text    | YES  |     | NULL    |                |
    | condition | text    | YES  |     | NULL    |                |
    | symbol    | text    | YES  |     | NULL    |                |
    | qty       | double  | YES  |     | NULL    |                |
    | price     | text    | YES  |     | NULL    |                |
    +-----------+---------+------+-----+---------+----------------+
    6 rows in set (0.00 sec)

#### Creating Tables with Foreign Keys

    await db.create_table(
        'departments', 
        [    
            ('id', int, 'UNIQUE'),
            ('name', str)
        ], 
        'id' # Primary Key 
    )

    await db.create_table(
        'positions', 
        [    
            ('id', int, 'UNIQUE'),
            ('name', str),
            ('department_id', int)
        ], 
        'id', # Primary Key
        foreign_keys={
            'department_id': {
                'table': 'departments', 
                'ref': 'id',
                'mods': 'ON UPDATE CASCADE ON DELETE CASCADE'
            }
        }
    )

    await db.create_table(
        'employees', 
        [    
            ('id', int, 'UNIQUE'),
            ('name', str),
            ('position_id', int)
        ], 
        'id', # Primary Key
        foreign_keys={
            'position_id': {
                'table': 'positions', 
                'ref': 'id',
                'mods': 'ON UPDATE CASCADE ON DELETE CASCADE'
            }
        }
    )


### Insert Data
Requires key-value pairs - may be input using dict or the following

Un-packing

    # Note order_num is not required as auto_increment was specified
    trade = {'date': '2006-01-05', 'trans': 'BUY', 'symbol': 'RHAT', 'qty': 100.0, 'price': 35.14}
    await db.tables['stocks'].insert(**trade)

    query:
        INSERT INTO stocks (date, trans, symbol, qty, price) VALUES ("2006-01-05", "BUY", "RHAT", 100, 35.14)

In-Line

    # Note order_num is not required as auto_increment was specified
    await db.tables['stocks'].insert(
        date='2006-01-05', 
        trans='BUY',
        symbol='RHAT',
        qty=200.0,
        price=65.14
    )

    query:
        INSERT INTO stocks (date, trans, symbol, qty, price) VALUES ("2006-01-05", "BUY", "RHAT", 200, 65.14)

#### Inserting Special Data 
- Columns of type string can hold JSON dumpable python dictionaries as JSON strings and are automatically converted back into dicts when read. 
- Nested Dicts are also Ok, but all items should be JSON compatible data types


        tx_data = {
            'type': 'BUY', 
            'condition': {
                'limit': '36.00', 
                'time': 'end_of_trading_day'
            }
        }

        trade = {
            'order_num': 1, 'date': '2006-01-05', 
            'trans': tx_data, # 
            'symbol': 'RHAT', 
            'qty': 100, 'price': 35.14, 'after_hours': True
        }

        await db.tables['stocks'].insert(**trade)
        query:
            INSERT INTO stocks 
                (
                    order_num, 
                    date, trans, 
                    symbol, 
                    qty, 
                    price, 
                    after_hours
                ) 
                VALUES 
                    (
                        1, 
                        "2006-01-05", 
                        '{"type": "BUY", "condition": {"limit": "36.00", "time": "end_of_trading_day"}}', 
                        "RHAT", 
                        100, 
                        35.14, 
                        True
                    )
        result:
            In:
                sel = await db.tables['stocks'][1]
                print(sel['trans']['condition'])
            Out: #
                {'limit': '36.00', 'time': 'end_of_trading_day'}


### Select Data

All Rows & Columns in table

    await db.tables['employees'].select('*')

All Rows & Specific Columns 

    await db.tables['employees'].select(
        'id', 
        'name', 
        'position_id'
        )

All Rows & Specific Columns with Matching Values

    await db.tables['employees'].select(
        'id', 
        'name', 
        'position_id', 
        where={
            'id': 1000
            }
        )

All Rows & Specific Columns with Multple Matching Values

    await db.tables['employees'].select(
        'id', 
        'name', 
        'position_id', 
        where={
            'id': 1000, 
            'name': 'Frank Franklin'}
        )

#### Advanced Usage:

All Rows & Columns from employees, Combining ALL Rows & Columns of table positions (if foreign keys match)

    # Basic Join
    await db.tables['employees'].select('*', join='positions')
    query:
        SELECT * FROM employees JOIN positions ON employees.position_id = positions.id
    output:
        [
            {
                'employees.id': 1000, 
                'employees.name': 'Frank Franklin', 
                'employees.position_id': 100101, 
                'positions.name': 'Director', 
                'positions.department_id': 1001
            },
            ...
        ]
All Rows & Specific Columns from employees, Combining All Rows & Specific Columns of table positions (if foreign keys match)

    # Basic Join 
    await db.tables['employees'].select(
        'employees.name', 
        'positions.name', 
        join='positions' # # possible only if foreign key relation exists between employees & positions
        )
    query:
        SELECT employees.name,positions.name FROM employees JOIN positions ON employees.position_id = positions.id
    output:
        [
            {'employees.name': 'Frank Franklin', 'positions.name': 'Director'}, 
            {'employees.name': 'Eli Doe', 'positions.name': 'Manager'},
            ...
        ]

All Rows & Specific Columns from employees, Combining All Rows & Specific Columns of table positions (if foreign keys match) with matching 'position.name' value

    # Basic Join with conditions
    await db.tables['employees'].select(
        'employees.name', 
        'positions.name', 
        join='positions', # possible only if foreign key relation exists between employees & positions
        where={
            'positions.name': 'Director'}
        )
    query:
        SELECT 
            employees.name,
            positions.name 
        FROM 
            employees 
        JOIN positions 
            ON 
                employees.position_id = positions.id 
        WHERE 
            positions.name='Director'
    output:
        [
            {'employees.name': 'Frank Franklin', 'positions.name': 'Director'}, 
            {'employees.name': 'Elly Doe', 'positions.name': 'Director'},
            ..
        ]

All Rows & Specific Columns from employees, Combining Specific Rows & Specific Columns of tables positions & departments

Note: join='x_table' will only work if the calling table has a f-key reference to table 'x_table'

    # Multi-table Join with conditions
    await db.tables['employees'].select(
        'employees.name', 
        'positions.name', 
        'departments.name', 
        join={
            'positions': {
                'employees.position_id': 'positions.id'
                }, 
            'departments': {
                'positions.department_id': 'departments.id'
                }
        }, 
        where={
            'positions.name': 'Director'}
        )
    query:
        SELECT 
            employees.name,positions.name,
            departments.name 
        FROM 
            employees 
        JOIN 
            positions 
                ON 
                    employees.position_id = positions.id 
        JOIN 
            departments 
                ON 
                    positions.department_id = departments.id 
        WHERE 
            positions.name='Director'
    result:
        [
            {'employees.name': 'Frank Franklin', 'positions.name': 'Director', 'departments.name': 'HR'}, 
            {'employees.name': 'Elly Doe', 'positions.name': 'Director', 'departments.name': 'Sales'}
        ]

Special Note: When performing multi-table joins, joining columns must be explicity provided. 
<br>The key-value order is not explicity important, but will determine which column name is present in returned rows


    join={'y_table': {'y_table.id': 'x_table.y_id'}}
    result:
        [
            {'x_table.a': 'val1', 'y_table.id': 'val2'},
            {'x_table.a': 'val1', 'y_table.id': 'val3'}
        ]
OR

    join={'y_table': {'x_table.y_id': 'y_table.id'}}
    result:
        [
            {'x_table.a': 'val1', 'x_table.y_id': 'val2'},
            {'x_table.a': 'val1', 'x_table.y_id': 'val3'}
        ]

#### Operators

The Following operators are supported within the list query syntax

'=', '==', '<>', '!=', '>', '>=', '<', '<=', 'like', 'in', 'not in', 'not like'

Operator Syntax Requires a list-of-lists and supports multiple combined conditions

    #Syntax

    await db.tables['table'].select(
        '*',
        where=[[condition1], [condition2], [condition3]]
    )


    await db.tables['table'].select(
        '*',
        where=[
            ['col1', 'like', 'abc*'],               # Wildcards 
            ['col2', '<', 10],                      # Value Comparison
            ['col3', 'not in', ['a', 'b', 'c'] ]    # Inclusion / Exclusion
        ]
    )

Examples:

Search for rows which contain specified chars using wild card '*' 

    find_employee = await db.tables['employees'].select(
        'id', 
        'name',
        where=[
            ['name', 'like', '*ank*'] # Double Wild Card - Search
        ]
    )
    query:
        SELECT id,name FROM employees WHERE name like '%ank%'
    result:
        [
            {'id': 1016, 'name': 'Frank Franklin'}, 
            {'id': 1018, 'name': 'Joe Franklin'}, 
            {'id': 1034, 'name': 'Dana Franklin'}, 
            {'id': 1036, 'name': 'Jane Franklin'}, 
            {'id': 1043, 'name': 'Eli Franklin'}, 
        ]


Delete Rows matching value comparison


    delete_department = await db.tables['departments'].delete(
        where=[
            ['id', '<', 2000] # Value Comparison
        ]
    )
    query:
        DELETE 
            FROM 
                departments 
            WHERE 
                id < 2000

Select Rows using Join and exluding rows with sepcific values

    join_sel = db.tables['employees'].select(
        '*', 
        join={
            'positions': {
                'employees.position_id':'positions.id', 
                'positions.id': 'employees.position_id'
            }
        },
        where=[
            [
                'positions.name', 'not in', ['Manager', 'Intern', 'Rep'] # Exclusion within Join
            ],
            [
                'positions.department_id', '<>', 2001                    # Exclusion via NOT EQUAL
            ]
        ]
    )
    query:
        SELECT 
            * 
        FROM 
            employees 
        JOIN 
            positions 
            ON 
                employees.position_id = positions.id  
            AND  
                positions.id = employees.position_id 
        WHERE 
            positions.name not in ('Manager', 'Intern', 'Rep') 
        AND 
            positions.department_id <> 2001


#### Special Examples:
Bracket indexs can only be used for primary keys and return entire row, if existent

    await db.tables['employees'][1000] 

    query:
        SELECT * 
        FROM 
            employees 
        WHERE 
            id=1000
    result:
        {'id': 1000, 'name': 'Frank Franklin', 'position_id': 100101}

Note: As  db.tables['employees'][1000] returns an 'awaitable', sub keys cannot be specified until the object has been 'awaited'

    In: 
        # Wrong
        await db.tables['employees'][1000]['id']
    Out:
        __main__:1: RuntimeWarning: coroutine was never awaited
        RuntimeWarning: Enable tracemalloc to get the object allocation traceback
        Traceback (most recent call last):
        File "<stdin>", line 1, in <module>
        TypeError: 'coroutine' object is not subscriptable

    # Right
        sel = await db.tables['employees'][1000]
        sel['id]



Iterate through table - grab all rows - allowing client side filtering 

    async for row in db.tables['employees']:
        print(row['id], row['name'])
    query:
        SELECT * FROM employees
    result:
        1000 Frank Franklin
        1001 Eli Doe
        1002 Chris Smith
        1003 Clara Carson

Using list comprehension

    sel = [(row['id'], row['name']) async for row in db.tables['employees']]
    query:
        SELECT * FROM employees
    result:
        [
            (1000, 'Frank Franklin'), 
            (1001, 'Eli Doe'), 
            (1002, 'Chris Smith'), 
            (1003, 'Clara Carson'),
            ...
        ]

### Update Data

Define update values in-line or un-pack

    await db.tables['stocks'].update(
        symbol='NTAP',trans='SELL', 
        where={'order_num': 1}
        )
    query:
        UPDATE stocks 
                SET 
                    symbol = 'NTAP', 
                    trans = 'SELL' 
                WHERE 
                    order_num=1

Un-Pack

    # JSON Serializable Data 
    tx_data = {
        'type': 'BUY', 
        'condition': {
            'limit': '36.00', 
            'time': 'end_of_trading_day'
            }
        }
    to_update = {
        'symbol': 'NTAP', 
        'trans': tx_data # dict
        }

    await db.tables['stocks'].update(
        **to_update, 
        where={'order_num': 1}
        )
    query:
        UPDATE stocks 
            SET 
                symbol = 'NTAP', 
                trans = '{"type": "BUY", "condition": {"limit": "36.00", "time": "end_of_trading_day"}}' 
            WHERE 
                order_num=1

Bracket Assigment - Primary Key name assumed inside Brackets for value

    #JSON Serializable Data 

    tx_data = {
        'type': 'BUY', 
        'condition': {
            'limit': '36.00', 
            'time': 'end_of_trading_day'
            }
        }
    to_update = {
        'symbol': 'NTAP', 
        'trans': tx_data, # dict
        'qty': 500}

    # Synchronus only
    db.tables['stocks'][2] = to_update 

    # Asynchronus
    await db.tables['stocks'].set_item(2, to_update)

    query:
        # check that primary_key value 2 exists
        SELECT * FROM stocks WHERE order_num=2

        # update 
        UPDATE stocks 
            SET
                symbol = 'NTAP', 
                trans = '{"type": "BUY", "condition": {"limit": "36.00", "time": "end_of_trading_day"}}', 
                qty = 500 
            WHERE order_num=2

    result:
        await db.tables['stocks'][2]

        # beutified
        {
            'order_num': 2, 
            'date': '2006-01-05', 
            'trans': {
                'type': 'BUY', 
                'condition': {
                    'limit': '36.00', 
                    'time': 'end_of_trading_day'
                }
            }, 
            'symbol': 'NTAP', 
            'qty': 500, 
            'price': 35.16, 
            'after_hours': True
        }


### Delete Data 

    await db.tables['stocks'].delete(
        where={'order_num': 1}
        )

