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]
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
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
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
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
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
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
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)
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
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
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)
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)