def copy_data(config, debug=False, insert_method='batch', multi_process=False):
# try:
#optional parameters for config
optional = {}
optional['debug'] = debug
optional["insert_method"] = insert_method
#print(pyodbc.drivers())
#quit()
# determine CopyConf instance depending user input (whether a CopyConf instance or path of xml/json config file)
if type(config)== str:
# user pass a path of config file
cc = copyconf.CopyConf(config)
if config.__class__.__name__ == 'CopyConf':
# user pass an instance of CopyConf
cc = config
#cc = copyconf.CopyConf(config)
#for c in cc.copy_list:
# for k,v in c.__dict__.items():
# print(k,v)
# validate config
cc.validate_config_object()
# validate config for existence/accessible
cc.validate()
if debug:
cc.debug()
if multi_process is True:
copies = []
copies = [c for c in cc.copy_list]
for copy in copies:
copy.optional = optional
with Pool(processes=4) as pool:
p = pool.map(execute_copy,copies)
else:
for copy in cc.copy_list:
copy.optional = optional
execute_copy(copy)
return 0
def process(data, session_dt_string=None):
# print(data)
# print(session_dt_string)
# drop target table (or comment the two lines below to append data into an existing table)
#conn_str = "DRIVER={ODBC Driver 13 for SQL Server};SERVER=EHL5CD8434KLM;PORT=1443;DATABASE=ARTG;UID=tester1;PWD=password;"
#copython.drop_table(conn_str,"dbo","artg_entry")
#----------------------------------------
# start writing a simple programmable copy
#----------------------------------------
# define a dict that holds paths from different group in json (think this as schema)
paths_dict = {}
colmap_dict = {}
# define if there is any data injection required
data_injection_lod = []
data_injection_lod.append({
"table_name": "artg_entry",
"column_name": "created_on",
"value": session_dt_string
})
data_injection_lod.append({
"table_name": "artg_entry",
"column_name": "data_source",
"value": "apps.tga.gov.au"
})
##### prepare artg_entry table #############
# create artg_entry paths
artg_entry = ['/Results/#/ApprovalArea'\
,'/Results/#/EntryType'\
,'/Results/#/LicenceClass'\
,'/Results/#/LicenceId'\
,'/Results/#/Name'\
,'/Results/#/ProductCategory'\
,'/Results/#/ProductInformation/DocumentLink'\
,'/Results/#/StartDate'\
,'/Results/#/Status'\
]
# add the path dict
paths_dict['artg_entry'] = artg_entry
#colmap for artg_entry
colmap_artg_entry_dict = {
'ApprovalArea': 'ApprovalArea',
'EntryType': 'EntryType',
'LicenceClass': 'LicenceClass',
'LicenceId': 'LicenceId',
'Name': 'Name',
'ProductCategory': 'ProductCategory',
'DocumentLink': 'DocumentLink',
'StartDate': 'StartDate',
'Status:': 'Status',
'created_on': 'created_on' #injected
,
'data_source': 'data_source'
}
# add colmap_artg_entry_dict
colmap_dict['artg_entry'] = colmap_artg_entry_dict
##### prepare manufacturers table #############
# create manufacturers paths
manufacturers = [
'/Results/#/LicenceId',
'/Results/#/Manufacturers/#/Address/AddressLine1',
'/Results/#/Sponsor/Address/AddressLine2',
'/Results/#/Manufacturers/#/Address/Country',
'/Results/#/Manufacturers/#/Address/Postcode',
'/Results/#/Manufacturers/#/Address/State',
'/Results/#/Manufacturers/#/Address/Suburb',
'/Results/#/Manufacturers/#/Name'
]
# add manufacturers paths
paths_dict['manufacturers'] = manufacturers
# create manufacturers colmap
colmap_manufacturers_dict = {
'LicenceId': 'LicenceId',
'AddressLine1': 'AddressLine1',
'AddressLine2': 'AddressLine2',
'Country': 'Country',
'Postcode': 'Postcode',
'State': 'State',
'Suburb': 'Suburb',
'Name': 'Name'
}
# add manufacturers colmap
colmap_dict['manufacturers'] = colmap_manufacturers_dict
##### prepare sponsor table #############
# create products paths
products = ['/Results/#/LicenceId'
,'/Results/#/Products/#/EffectiveDate'\
,'/Results/#/Products/#/GMDNCode'\
,'/Results/#/Products/#/GMDNTerm'\
,'/Results/#/Products/#/Name'\
,'/Results/#/Products/#/Type'\
]
# add products paths
paths_dict['products'] = products
# create products colmap
colmap_products_dict = {
'LicenceId': 'LicenceId',
'EffectiveDate': 'EffectiveDate',
'GMDNCode': 'GMDNCode',
'GMDNTerm': 'GMDNTerm',
'Name': 'Name',
'Type': 'Type'
}
##### prepare sponsor table #############
# create sponsor paths
sponsor = [
'/Results/#/LicenceId', '/Results/#/Sponsor/Address/AddressLine1',
'/Results/#/Sponsor/Address/AddressLine2',
'/Results/#/Sponsor/Address/Country',
'/Results/#/Sponsor/Address/Postcode',
'/Results/#/Sponsor/Address/State',
'/Results/#/Sponsor/Address/Suburb', '/Results/#/Sponsor/Name'
]
# add sponsor path
paths_dict['sponsor'] = sponsor
# create sponsor colmap
colmap_sponsor_dict = {
'LicenceId': 'LicenceId',
'AddressLine1': 'AddressLine1',
'AddressLine2': 'AddressLine2',
'Country': 'Country',
'Postcode': 'Postcode',
'State': 'State',
'Suburb': 'Suburb',
'Name': 'Name'
}
# add sponsor colmap
colmap_dict['sponsor'] = colmap_sponsor_dict
#source: http://apps.tga.gov.au/prod/ARTGSearch/ARTGWebService.svc/json/ARTGValueSearch/?entrytype=device&pagestart=1&pageend=1
#path = r"C:\Users\60145210\Documents\ARTG\test2.json"
#data = json.load(open(path))
# for k,v in data.items():
# print("{} type {}".format(k,type(v)))
#print('---------------')
tab_with_records = get_tables_with_record(
data, paths_dict, data_injection_lod=data_injection_lod)
# inject timestamp
##### test debug here #######
# for tab in tab_with_records:
# print(tab,tab_with_records[tab])
# for k,v in colmap_dict.items():
# print(k,':',v)
# for src,trg in v.items():
# print(src,trg)
#tab_with_records['artg_entry']['created_on'] = timestamp
# print(tab_with_records['artg_entry'])
# print('hello')
# print(timestamp)
######
###### start interfacing to copython
######
# create a CopyConf object with None arg (no config file passed in) and fill up its attributes
cc = copyconf.CopyConf()
cc.description = "copy a list of dictionary (LOD) into mssql" # description of this copy
# for each table create an instance of copypthon copy
for tab in tab_with_records:
#print(tab,tab_with_records[tab])
# create a Copy object and define its source/target type
c = copyconf.Copy("artg_entry_copy")
c.source_type = "lod"
c.target_type = "sql_table"
# create a source object (in this case a csv object) and fill up its attributes
src_obj = copyconf.LODConf()
src_obj.lod = tab_with_records[tab]
# assign this object to copy object as source
c.source = src_obj
# creat target object (in this case a sql table)
trg_obj = copyconf.SQLTableConf()
trg_obj.conn_str = "DRIVER={ODBC Driver 13 for SQL Server};SERVER=EHL5CD8434KLM;PORT=1443;DATABASE=ARTG;UID=tester1;PWD=password;"
trg_obj.schema_name = "dbo"
trg_obj.table_name = tab
# assign this object to copy object as target
c.target = trg_obj
# define column mapping.
# in this simple example we need to create colmap objects and add them to the copy
# of course there is better way to do this eg. get a list fo column mapping pair
# and create a for-loop process to add any colmap.
# colmap = copyconf.ColMapConf("EntryType","EntryType")
# c.colmap_list.append(colmap)
# colmap = copyconf.ColMapConf("LicenceClass","LicenceClass")
# c.colmap_list.append(colmap)
# colmap = copyconf.ColMapConf("LicenceId","LicenceId")
# c.colmap_list.append(colmap)
for k, v in colmap_dict.items():
if k == tab:
for src, trg in v.items():
colmap = copyconf.ColMapConf(src, trg)
c.colmap_list.append(colmap)
#add this c (Copy instance) into cc (a CopyConf above)
cc.add_copy(c)
#----------------
# end
#----------------
# call copython.copy_data and pass the cc as argument
PRINT_RESULT = False
res = copython.copy_data(cc, debug=False)
if PRINT_RESULT:
print("res={}".format(res))
def process(data,session_dt_string = None):
# print(data)
# print(session_dt_string)
# drop target table (or comment the two lines below to append data into an existing table)
#conn_str = "DRIVER={ODBC Driver 13 for SQL Server};SERVER=EHL5CD8434KLM;PORT=1443;DATABASE=ARTG;UID=tester1;PWD=password;"
#copython.drop_table(conn_str,"dbo","artg_entry")
#----------------------------------------
# start writing a simple programmable copy
#----------------------------------------
path = r"C:\Users\60145210\Documents\Projects\copython\test\artg_project\_cf_path_totable.json"
jc = jsontabs.JSONPath_Tables_Conf(path)
#define if there is any data injection required
data_injection_lod = []
data_injection_lod.append({"table_name":"artg_entry","column_name":"created_on","value":session_dt_string})
data_injection_lod.append({"table_name":"artg_entry","column_name":"data_source","value":"apps.tga.gov.au"})
#print(jc.jsondata)
#jc.set_intertables_column_copies()
#print(jc.intertables_column_copies)
#jc.set_referencing_columns()
# for tab in jc.tables:
# print(tab.name)
# print(' ',tab.referencing_columns)
# print(' ',tab.referencing_tables)
# quit()
# for tab in jc.tables:
# print(' tab', tab.name)
# print(' isprimary', tab.is_primary)
#
# quit(0)
#print('---start debugging...')
# traverse the entire key & value and any sub key & value
jc = jsontabs.gen_json_tables(jc,data,data_injection_lod = data_injection_lod)
# print('\n--- debugging CURRENT ROW .....')
# for tab in jc.tables:
# print(tab.name)
# print('-----------')
# print(' ',[str(x) for x in tab.current_row])
# print('-----------')
# # for i,row in enumerate(tab.rows):
# # print(' ',i,row)
# print('--- end debugging CURRENT ROW .....\n')
# print('--- debugging FINAL ROWS')
# for tab in jc.tables:
# print(tab.name)
# print('-----------')
# #print(' ',tab.rows)
# for k,v in tab.rows.items():
# print(' ',k,v.print_kv())
# print('-----------')
#
# print('--- end debugging FINAL ROWS....\n')
#
# quit()
# for tab in jc.tables:
# print(tab.name)
# print(' ',len(tab.rows))
# print(' counter:',tab.counter)
# for rowid,row in jc.tables[0].rows.items():
# print(rowid,row)
# for dc in row.datarow:
# print('--->',dc)
##### test debug here #######
# for tab in jc.tables:
# print(tab.name)
# for k,v in colmap_dict.items():
# print(k,':',v)
# for src,trg in v.items():
# print(src,trg)
#tab_with_records['artg_entry']['created_on'] = timestamp
# print(tab_with_records['artg_entry'])
# print('hello')
# print(timestamp)
######
###### start interfacing to copython
######
# create a CopyConf object with None arg (no config file passed in) and fill up its attributes
cc = copyconf.CopyConf()
cc.description = "copy a flytab into mssql" # description of this copy
# for each table create an instance of copypthon copy
for tab in jc.tables:
print(tab.name)
# create a Copy object and define its source/target type
c = copyconf.Copy("artg_entry_copy")
c.source_type = "flytab"
c.target_type = "sql_table"
# create a source object (in this case a csv object) and fill up its attributes
src_obj = copyconf.FlyTableConf()
src_obj.flytab = tab
# assign this object to copy object as source
c.source =src_obj
# creat target object (in this case a sql table)
trg_obj = copyconf.SQLTableConf()
trg_obj.conn_str = "DRIVER={ODBC Driver 13 for SQL Server};SERVER=EHL5CD8434KLM;PORT=1443;DATABASE=Test;UID=tester1;PWD=password;"
trg_obj.schema_name = "dbo"
trg_obj.table_name = tab.name
# assign this object to copy object as target
c.target = trg_obj
# define column mapping.
# in this simple example we need to create colmap objects and add them to the copy
# of course there is better way to do this eg. get a list fo column mapping pair
# and create a for-loop process to add any colmap.
# colmap = copyconf.ColMapConf("EntryType","EntryType")
# c.colmap_list.append(colmap)
# colmap = copyconf.ColMapConf("LicenceClass","LicenceClass")
# c.colmap_list.append(colmap)
# colmap = copyconf.ColMapConf("LicenceId","LicenceId")
# c.colmap_list.append(colmap)
for src,trg in tab.colmap.items():
colmap = copyconf.ColMapConf(src,trg)
c.colmap_list.append(colmap)
#add this c (Copy instance) into cc (a CopyConf above)
cc.add_copy(c)
#----------------
# end
#----------------
# call copython.copy_data and pass the cc as argument
PRINT_RESULT = False
res = copython.copy_data(cc,debug=False)
if PRINT_RESULT:
print("res={}".format(res))
"""
import copython
from copython import copyconf
if __name__ == "__main__":
# drop target table (or comment the two lines below to append data into an existing table)
conn_str = "DRIVER={ODBC Driver 13 for SQL Server};SERVER=EHL5CD8434KLM;PORT=1443;DATABASE=Test;UID=tester1;PWD=password;"
copython.drop_table(conn_str, "dbo", "routes")
#----------------------------------------
# start writing a simple programmable copy
#----------------------------------------
# create a CopyConf object with None arg (no config file passed in) and fill up its attributes
cc = copyconf.CopyConf()
cc.description = "copy csv file into mssql" # description of this copy
# create a Copy object and define its source/target type
c = copyconf.Copy("routes")
c.source_type = "csv"
c.target_type = "sql_table"
# create a source object (in this case a csv object) and fill up its attributes
src_obj = copyconf.CSVConf()
src_obj.path = r"C:\Users\60145210\Documents\Projects\copython\test\routes.txt"
src_obj.encoding = "utf-8-sig"
src_obj.has_header = "yes"
src_obj.delimiter = ","
src_obj.quotechar = '"' # or "\""
# assign this object to copy object as source