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)