コード例 #1
0
def test_logging_copy_rows(caplog, level, expected, pgtestdb_conn,
                           pgtestdb_test_tables, pgtestdb_insert_sql,
                           test_table_data):
    # Arrange
    caplog.set_level(level, logger=logger.name)
    select_sql = "SELECT * FROM src"
    insert_sql = pgtestdb_insert_sql.replace('src', 'dest')

    # Act
    copy_rows(select_sql,
              pgtestdb_conn,
              insert_sql,
              pgtestdb_conn,
              chunk_size=1)

    # ID for connection object and hostname vary between tests
    # and test environments
    messages = [
        re.sub(r'object at .*;', 'object at ???;', m) for m in caplog.messages
    ]
    messages = [re.sub(r'host=.*? ', 'host=??? ', m) for m in messages]
    messages = [
        re.sub(r'port=[0-9]{1,5}\'', 'port=???\'', m) for m in messages
    ]

    # Assert
    for i, message in enumerate(messages):
        assert message == expected[i]
コード例 #2
0
def test_copy_rows_with_dict_row_factory(pgtestdb_conn, pgtestdb_test_tables,
                                         pgtestdb_insert_sql, test_table_data):
    # Arrange
    select_sql = "SELECT * FROM src"
    insert_sql = """
        INSERT INTO dest (id, value, simple_text, utf8_text, day, date_time)
        VALUES (
            %(id)s, %(value)s, %(simple_text)s,
            %(utf8_text)s, %(day)s, %(date_time)s
        );
    """

    expected = [(1001, 1.234, 'TEXT', 'Öæ° z', date(2018, 12, 7),
                 datetime(2018, 12, 7, 13, 1, 59)),
                (1002, 2.234, 'TEXT', 'Öæ° z', date(2018, 12, 8),
                 datetime(2018, 12, 8, 13, 1, 59)),
                (1003, 2.234, 'TEXT', 'Öæ° z', date(2018, 12, 9),
                 datetime(2018, 12, 9, 13, 1, 59))]

    # Act
    copy_rows(select_sql,
              pgtestdb_conn,
              insert_sql,
              pgtestdb_conn,
              transform=transform_modify_dict,
              row_factory=dict_row_factory)

    # Assert
    sql = "SELECT * FROM dest"
    result = get_rows(sql, pgtestdb_conn)
    assert result == expected
コード例 #3
0
def test_copy_rows_happy_path(test_tables, testdb_conn, test_table_data):
    # Arrange and act
    select_sql = "SELECT * FROM src"
    insert_sql = INSERT_SQL.format(tablename='dest')
    copy_rows(select_sql, testdb_conn, insert_sql, testdb_conn)

    # Assert
    sql = "SELECT * FROM dest"
    result = get_rows(sql, testdb_conn)

    assert result == test_table_data
コード例 #4
0
def test_copy_rows_happy_path(pgtestdb_conn, pgtestdb_test_tables,
                              pgtestdb_insert_sql, test_table_data):
    # Arrange and act
    select_sql = "SELECT * FROM src"
    insert_sql = pgtestdb_insert_sql.replace('src', 'dest')
    copy_rows(select_sql, pgtestdb_conn, insert_sql, pgtestdb_conn)

    # Assert
    sql = "SELECT * FROM dest"
    result = iter_rows(sql, pgtestdb_conn)
    assert list(result) == test_table_data
コード例 #5
0
def test_copy_rows_happy_path_fast_true(test_tables, testdb_conn, testdb_conn2,
                                        test_table_data):
    # Note: ODBC driver requires separate connections for source and destination,
    # even if they are the same database.
    # Arrange and act
    select_sql = "SELECT * FROM src"
    insert_sql = INSERT_SQL.format(tablename='dest')
    copy_rows(select_sql, testdb_conn, insert_sql, testdb_conn2)

    # Assert
    sql = "SELECT * FROM dest"
    result = get_rows(sql, testdb_conn)
    assert result == test_table_data
コード例 #6
0
def test_copy_rows_happy_path(test_tables, testdb_conn, test_table_data):
    # Arrange and act
    select_sql = "SELECT * FROM src"
    insert_sql = INSERT_SQL.format(tablename='dest')
    copy_rows(select_sql, testdb_conn, insert_sql, testdb_conn)

    # Assert
    sql = "SELECT * FROM dest"
    result = get_rows(sql, testdb_conn)

    # Fix result date and datetime strings to native classes
    fixed_dates = []
    for row in result:
        fixed_dates.append((*row[:4], row.DAY.date(), row.DATE_TIME))

    assert fixed_dates == test_table_data
コード例 #7
0
def test_copy_rows_transform(pgtestdb_conn, pgtestdb_test_tables, my_transform):
    # Arrange
    select_sql = "SELECT * FROM src"
    insert_sql = "INSERT INTO dest (id) VALUES (%s)"

    expected = [(2, None, None, None, None, None),
                (3, None, None, None, None, None)]

    # Act
    copy_rows(select_sql, pgtestdb_conn, insert_sql, pgtestdb_conn,
              transform=my_transform)

    # Assert
    sql = "SELECT * FROM dest"
    result = iter_rows(sql, pgtestdb_conn)
    assert list(result) == expected
コード例 #8
0
def test_copy_rows_happy_path(test_tables, testdb_conn, test_table_data):
    # Arrange and act
    select_sql = "SELECT * FROM src"
    insert_sql = INSERT_SQL.format(tablename='dest')
    copy_rows(select_sql, testdb_conn, insert_sql, testdb_conn)

    # Assert
    sql = "SELECT * FROM dest"
    result = get_rows(sql, testdb_conn)

    # Oracle returns date object as datetime, convert test data to compare
    for i, row in enumerate(test_table_data):
        row_with_datetimes = [datetime.combine(x, datetime.min.time())
                              if isinstance(x, date) and not isinstance(x, datetime)
                              else x
                              for x in row]
        assert result[i] == tuple(row_with_datetimes)
コード例 #9
0
ファイル: test_sqlite.py プロジェクト: ximenesuk/etlhelper
def test_copy_rows_happy_path(test_tables, testdb_conn, test_table_data):
    # Arrange and act
    select_sql = "SELECT * FROM src"
    insert_sql = INSERT_SQL.format(tablename='dest')
    copy_rows(select_sql, testdb_conn, insert_sql, testdb_conn)

    # Assert
    sql = "SELECT * FROM dest"
    result = get_rows(sql, testdb_conn)

    # Fix result date and datetime strings to native classes
    fixed = []
    for row in result:
        fixed.append(
            (*row[:4], dt.datetime.strptime(row.day, '%Y-%m-%d').date(),
             dt.datetime.strptime(row.date_time, '%Y-%m-%d %H:%M:%S')))

    assert fixed == test_table_data
コード例 #10
0
def test_copy_rows_happy_path_deprecated_tables_fast_true(
        test_deprecated_tables, testdb_conn, testdb_conn2, test_table_data):
    # Note: ODBC driver requires separate connections for source and destination,
    # even if they are the same database.
    # Arrange and act
    select_sql = "SELECT * FROM src"
    insert_sql = INSERT_SQL.format(tablename='dest')
    with pytest.warns(UserWarning) as record:
        copy_rows(select_sql, testdb_conn, insert_sql, testdb_conn2)

    # Assert
    assert len(record) == 1
    assert str(
        record[0].message).startswith("fast_executemany execution failed")

    sql = "SELECT * FROM dest"
    result = get_rows(sql, testdb_conn)
    assert result == test_table_data
コード例 #11
0
def copy_src_to_dest():
    delete_sql = table_delete_query(D_TABLE)  # USE THIS TO CLEAR DESTINATION FOR IDEMPOTENCE

    src_conn = get_source_connection()
    print('Connected to source')
    dest_conn = get_destination_connection()
    print('Connected to destination')

    s_tables = []
    if DCLEAR:
        execute(delete_sql, dest_conn)
        print('Cleared destination')

    if engine == ENGINES['mysql']:
        mysql_copy_src_to_dest(src_conn, s_tables, dest_conn)
    else:
        if S_TABLES == '__all__':
            tables_query = get_tables_query()
            rows = get_rows(tables_query, src_conn)
            for row in rows:
                s_tables.append(row.tablename)
        else:
            s_tables = S_TABLES.split(",")

        for S_DB_TABLE in s_tables:
            record_count = get_rows("select count(*) from {0}".format(S_DB_TABLE), src_conn)[0].count
            offset = 0
            limit = 50000
            print('Copying {0} records from {1}'.format(record_count, S_DB_TABLE))
            while record_count > 0:
                select_sql = table_select_query(S_DB_TABLE, src_conn, offset=offset, limit=limit)
                print('Table: {} | Records remaining: {} | Limit: {} | Offset: {} '.format(S_DB_TABLE, record_count,limit, offset))
                insert_sql = table_insert_query(D_TABLE)
                copy_rows(select_sql, src_conn, insert_sql, dest_conn)
                record_count -= limit
                offset += limit

            create_view(S_DB_TABLE, dest_conn)

    refresh_mat_views(dest_conn)
コード例 #12
0
def test_copy_rows_bad_param_style(test_tables, testdb_conn, test_table_data):
    # Arrange and act
    select_sql = "SELECT * FROM src"
    insert_sql = BAD_PARAM_STYLE_SQL.format(tablename='dest')
    with pytest.raises(ETLHelperInsertError):
        copy_rows(select_sql, testdb_conn, insert_sql, testdb_conn)