def main():
args=parse_cli_args()
output_directory=None
schemas=[]
output_directory=os.path.abspath(args.o)
schemas=[args.s]
# sqlserver = db_utils.Connection(dbschema='dbo', database='enforce', dbtype='MSSQL', host=host, commit=False)
db_postgres = db_utils.DB(schema=logging, dbname=pgdatabase,
host=pghost)
#schemas = get_schema_except(db_postgres, ['op_dba', 'public', 'pg_catalog', 'information_schema', 'citus', 'sys', 'sqitch'])
for s in schemas:
db_postgres = db_utils.DB(schema=s, dbname=pgdatabase,
host=pghost)
#migu.change_table_owner(db_postgres, s, 'operational_dba')
#migu.change_view_owner(db_postgres, s, 'operational_dba')
static_func.print_create_table(db_postgres, folder=output_directory,
targetschema=s, file_prefix='{}.'.format(s))
static_func.print_create_functions(db_postgres, folder=output_directory,
targetschema=s, file_prefix='{}.'.format(s))
static_func.print_create_views(db_postgres, folder=output_directory,
targetschema=s, file_prefix='{}.'.format(s))
def test_postgres(self):
from shutil import copyfile
src = os.path.join(curr_file_path, 'sample_data/AgeRange.mdb')
dst = os.path.join(curr_file_path, TEST_OUTPUT_DIR, 'msaccess.mdb')
copyfile(src, dst)
x = postgres.DB(port=PORT, userid=USERID, host=HOST,
pwd=PASSWORD, dbname=DATABASE,loglevel=logging.level)
assert isinstance(x, postgres.DB)
# We don't want to put data into MsAccess we want to get away from access
self.populate_test_table(DB=x, fqn_table_name=TEST_TABLE)
z = x.get_all_tables()
print(z)
y = x.get_table_columns(TEST_TABLE)
# make sure we log error
z = x.connect_SqlAlchemy()
file = os.path.join(
curr_file_path, TEST_OUTPUT_DIR, 'test_postres.csv')
x.query_to_file(file, 'select * from {}'.format(TEST_TABLE),
header=y, file_format='CSV')
print(pd.read_csv(file))
file = os.path.join(curr_file_path, TEST_OUTPUT_DIR, 'test.parquet')
x.query_to_file(
file, 'select * from {}'.format(TEST_TABLE), file_format='PARQUET')
print(pd.read_parquet(file, engine='pyarrow'))
file = os.path.join(curr_file_path, TEST_OUTPUT_DIR, 'test.hdf5')
x.query_to_file(file, 'select * from {}'.format(TEST_TABLE),
file_format='HDF5', hdf5_key='table')
print(pd.read_hdf(file, 'table'))
def test_03_move_files(self):
self.pio = []
for project in yaml_config:
incoming = yaml_config[project]['incoming']
outgoing = yaml_config[project]['outgoing']
outgoing_path = yaml_config[project]['outgoing']['path']
logger = logging.getLogger()
pio = ProjectIO(project=project,
logger=logger,
incoming=incoming,
outgoing=outgoing)
pio.incoming.save_all(SESS)
pio.outgoing.move_files(pio.incoming.files)
self.pio.append(
pio
) #saving this so we can test base on values that current exists in this object
for f in pio.incoming.files:
new_path = os.path.join(outgoing_path, os.path.basename(f))
print("Checking file in Out folder: ", new_path)
self.assertTrue(os.path.isfile(new_path))
#verify db logic here since the meata data still exits
db = dbconn.DB()
for p in self.pio:
for f in p.incoming.files:
print(f)
class Test_db_utils_postgres(unittest.TestCase):
HOST = 'pgdb'
DATABASE = 'postgres'
USERID = 'docker'
DBTYPE = 'POSTGRES'
DATA_SCHEMA = 'prey'
DBPASSWORD = '******'
DBPORT = 5432
SAMPLE_DATA_LINE_COUNT = 1500
SAMPLE_DATA_TOTAL_TABLES = 8 # None will get all tables
CLEAN_PREV = False
GENERATE_SAMPLE_DATA = False
GENERATE_SAMPLE_DATA_W_HEADER = True
SAMPLE_DATA_HAS_HEADER = False
GENERATE_CRC = False
GENERATE_FILE_ID = False
LIMIT_ROWS = None
START_ROW = 2
TRUNCATE_TABLE = True
db = db_utils.DB(host=HOST,
userid=USERID,
dbname=DATABASE,
schema=DATA_SCHEMA,
pwd=DBPASSWORD,
port=DBPORT)
db.execute("create schema {}".format(LOGGING_SCHEMA))
db.execute("drop schema {} cascade".format(TEST_SCHEMA))
db.execute("create schema {}".format(TEST_SCHEMA))
db.execute("create schema {}".format(DATA_SCHEMA))
dirs = {
'sample_data_dir': "/workspace/_sample_data/",
'sample_working_dir': "/workspace/_sample_working_dir/",
'sample_zip_data_dir': "/workspace/_sample_zip_data/"
}
data = None
vw_file = '/workspace/tests/sql/logging_tables.sql'
#with open(vw_file,'r') as file:
# data = file.read().replace('\n', '')
db.create_cur()
db.cursor.execute(open(vw_file, "r").read())
def test_data_load(self):
self.db.execute(self.data)
self.db.execute("truncate table logging.meta_source_files")
print('# In function:', sys._getframe().f_code.co_name)
import py_dbmigration.data_load as data_load
data_load.main(yamlfile='/workspace/tests/data_load_msaccess.yaml',
write_path=self.dirs['sample_working_dir'],
schema=TEST_SCHEMA)
rs, meta = self.db.query(
"""select database_table from logging.meta_source_files
where database_table is not NULL""")
for row in rs:
print("Table Name: ", row[0])
pprint.pprint(
self.db.query("select * from {} limit 1".format(row[0])))
def test_postgres(self):
x = postgres.DB(port=PORT,
pwd=PASSWORD,
userid=USERID,
loglevel=logging.level)
self.clean_test_db(x)
try:
fail_db = postgres.DB(loglevel=logging.level) #purpose fail
except Exception as e:
print("Purposely Fail test Destroy", e)
self.populate_test_table(x)
x.execute("truncate table test.test")
x.commit()
x_cols = x.get_table_columns('test.test')
z, = (x.get_a_row('select 1 as col1 '))
def test_bulk_load_dataframe(self):
db = postgres.DB(port=PORT, userid=USERID, host=HOST,
pwd=PASSWORD, dbname=DATABASE,loglevel=logging.level)
df = pd.read_csv(TEST_CSV_FILE)
print(db.get_table_columns(TEST_TABLE))
#db.execute('truncate table test.test')
db.bulk_load_dataframe(
dataframe=df, table_name_fqn=TEST_TABLE, encoding='utf8', workingpath='MEMORY')
db.execute('truncate table {}'.format(TEST_TABLE))
db.bulk_load_dataframe(dataframe=df, table_name_fqn=TEST_TABLE, encoding='utf8',
workingpath=os.path.join(curr_file_path, TEST_OUTPUT_DIR))
print(db.query('select * from {}'.format(TEST_TABLE)))
def main(yamlfile=None, write_path=None, schema=None, logging_mode=None):
import sys
import argparse
parser = argparse.ArgumentParser()
parser.add_argument('--yaml')
parser.add_argument('--logging')
args = parser.parse_args()
logging_set = args.logging or logging_mode
if logging_set is 'debug':
logging.setLevel(log.DEBUG)
else:
logging.setLevel(log.INFO)
datafiles = None
if args.yaml is not None:
datafiles = process_yaml(os.path.abspath(args.yaml))
else:
datafiles = process_yaml(yamlfile)
writable_path = os.getenv('WORKINGPATH', write_path) or os.getcwd()
PGDATASCHEMA = os.getenv('PGDATASCHEMA', schema)
if len(datafiles) > 0:
db = db_utils.DB(schema=PGDATASCHEMA)
# db.truncate_table("logging", "meta_source_files")
df = dfm.data_files.DataFile(writable_path, db, datafiles)
df.init_db()
df.reset_meta_table(db, 'FAILED', where_clause=" (1=1) ")
df.do_work(db, cleanup=False, skip_ifexists=False)
db.execute('vacuum analyze logging.meta_source_files')
else:
logging.info("No configruation Items found...Exiting.")
def get_pg_database(self):
from py_dbutils.rdbms import postgres as db_utils
HOST = os.environ['PGHOST'] or 'localhost'
DATABASE = os.environ['PGDATABASE'] or 'postgres'
DBPORT= os.environ['PGPORT'] or 5432
USERID = os.environ['PGUSER'] or 'docker'
DBPASSWORD = os.environ['PGPASSWORD'] or 'docker'
db = db_utils.DB(host=HOST, userid=USERID, dbname=DATABASE, schema=self.TEST_SCHEMA,
pwd=DBPASSWORD, port=DBPORT)
db.execute("create schema {}".format(self.LOGGING_SCHEMA))
db.execute("drop schema {} cascade".format(self.TEST_SCHEMA))
db.execute("create schema {}".format(self.TEST_SCHEMA))
sql_files=[]
#with open(vw_file,'r') as file:
# data = file.read().replace('\n', '')
# db.create_cur()
# for f in sql_files:
# with open(f,'r') as sql_file:
# db.cursor.execute(sql_file.read())
return db
if copy_status != load_status:
raise Exception(
"Rows Loaded Do not match nows Dumped!Please Check this file:{0}".
format(file_name))
else:
os.remove(file_name)
return load_status
logging.basicConfig(level='DEBUG')
writable_path = os.environ['WORKINGPATH']
src_db = db_utils.DB(host=os.environ['PGHOST_INTERNAL'],
port=os.environ['PGPORT_INTERNAL'],
database=os.environ['PGDATABASE_INTERNAL'],
dbschema=os.environ['PGDATABASE_INTERNAL'],
userid=os.environ['PGUSER_INTERNAL'],
password=os.environ['PGPASSWORD_INTERNAL'])
trg_db = db_utils.DB(host=os.environ['PGHOST_EXTERNAL'],
port=os.environ['PGPORT_EXTERNAL'],
database=os.environ['PGDATABASE_EXTERNAL'],
dbschema=os.environ['PGDATABASE_EXTERNAL'],
userid=os.environ['PGUSER_EXTERNAL'],
password=os.environ['PGPASSWORD_EXTERNAL'])
database_name = os.environ['PGDATABASE_INTERNAL']
record_keeper = db_table.db_table_func.RecordKeeper(
trg_db, db_table.db_table_def.PublishLog)
# row = t.get_record(db_table.MetaSourceFiles.id == self.meta_source_file_id)
class Test_db_utils_postgres(unittest.TestCase):
HOST = 'pgdb'
DATABASE = 'postgres'
USERID = 'docker'
DBTYPE = 'POSTGRES'
DATA_SCHEMA = 'prey'
DBPASSWORD = '******'
DBPORT = 5432
SAMPLE_DATA_LINE_COUNT = 1500
SAMPLE_DATA_TOTAL_TABLES = 8 # None will get all tables
CLEAN_PREV = False
GENERATE_SAMPLE_DATA = False
GENERATE_SAMPLE_DATA_W_HEADER = True
SAMPLE_DATA_HAS_HEADER = False
GENERATE_CRC = False
GENERATE_FILE_ID = False
LIMIT_ROWS = None
START_ROW = 2
TRUNCATE_TABLE = True
db = db_utils.DB(host=HOST,
userid=USERID,
dbname=DATABASE,
schema=DATA_SCHEMA,
pwd=DBPASSWORD,
port=DBPORT)
db.execute("create schema {}".format(LOGGING_SCHEMA))
db.execute("create schema {}".format(TEST_SCHEMA))
db.execute("create schema {}".format(DATA_SCHEMA))
dirs = {
'sample_data_dir': "./_sample_data/",
'sample_working_dir': "./_sample_working_dir/",
'sample_zip_data_dir': "./_sample_zip_data/"
}
def test_00_init(self):
print('# In function:', sys._getframe().f_code.co_name)
for x in self.dirs:
os.makedirs(name=self.dirs[x], exist_ok=True)
#@unittest.skip("Skipping for now")
def test_08_walkdir_data_file(self):
print('# In function:', sys._getframe().f_code.co_name)
# datafiles = dfm.DataFile([dfm.FilesOfInterest('account', r'^d.*.txt', '', None, self.schema, has_header=self.SAMPLE_DATA_HAS_HEADER)]
print("Truncating Logging Tables:")
self.db.execute(
"TRUNCATE table logging.meta_source_files, logging.table_file_regex, logging.error_log, logging.load_status RESTART IDENTITY;"
)
# This is how we store the files we are looking for List of FileOfInterest
foi_list = [
data_files.FilesOfInterest('CSV',
file_regex=r".*\.csv",
file_path=self.dirs["sample_data_dir"],
parent_file_id=0,
project_name=PROJECT_NAME)
]
foi_list.append(
data_files.FilesOfInterest(
'ZIP',
file_regex=r".*\.zip",
file_path=self.dirs["sample_zip_data_dir"],
parent_file_id=0,
project_name=PROJECT_NAME))
df = data_files.DataFile(working_path=self.dirs["sample_working_dir"],
db=self.db,
foi_list=foi_list,
parent_file_id=0)
df.init_db()
assert isinstance(df, data_files.DataFile)
#result_set = self.db.query("select * from logging.meta_source_files")
#self.assertGreater(len(result_set), 0, "No files Found: Check Regex Logic")
df.do_work(self.db, cleanup=False, limit_rows=None)
class Test_db_utils_postgres(unittest.TestCase):
HOST = 'pgdb'
DATABASE = 'postgres'
USERID = 'docker'
DBTYPE = 'POSTGRES'
DATA_SCHEMA = 'prey'
DBPASSWORD = '******'
DBPORT = 5432
SAMPLE_DATA_LINE_COUNT = 1500
SAMPLE_DATA_TOTAL_TABLES = 8 # None will get all tables
CLEAN_PREV = False
GENERATE_SAMPLE_DATA = False
GENERATE_SAMPLE_DATA_W_HEADER = True
SAMPLE_DATA_HAS_HEADER = False
GENERATE_CRC = False
GENERATE_FILE_ID = False
LIMIT_ROWS = None
START_ROW = 2
TRUNCATE_TABLE = True
db = db_utils.DB(host=HOST,
userid=USERID,
dbname=DATABASE,
schema=DATA_SCHEMA,
pwd=DBPASSWORD,
port=DBPORT)
db.execute("create schema {}".format(LOGGING_SCHEMA))
db.execute("create schema {}".format(TEST_SCHEMA))
db.execute("create schema {}".format(DATA_SCHEMA))
dirs = {
'sample_data_dir': "./_sample_data/",
'sample_working_dir': "./_sample_working_dir/",
'sample_zip_data_dir': "./_sample_zip_data/"
}
# this should run first test functions run alphabetically
#@unittest.skip("only need once")
def test_00_init(self):
print('# In function:', sys._getframe().f_code.co_name)
self.db.execute(
'create schema if not exists {}'.format(self.DATA_SCHEMA)
) # db.execute('create database if not exists testing')
tbl = self.db.get_all_tables()
for table in tbl:
if table.startswith(self.DATA_SCHEMA + '.'):
static_func.add_column(self.db, self.DATA_SCHEMA + "." + table,
'crc', 'uuid')
static_func.add_column(self.db, self.DATA_SCHEMA + "." + table,
'file_id', 'Integer')
for x in self.dirs:
print(x)
os.makedirs(name=self.dirs[x], exist_ok=True)
# this should run last
def test_zz_last(self):
print('# In function:', sys._getframe().f_code.co_name)
# this should run last
def test_02_record_keeper(self):
print('# In function:', sys._getframe().f_code.co_name)
t = db_table.db_table_func.RecordKeeper(
self.db, db_table.db_table_def.MetaSourceFiles)
row = db_table.db_table_def.MetaSourceFiles(project_name=PROJECT_NAME,
file_path='.',
file_name='abc',
file_name_data='',
file_type='ZIP',
parent_file_id=0)
t.add_record(row, commit=True)
self.db.commit()
def test_03_query(self):
print('# In function:', sys._getframe().f_code.co_name)
x = self.db.query('select 1 from logging.meta_source_files limit 1;')
print(x)
self.assertTrue(x)
@unittest.skip("Not yet")
def test_05_upsert(self):
sql = """insert into table_b (pk_b, b)
select pk_a,a from table_a
on conflict ({}) do update set b=excluded.b;"""
self.assertTrue(
(static_func.generate_postgres_upsert(self.db, 'meta_source_files',
'stg', 'logging')))
# self.db.get_primary_keys('logging.meta_source_files')
#@static_func.timer
def test_06_migrate_utils(self):
if self.GENERATE_SAMPLE_DATA:
# static_func.generate_data_sample(self.db,'xref_clickwrap_agreement',self.schema,'_sample_data/xref_clickwrap_agreement.csv',line_count=5)
static_func.generate_data_sample_all_tables(
self.db,
self.DATA_SCHEMA,
self.dirs['sample_data_dir'],
line_count=self.SAMPLE_DATA_LINE_COUNT,
zip_file_name=os.path.join(self.dirs['sample_zip_data_dir'],
'sample_data.zip'),
num_tables=self.SAMPLE_DATA_TOTAL_TABLES,
post_fix='2018.csv',
include_header=self.GENERATE_SAMPLE_DATA_W_HEADER)
#@unittest.skip("Skipping for now")
def test_08_walkdir_data_file(self):
print('# In function:', sys._getframe().f_code.co_name)
# datafiles = dfm.DataFile([dfm.FilesOfInterest('account', r'^d.*.txt', '', None, self.schema, has_header=self.SAMPLE_DATA_HAS_HEADER)]
print("Truncating Logging Tables:")
self.db.execute(
"TRUNCATE table logging.meta_source_files, logging.table_file_regex, logging.error_log, logging.load_status RESTART IDENTITY;"
)
self.db.execute(
"""INSERT into logging.table_file_regex SELECT distinct concat(table_name,'.*.csv'),',',
table_schema,table_name,now(),TRUE
FROM information_schema.columns a
WHERE table_schema = '{}'""".format(self.DATA_SCHEMA))
# This is how we store the files we are looking for List of FileOfInterest
foi_list = [
data_files.FilesOfInterest('CSV',
file_regex=r".*\.csv",
file_path=self.dirs["sample_data_dir"],
parent_file_id=0)
]
foi_list.append(
data_files.FilesOfInterest(
'ZIP',
file_regex=r".*\.zip",
file_path=self.dirs["sample_zip_data_dir"],
parent_file_id=0))
df = data_files.DataFile(working_path=self.dirs["sample_working_dir"],
db=self.db,
foi_list=foi_list,
parent_file_id=0)
assert isinstance(df, data_files.DataFile)
result_set = self.db.query("select * from logging.meta_source_files")
self.assertGreater(len(result_set), 0,
"No files Found: Check Regex Logic")
# t = db_table.db_table_func.RecordKeeper(self.db, table_def=db_table.db_table_def.TableFilesRegex)
# records = t.get_all_records()
# # GENERATING FOI FROM DB META DATA IN LOGGING SCHEMA TABLE TableFilesRegex
# for r in records:
# assert isinstance(r, db_table.db_table_def.TableFilesRegex)
# foi_list.append(data_files.FilesOfInterest(
# file_type='CSV', table_name=str("tbl_1"), file_regex=str(r.regex),
# file_delimiter=str(r.delimiter), column_list=None, schema_name=str(r.db_schema),
# has_header=self.SAMPLE_DATA_HAS_HEADER, append_file_id=self.GENERATE_FILE_ID,
# append_crc=self.GENERATE_CRC,
# limit_rows=self.LIMIT_ROWS, start_row=self.START_ROW, insert_option=self.TRUNCATE_TABLE))
df.do_work(self.db, cleanup=False, limit_rows=None)
def clean_db(self):
self.db.execute(
"TRUNCATE table logging.meta_source_files, logging.table_file_regex, logging.error_log, logging.load_status RESTART IDENTITY;"
)
self.db.execute(
"""INSERT into logging.table_file_regex SELECT distinct concat(table_name,'.*.csv'),',',
table_schema,table_name,now(),TRUE
FROM information_schema.columns a
WHERE table_schema = '{}'""".format(self.DATA_SCHEMA))
def make_foi(self):
# This is how we store the files we are looking for List of FileOfInterest
foi_list = [
data_files.FilesOfInterest('CSV',
file_regex=r".*\.csv",
file_path=self.dirs["sample_data_dir"],
parent_file_id=0)
]
foi_list.append(
data_files.FilesOfInterest(
'ZIP',
file_regex=r".*\.zip",
file_path=self.dirs["sample_zip_data_dir"],
parent_file_id=0))
return foi_list
@unittest.skip("skipping")
def test_07_import_data(self):
print('# In function:', sys._getframe().f_code.co_name)
import itertools
logging.setLevel(log.WARN)
#SAMPLE_DATA_HAS_HEADER = [True, False]
CLEAN_PREV = [True, False]
GENERATE_SAMPLE_DATA = [True, False]
GENERATE_SAMPLE_DATA_W_HEADER = [True]
GENERATE_CRC = [False, True]
GENERATE_FILE_ID = [False, True]
LIMIT_ROWS = [None, 10]
START_ROW = [0]
TRUNCATE_TABLE = [True, False]
IMPORT_METHOD = [data_files.DataFile.IMPORT_VIA_PANDAS
] #,data_files.DataFile.IMPORT_VIA_CLIENT_CLI]
x = itertools.product(
#SAMPLE_DATA_HAS_HEADER,
GENERATE_CRC,
GENERATE_FILE_ID,
LIMIT_ROWS,
START_ROW,
TRUNCATE_TABLE,
IMPORT_METHOD)
for params in x:
#_SAMPLE_DATA_HAS_HEADER,\
_GENERATE_CRC,\
_GENERATE_FILE_ID,\
_LIMIT_ROWS,\
_START_ROW,\
_TRUNCATE_TABLE,\
_IMPORT_METHOD= params
print('# In function:', sys._getframe().f_code.co_name)
static_func.print_padded(20,
'_GENERATE_CRC', \
'_GENERATE_FILE_ID', \
'_LIMIT_ROWS', \
'_START_ROW', \
'_TRUNCATE_TABLE', \
'_IMPORT_METHOD')
static_func.print_padded(20,
_GENERATE_CRC,\
_GENERATE_FILE_ID,\
_LIMIT_ROWS,\
_START_ROW,\
_TRUNCATE_TABLE,\
_IMPORT_METHOD)
self.clean_db()
foi_list = self.make_foi()
df = data_files.DataFile(
working_path=self.dirs["sample_working_dir"],
db=self.db,
foi_list=foi_list,
parent_file_id=0)
assert isinstance(df, data_files.DataFile)
t = db_table.db_table_func.RecordKeeper(
self.db, table_def=db_table.db_table_def.TableFilesRegex)
records = t.get_all_records()
t.session.close()
for r in records:
assert isinstance(r, db_table.db_table_def.TableFilesRegex)
foi_list.append(
data_files.FilesOfInterest(
file_type='CSV',
table_name=str(r.table_name),
file_regex=str(r.regex),
file_delimiter=str(r.delimiter),
column_list=None,
schema_name=str(r.db_schema),
has_header=self.GENERATE_SAMPLE_DATA_W_HEADER,
append_file_id=_GENERATE_FILE_ID,
append_crc=_GENERATE_CRC,
limit_rows=_LIMIT_ROWS,
start_row=_START_ROW,
insert_option=_TRUNCATE_TABLE))
df.do_work(self.db,
cleanup=False,
limit_rows=None,
import_type=_IMPORT_METHOD)
def test_profile_csv(self):
print('# In function:', sys._getframe().f_code.co_name)
#static_func.profile_csv(full_file_path="/Users/hnguyen/PycharmProjects/py_dbmigration/_sample_data/test_city2018.csv")
static_func.profile_csv_directory(
"/Users/hnguyen/PycharmProjects/py_dbmigration/_sample_data")
def test_check_iii(self):
print('# In function:', sys._getframe().f_code.co_name)