dataferry 0.0.6
Simple ETL tools and functions for SQL Server
Requires Python: >=3.8

<h1>About</h1>

This package includes but is not limited to:

1. A class to create a connection to SQL Server.
2. A class to transfer data from one SQL Server to another.
3. Classes to transfer data to and from text files.
4. Functions to execute code as well as trap and log errors.
5. Functions to execute local SQL files.
6. Functions to update dimension tables.

Key features:

1. The data is transfered in chunks of 10,000 rows
    * There are never more than 10,000 records in memory at a time.
2. Basic data transformations include but are not limited to:
    * Applying calculations and derivaations
    * Changing data types of columns
3. Enabling logging will produce a file that records each chunk of data transfered.

<h1>Dependencies</h1>

**Required**

| Package	| Version	| License						|
|---------------|---------------|-------------------------------------------------------|
| pandas	| 1.3.5		| OSI Approved :: BSD License				|
| SQLAlchemy	| 1.3.5		| MIT License (MIT)					|

<h1>Config</h>

<h2>Create and populate a local config_params.ini file</h2>

Windows authentication example:

    [SOMEUSERFRIENDLYSERVERNAME]
    platform=sql_server
    server=SOMESERVERNAME
    database=SOMEDATABASE
    authentication=windows
    
Server authentication example:

    [SOMEOTHERUSERFRIENDLYSERVERNAME]
    platform=sql_server
    server=SOMESERVERNAME
    database=SOMEDATABASE
    authentication=server
    uid=SOMEGENERICUSERNAME
    pwd=SOMEGENERICPASSWORD

<h1>Connections</h>
    
<h2>Set the parameters and create the connections</h2>

    # import objects
    from dataferry import SqlServerConnection, Etl
    
    # define mandatory generic variables
    config_params_path = r'xxx\config_params.ini'
    
    # define optional generic variables
    log_path = r'xxx\log.log'
    
    # make connections
    source_connection = SqlServerConnection('MSSQLSVRA', config_params_path)
    destination_connection = SqlServerConnection('MSSQLSVRB', config_params_path)

<h1>ETL</h>
    
<h2>Create the ETL classes</h2>
    
    # define etl class variables
    source_sql = r'''SELECT * FROM TESTDB.dbo.tbl_test'''
    destination_database = 'TESTDB'
    destination_table = 'tbl_test'
    
    # define optional etl variables
    xforms = [
        "my_chunk['test_id'] = my_chunk['test_id'] * 10"
        , "my_chunk['test_id'] = my_chunk['test_id'].astype(str)"
        ]
    # destination_schema = 'SOMESCHEMA'
    
    # create etl class
    etl_a = Etl(source_sql, destination_database, destination_table, source_connection, destination_connection
        , xforms=xforms
        # , schema=destination_schema
        , log_path=log_path)
    
    # create other etl classes as required
    
    # create a list of etl classes
    xfers = [
        etl_a
        , # etl_b
        ]

<h2>Execute the ETL classes</h2>

    # iterate through the etl list and execute the etl classes
    for xf in xfers:
        
        # either drop or truncate the destination table
        xf.drop_table()
        # xf.truncate_table()
        
        # transfer the data
        xf.transfer_data()

<h1>Disclaimer</h1>

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.