def dump_to_dest(self, src_conn_id: str, t_schema: str, t_name: str):
"""
Carrega dump da origem para o destino
"""
# avoid parse to python dictionary (keeps postgres json)
register_adapter(dict, Json)
register_json(oid=3802, array_oid=3807, globally=True)
src_hook = PostgresHook(postgres_conn_id=src_conn_id)
src_conn = src_hook.get_conn()
src_cursor = src_conn.cursor()
src_cursor.execute(f'select count(0) from {t_name};')
qtd = src_cursor.fetchone()[0]
dest_cursor = self.conn.cursor()
dest_cursor.execute(f'TRUNCATE TABLE {t_schema}.{t_name};')
self.conn.commit()
if qtd > 0:
with tempfile.NamedTemporaryFile() as temp_file:
print('Gerando dump tabela:', t_name, 'linhas:', qtd)
src_hook.bulk_dump(t_name, temp_file.name)
print('Carregando dump tabela:', f'{t_schema}.{t_name}',
'linhas:', qtd)
self.hook.bulk_load(f'{t_schema}.{t_name}', temp_file.name)
else:
print('Não foi gerado dump tabela:', t_name,
'pois possui 0 registros')
def test_bulk_dump(self):
hook = PostgresHook()
input_data = ["foo", "bar", "baz"]
with hook.get_conn() as conn:
with conn.cursor() as cur:
cur.execute("CREATE TABLE {} (c VARCHAR)".format(self.table))
values = ",".join("('{}')".format(data) for data in input_data)
cur.execute("INSERT INTO {} VALUES {}".format(self.table, values))
conn.commit()
with NamedTemporaryFile() as f:
hook.bulk_dump(self.table, f.name)
f.seek(0)
results = [line.rstrip().decode("utf-8") for line in f.readlines()]
self.assertEqual(sorted(input_data), sorted(results))
def test_bulk_dump(self):
hook = PostgresHook()
input_data = ["foo", "bar", "baz"]
with hook.get_conn() as conn:
with conn.cursor() as cur:
cur.execute("CREATE TABLE {} (c VARCHAR)".format(self.table))
values = ",".join("('{}')".format(data) for data in input_data)
cur.execute("INSERT INTO {} VALUES {}".format(self.table, values))
conn.commit()
with NamedTemporaryFile() as f:
hook.bulk_dump(self.table, f.name)
f.seek(0)
results = [line.rstrip().decode("utf-8") for line in f.readlines()]
self.assertEqual(sorted(input_data), sorted(results))
def dump_data(table: str):
pg_hook = PostgresHook(postgres_conn_id='my_postgres_conn',
schema='breakfast')
pg_hook.bulk_dump(table, f'/usr/local/airflow/dags/{table}_export')