Simple extract, transform\filter and load process in Python.

Simple_etl_proc.py

# -*- coding: UTF-8 -*-

import pyodbc


conn_tmd = pyodbc.connect(r'DSN=DEV_DWH_TMD;UID=informatica-repos;PWD=HswfhmLjhju2')
conn_buff_1c = pyodbc.connect(r'DSN=DWH_1C;UID=dwh_dev;PWD=sd0%7gsh3874')
conn_gis_src = pyodbc.connect(r'DSN=DEV_DWH_GIS;UID=appviews;PWD=g*3MDqEP2ghj')
conn_op_src = pyodbc.connect(r'DSN=DWH_OP;UID=Infaipc_prod_dwh;PWD=HfcnhjdsqHbceyjr4')
conn_sf_src = pyodbc.connect(r'DSN=DWH_SF_src;UID=Infaipc_prod_dwh;PWD={Dsldb;yjqZobr82}')
# conn_ods = pyodbc.connect(r'DSN=DEV_DWH_tgt;UID=dwh_dev;PWD=sd0%7gsh3874')

query1 = u"""
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, 2 AS grp, 's_m_EDWODS_1C_' AS session
    FROM [DWH_BUFF_1C].INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'
        AND TABLE_NAME NOT LIKE 'Test%'
        AND TABLE_NAME NOT LIKE '%test'
        AND TABLE_NAME NOT LIKE '%test%'
        AND TABLE_NAME NOT LIKE '%tst%'
    UNION ALL
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, 0 AS grp, 's_m_EDWODS_' AS session
    FROM [DWH_ODS_dev].INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'
        AND TABLE_NAME NOT LIKE 'Test%'
        AND TABLE_NAME NOT LIKE '%test'
        AND TABLE_NAME NOT LIKE '%test%'
        AND TABLE_NAME NOT LIKE '%tst%'
    """
query2 = u"""
    select table_catalog, table_schema, table_name, 1 AS grp, 's_m_EDWODS_GIS_' AS session
    from information_schema.tables
    where table_type = 'BASE TABLE'
    and table_schema in ('dicts','onecupp', 'geodata', 'salesforce');
    """
query3 = u"""
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, 3 AS grp, 's_m_EDWODS_OP_' AS session
    FROM wp.INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'
        AND TABLE_NAME NOT LIKE 'Test%'
        AND TABLE_NAME NOT LIKE '%test'
        AND TABLE_NAME NOT LIKE '%test%'
        AND TABLE_NAME NOT LIKE '%tst%'
    """
query4 = u"""
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, 4 AS grp, 's_m_EDWODS_SF_' AS session
    FROM AT_SF_PRODUCTION.INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'
        AND TABLE_NAME NOT LIKE 'Test%'
        AND TABLE_NAME NOT LIKE '%test'
        AND TABLE_NAME NOT LIKE '%test%'
        AND TABLE_NAME NOT LIKE '%tst%'
    """
query_insert_groups = u"""
    INSERT INTO [develop].[EDWTMD].[LOAD_GROUP] 
    (ID, NAME, WF_NAME)
    VALUES (?,?,?)
    """
query_insert_tables = u"""
    INSERT INTO [develop].[EDWTMD].[LOAD_TABLE] 
    (ID, GRP_ID, TABLE_NAME, SESS_NAME)
    VALUES (?,?,?,?)
    """
query_tmd_last_id = u"""
    SELECT MAX(ID) FROM [develop].[EDWTMD].[LOAD_TABLE]
    """
cur_tmd = conn_tmd.cursor()
cur_buff_1c = conn_buff_1c.cursor()
cur_buff_1c.execute(query1)
cur_gis_src = conn_gis_src.cursor()
cur_gis_src.execute(query2)
cur_op_src = conn_op_src.cursor()
cur_op_src.execute(query3)
cur_sf_src = conn_sf_src.cursor()
cur_sf_src.execute(query4)

last_tmd_tables_id = cur_tmd.execute(query_tmd_last_id).fetchall()[0][0]
last_tmd_tables_id = 0 if last_tmd_tables_id is None else last_tmd_tables_id

tables = cur_sf_src.fetchall() + cur_buff_1c.fetchall() + cur_gis_src.fetchall() + cur_op_src.fetchall()
tables_to_load = []
for t in tables:
    if t[3] != 0:
        last_tmd_tables_id += 1
        tables_to_load.append((last_tmd_tables_id, t[3], t[1] + '.' + t[2], t[4] + t[2].upper()))
    else:
        last_tmd_tables_id += 1
        if t[1] == u'GIS':
            tables_to_load.append((last_tmd_tables_id, 1, t[1] + '.' + t[2], t[4] + 'GIS_' + t[2].upper()))
        elif t[1] == u'OneC':
            tables_to_load.append((last_tmd_tables_id, 2, t[1] + '.' + t[2], t[4] + '1C_' + t[2].upper()))
        elif t[1] == u'OpenProject':
            tables_to_load.append((last_tmd_tables_id, 3, t[1] + '.' + t[2], t[4] + 'OP_' + t[2].upper()))
        elif t[1] == u'SalesForce':
            tables_to_load.append((last_tmd_tables_id, 4, t[1] + '.' + t[2], t[4] + 'SF_' + t[2].upper()))

for tl in tables_to_load:
    cur_tmd.execute(query_insert_tables, tl[0], tl[1], tl[2], tl[3])
cur_tmd.commit()