def load_maps():
conn = None
try:
# region read connection parameters
params = config.config('db')
schema = """pcornet_maps"""
# endregion
# region connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# endregion
# region check if the schema exisit
cur.execute(
"""select exists(select 1 from information_schema.schemata where schema_name = \'""" + schema + """\');""")
schema_exist = cur.fetchone()[0]
if not schema_exist:
print '% schema does not exist..... \n Creating schema ....' % schema
cur.execute(query.create_schema(schema))
print '% schema created' % schema
conn.commit()
# set the search pat to the schema
cur.execute("""SET search_path TO """ + schema + """;""")
time.sleep(0.1)
# endregion
# region create tables
try:
print '\ncreating and populating the mapping table ...'
cur.execute(query.create_table(schema))
conn.commit()
# region import the file to the database
if os.path.isfile('data/concept_map.csv'):
f = io.open('data/concept_map.csv', 'r', encoding="utf8")
cur.copy_from(f, schema + ".pedsnet_pcornet_valueset_map", columns=(
"source_concept_class",
"target_concept",
"pcornet_name",
"source_concept_id",
"concept_description",
"value_as_concept_id"),
sep=",")
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
# region permissions
try:
print '\nSetting permissions'
cur.execute("""SET search_path TO """ + schema + """;""")
time.sleep(0.1)
cur.execute(query.permission(schema))
conn.commit()
except(Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
# region Alter owner of tables
try:
cur.execute("""SET search_path TO """ + schema + """;""")
time.sleep(0.1)
cur.execute(query.owner(schema))
conn.commit()
except(Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
print '\nPcornet valueset map loaded ... \nClosing database connection...'
cur.close()
except (Exception, psycopg2.OperationalError) as error:
print(error)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
except (Exception, psycopg2.ProgrammingError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def load_maps():
conn = None
try:
# region read connection parameters
params = config.config('db')
schema = """pcornet_maps"""
# endregion
# region connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# endregion
# region check if the schema exisit
cur.execute(
"""select exists(select 1 from information_schema.schemata where schema_name = \'"""
+ schema + """\');""")
schema_exist = cur.fetchone()[0]
if not schema_exist:
print '% schema does not exist..... \n Creating schema ....' % schema
cur.execute(query.create_schema(schema))
print '% schema created' % schema
conn.commit()
# set the search pat to the schema
cur.execute("""SET search_path TO """ + schema + """;""")
time.sleep(0.1)
# endregion
# region create tables
try:
print '\ncreating and populating the mapping table ...'
cur.execute(query.create_table(schema))
conn.commit()
# region import the file to the database
if os.path.isfile('data/concept_map.csv'):
f = io.open('data/concept_map.csv', 'r', encoding="utf8")
cur.copy_from(
f,
schema + ".pedsnet_pcornet_valueset_map",
columns=("source_concept_class", "target_concept",
"pcornet_name", "source_concept_id",
"concept_description", "value_as_concept_id"),
sep=",")
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
# region permissions
try:
print '\nSetting permissions'
cur.execute("""SET search_path TO """ + schema + """;""")
time.sleep(0.1)
cur.execute(query.permission(schema))
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
# region Alter owner of tables
try:
cur.execute("""SET search_path TO """ + schema + """;""")
time.sleep(0.1)
cur.execute(query.owner(schema))
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
print '\nPcornet valueset map loaded ... \nClosing database connection...'
cur.close()
except (Exception, psycopg2.OperationalError) as error:
print(error)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
except (Exception, psycopg2.ProgrammingError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def connect():
'''This function is used to connect to the database and query the database'''
conn = None
try:
# region read connection parameters
params = config.config('db')
schema_path = config.config('schema')
# schema = schema_path['schema']+"""_3dot1_pcornet"""
schema = [(re.sub('_pedsnet', '', schema_path['schema']) + """_3dot1_pcornet"""),
(re.sub('_pedsnet', '', schema_path['schema']) + """_3dot1_start2001_pcornet""")]
# endregion
# region connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# endregion
# region Check if there is already a configuration file
if os.path.isfile(configfile_name):
# delete the file
os.remove(configfile_name)
# endregion
for schemas in schema:
# region check if the schema exisit
cur.execute(
"""select exists(select 1 from information_schema.schemata where schema_name = \'""" + schemas + """\');""")
schema_exist = cur.fetchall()[0]
if "True" not in schema_exist:
print '% schema does not exist..... \n Creating schema ....' % schemas
cur.execute(query.create_schema(schemas))
print '% schema created' % schemas
conn.commit()
# set the search pat to the schema
cur.execute("""SET search_path TO """ + schemas + """;""")
time.sleep(0.1)
# endregion
# region create mapping table
try:
print '\ncreating and populating the mapping table ...'
cur.execute(query.create_table(schemas))
conn.commit()
# region import the file to the database
if os.path.isfile('data/concept_map.csv'):
f = open('data/concept_map.csv', 'r')
cur.copy_from(f, schemas + ".pedsnet_pcornet_valueset_map", columns=("target_concept",
"source_concept_class",
"source_concept_id",
"value_as_concept_id",
"concept_description"),
sep=",")
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
# region run the DDL
try:
print '\nRunning the DDL ...'
# set the search pat to the schema
cur.execute("""SET search_path TO """ + schemas + """;""")
time.sleep(0.1)
cur.execute(query.dll())
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
# region Alter tables and add site column
try:
print '\nAltering table, adding {site} column ...'
cur.execute("""SET search_path TO """ + schemas + """;""")
cur.execute(query.site_col(schemas))
conn.commit()
except(Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
# region permissions
try:
print '\nSetting permissions'
cur.execute("""SET search_path TO """ + schemas + """;""")
time.sleep(0.1)
cur.execute(query.permission(schemas))
conn.commit()
except(Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
# region Alter owner of tables
try:
cur.execute("""SET search_path TO """ + schemas + """;""")
time.sleep(0.1)
cur.execute(query.owner(schemas))
conn.commit()
except(Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
# region Populate Harvest
try:
print '\nPopulating harvest table... '
if os.path.isfile('data/harvest_data.csv'):
f = open('data/harvest_data.csv', 'r')
cur.copy_from(f, schemas + ".harvest", columns=("admit_date_mgmt",
"birth_date_mgmt",
"cdm_version",
"datamart_claims",
"datamart_ehr",
"datamart_name",
"datamart_platform",
"datamartid",
"discharge_date_mgmt",
"dispense_date_mgmt",
"enr_end_date_mgmt",
"enr_start_date_mgmt",
"lab_order_date_mgmt",
"measure_date_mgmt",
"network_name", "networkid",
"onset_date_mgmt",
"pro_date_mgmt",
"px_date_mgmt",
"refresh_condition_date",
"refresh_death_cause_date",
"refresh_death_date",
"refresh_demographic_date",
"refresh_diagnosis_date",
"refresh_dispensing_date",
"refresh_encounter_date",
"refresh_enrollment_date",
"refresh_lab_result_cm_date",
"refresh_pcornet_trial_date",
"refresh_prescribing_date",
"refresh_pro_cm_date",
"refresh_procedures_date",
"refresh_vital_date",
"report_date_mgmt",
"resolve_date_mgmt",
"result_date_mgmt",
"rx_end_date_mgmt",
"rx_order_date_mgmt",
"rx_start_date_mgmt",
"specimen_date_mgmt"),
sep=",")
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
print '\nPcornet data model set up complete ... \nClosing database connection...'
cur.close()
except (Exception, psycopg2.OperationalError) as error:
print(error)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
except (Exception, psycopg2.ProgrammingError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def connect():
'''This function is used to connect to the database and query the database'''
conn = None
try:
# read connection parameters
params = config.config('db')
schema_path = config.config('schema')
# schema = schema_path['schema']+"""_3dot1_pcornet"""
schema = [
schema_path['schema'] + """_3dot1_pcornet""",
schema_path['schema'] + """_start2001_pcornet"""
]
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# Check if there is already a configuration file
if os.path.isfile(configfile_name):
# delete the file
os.remove(configfile_name)
for schemas in schema:
# check if the schema exisit
cur.execute(
"""select exists(select 1 from information_schema.schemata where schema_name = \'"""
+ schemas + """\');""")
schema_exist = cur.fetchall()[0]
print(schema_exist)
if "True" not in schema_exist:
cur.execute(query.create_schema(schemas))
print '% schema created' % schema
# set the search pat to the schema
cur.execute("""SET search_path TO """ + schemas + """;""")
# cur.execute("""SHOW search_path;""")
# path = cur.fetchall()
# print(path)
time.sleep(0.1)
# create table
try:
cur.execute(query.create_table(schemas))
print(conn.notices)
conn.commit()
# import the file to the database
if os.path.isfile('concept_map/concept_map.csv'):
f = open('concept_map/concept_map.csv', 'r')
cur.copy_from(
f,
schemas + ".pedsnet_pcornet_valueset_map",
columns=("target_concept", "source_concept_class",
"source_concept_id", "value_as_concept_id",
"concept_description"),
sep=",")
print(conn.notices)
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.OperationalError) as error:
print(error)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
except (Exception, psycopg2.ProgrammingError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def ddl_only():
"""
This function creates the DDL for the Transformation. Following steps are processed:
1. Create the XX_3dot1_pcornet schema and XX_3dot1_start2001_pcornet
2. Create the DDL for PCORnet
3. Create and populate the valueset map require to map the PEDSnet to PCORNet values
3. Alter table to add a column for the site
4. Populate the harvest table
5. Set the permission for pcor_et_user and pcornet_sas user.
"""
conn = None
try:
# region read connection parameters
params = config.config('db')
schema_path = config.config('schema')
# schema = schema_path['schema']+"""_3dot1_pcornet"""
schema = [
(re.sub('_pedsnet', '', schema_path['schema']) + """_pcornet""")
]
# endregion
# region connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# endregion
for schemas in schema:
# region check if the schema exisit
cur.execute(
"""select exists(select 1 from information_schema.schemata where schema_name = \'"""
+ schemas + """\');""")
schema_exist = cur.fetchall()[0]
if "True" not in schema_exist:
print '% schema does not exist..... \n Creating schema ....' % schemas
cur.execute(query.create_schema(schemas))
print '% schema created' % schemas
conn.commit()
# set the search pat to the schema
cur.execute("""SET search_path TO """ + schemas + """;""")
time.sleep(0.1)
# endregion
# region create tables
try:
print '\ncreating and populating the mapping table ...'
cur.execute(query.create_table(schemas))
conn.commit()
# region import the file to the database
if os.path.isfile('data/concept_map.csv'):
f = open('data/concept_map.csv', 'r')
cur.copy_from(
f,
schemas + ".pedsnet_pcornet_valueset_map",
columns=("target_concept", "source_concept_class",
"source_concept_id", "value_as_concept_id",
"concept_description"),
sep=",")
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
try:
cur.execute("""SET search_path TO """ + schemas + """;""")
time.sleep(0.1)
cur.execute(
"""INSERT INTO """ + schemas +
""".version_history (operation, model, model_version, dms_version,
dmsa_version) VALUES ('create constraints', 'pcornet', '3.1.0', '1.0.4-beta', '0.6.0');"""
)
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# region permissions
try:
print '\nSetting permissions'
cur.execute("""SET search_path TO """ + schemas + """;""")
time.sleep(0.1)
cur.execute(query.permission(schemas))
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
# region Alter owner of tables
try:
cur.execute("""SET search_path TO """ + schemas + """;""")
time.sleep(0.1)
cur.execute(query.owner(schemas))
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
# region Populate Harvest
try:
print '\nPopulating harvest table... '
if os.path.isfile('data/harvest_data.csv'):
f = open('data/harvest_data.csv', 'r')
cur.copy_from(
f,
schemas + ".harvest",
columns=(
"admit_date_mgmt", "birth_date_mgmt",
"cdm_version", "datamart_claims", "datamart_ehr",
"datamart_name", "datamart_platform", "datamartid",
"discharge_date_mgmt", "dispense_date_mgmt",
"enr_end_date_mgmt", "enr_start_date_mgmt",
"lab_order_date_mgmt", "measure_date_mgmt",
"network_name", "networkid", "onset_date_mgmt",
"pro_date_mgmt", "px_date_mgmt",
"refresh_condition_date",
"refresh_death_cause_date", "refresh_death_date",
"refresh_demographic_date",
"refresh_diagnosis_date",
"refresh_dispensing_date",
"refresh_encounter_date",
"refresh_enrollment_date",
"refresh_lab_result_cm_date",
"refresh_pcornet_trial_date",
"refresh_prescribing_date", "refresh_pro_cm_date",
"refresh_procedures_date", "refresh_vital_date",
"report_date_mgmt", "resolve_date_mgmt",
"result_date_mgmt", "rx_end_date_mgmt",
"rx_order_date_mgmt", "rx_start_date_mgmt",
"specimen_date_mgmt"),
sep=",")
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# endregion
print '\nPcornet data model set up complete ... \nClosing database connection...'
cur.close()
except (Exception, psycopg2.OperationalError) as error:
print(error)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
except (Exception, psycopg2.ProgrammingError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def connect():
'''This function is used to connect to the database and query the database'''
conn = None
try:
# read connection parameters
params = config.config('db')
schema_path = config.config('schema')
# schema = schema_path['schema']+"""_3dot1_pcornet"""
schema = [schema_path['schema'] + """_3dot1_pcornet""", schema_path['schema'] + """_start2001_pcornet"""]
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# Check if there is already a configuration file
if os.path.isfile(configfile_name):
# delete the file
os.remove(configfile_name)
for schemas in schema:
# check if the schema exisit
cur.execute(
"""select exists(select 1 from information_schema.schemata where schema_name = \'""" + schemas + """\');""")
schema_exist = cur.fetchall()[0]
print(schema_exist)
if "True" not in schema_exist:
cur.execute(query.create_schema(schemas))
print '% schema created' % schema
# set the search pat to the schema
cur.execute("""SET search_path TO """ + schemas + """;""")
# cur.execute("""SHOW search_path;""")
# path = cur.fetchall()
# print(path)
time.sleep(0.1)
# create table
try:
cur.execute(query.create_table(schemas))
print(conn.notices)
conn.commit()
# import the file to the database
if os.path.isfile('concept_map/concept_map.csv'):
f = open('concept_map/concept_map.csv', 'r')
cur.copy_from(f, schemas + ".pedsnet_pcornet_valueset_map", columns=(
"target_concept", "source_concept_class", "source_concept_id", "value_as_concept_id",
"concept_description"),
sep=",")
print(conn.notices)
conn.commit()
except (Exception, psycopg2.OperationalError) as error:
print(error)
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.OperationalError) as error:
print(error)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
except (Exception, psycopg2.ProgrammingError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')