def test_add_columns(self):
"""
This test checks how the new columns are read into existing table.
:return:
"""
file = os.path.join(self.my_path, 'resources/test_columns.csv')
extra_data = data.Data(file)
extra_data.convert_to_array_sql()
print(extra_data.sql_data)
reader.add_columns(extra_data, self.cursor, 100, table_name='test')
reader.add_tuples_new(extra_data,
self.data_base,
100,
table_name='test')
self.cursor.execute("""SELECT * FROM test WHERE Money_100=1790""")
result = self.cursor.fetchall()
self.assertEqual(1, len(result))
self.cursor.execute("""SELECT * FROM test WHERE KENNZIFFER='01065'""")
result = self.cursor.fetchall()
self.assertEqual(2, len(result))
def insert_new_data(link_file, level):
link_to_mapping_file = './resources/KRS_ROR_AMR_clean_mapping.csv'
link_to_template_input = './resources/KRS15_template.csv'
data_base = pymysql.connect(
"bmfvis.c35zrhmszzzr.eu-central-1.rds.amazonaws.com",
"admin",
"NPmpMe!696rY",
"mydb",
autocommit=True)
# data_base = pymysql.connect("localhost", "user", "password", "mydb", autocommit=True)
connections = {"default": data_base}
cursor = data_base.cursor()
if level == "0":
data = Data(link_file)
add_columns(data, cursor)
add_tuples_new(data, data_base=data_base)
data_base.commit()
load_meta_data_single(link_file, 100)
elif level == "1":
data = readin258AMR(link_file, link_to_mapping_file,
link_to_template_input)
add_columns(data, cursor, data_code=200)
add_tuples_new(data, data_base=data_base, data_code=200)
data_base.commit()
load_meta_data_single(link_file, 200)
elif level == "2":
data = readin257AMR(link_file, link_to_mapping_file,
link_to_template_input)
add_columns(data, cursor, data_code=300)
add_tuples_new(data, data_base=data_base, data_code=300)
data_base.commit()
load_meta_data_single(link_file, 300)
elif level == "3":
data = readinBund(link_file, link_to_template_input)
add_columns(data, cursor, data_code=400)
add_tuples_new(data, data_base=data_base, data_code=400)
data_base.commit()
load_meta_data_single(link_file, 400)
retrieve_db_data(connections).insert_all_years_into_db()
#d define database connection
data_base = pymysql.connect("localhost", "user", "password", "mydb")
# data_base = pymysql.connect("bmf.cvh00sxb8ti6.eu-central-1.rds.amazonaws.com", "admin", "NPmpMe!696rY", "mydb")
print("connected to db")
# load in all the data to DB
mapping_to_db(link_to_mapping_file)
create_table_and_load_data(data_base, Kreise_data)
data_base.commit()
data_base.close()
print("1/5")
#
data_base = pymysql.connect("localhost", "user", "password", "mydb")
# data_base = pymysql.connect('bmf.cvh00sxb8ti6.eu-central-1.rds.amazonaws.com', 'admin', 'NPmpMe!696rY', "mydb")
cursor = data_base.cursor()
add_columns(AMR12_data, cursor, data_code=200) # load in AMR12 data
add_tuples_new(AMR12_data, data_base=data_base, data_code=200)
data_base.commit()
print("2/5")
add_columns(AMR15_data, cursor, data_code=300) # load in AMR15 data
add_tuples_new(AMR15_data, data_base=data_base, data_code=300)
data_base.commit()
print("3/5")
add_columns(Bund_data, cursor, data_code=400) # load in Bund data
add_tuples_new(Bund_data, data_base=data_base, data_code=400)
data_base.commit()
print("4/5")
create_table_and_load_data(data_base, reference_data,
def test_wrong_code(self):
"""This test that an error message is raised when a wrong data_code is put in"""
with self.assertRaises(ValueError):
reader.add_columns(self.data, self.cursor, 500)
def resetdb():
data_base = pymysql.connect("localhost", "user", "password", "mydb")
connections = {"default": data_base}
link_to_mapping_file = './resources/KRS_ROR_AMR_clean_mapping.csv'
link_to_template_input = './resources/KRS15_template.csv'
link_to_AMR12_data = './resources/including metadata/AMR12_testfile_updated.csv'
link_to_AMR15_data = './resources/including metadata/AMR15_testfile_updated.csv'
link_to_Bund_data = './resources/including metadata/Bund_testfile_updated.csv'
link_to_Kreise_data = './resources/including metadata/KRS15_testfile_updated_ersetzt_mit_primär_null.csv'
link_to_reference_data = './resources/Referenzgroessen_input_updated_ERSETZT.csv'
AMR12_data = readin258AMR(
link_to_AMR12_data, link_to_mapping_file,
link_to_template_input) # create AMR12 data object
AMR15_data = readin257AMR(
link_to_AMR15_data, link_to_mapping_file,
link_to_template_input) # create AMR15 data object
Bund_data = readinBund(link_to_Bund_data,
link_to_template_input) # create Bund data object
reference_data = Data(link_to_reference_data)
Kreise_data = Data(link_to_Kreise_data)
print("0/8... connected to database")
# load in all the data to DB
mapping_to_db(link_to_mapping_file) # load in Mapping file to DB
print("1/8... done loading in the Mapping data")
create_table_and_load_data(data_base, Kreise_data) # load in Kreise data
data_base.commit()
print("2/8... done loading in the Kreise data")
cursor = data_base.cursor()
add_columns(AMR12_data, cursor, data_code=200) # load in AMR12 data
add_tuples_new(AMR12_data, data_base=data_base, data_code=200)
data_base.commit()
print("3/8... done loading in the AMR12 data")
add_columns(AMR15_data, cursor, data_code=300) # load in AMR15 data
add_tuples_new(AMR15_data, data_base=data_base, data_code=300)
data_base.commit()
print("4/8... done loading in the AMR15 data")
add_columns(Bund_data, cursor, data_code=400) # load in Bund data
add_tuples_new(Bund_data, data_base=data_base, data_code=400)
data_base.commit()
print("5/8... done loading in the Bund data")
create_table_and_load_data(
data_base, reference_data,
table_name="reference") # load in reference data
data_base.commit()
data_base.close()
print("6/8... done loading in the reference table")
link_to_KRS_metadata = './resources/including metadata/KRS15_testfile_updated_ersetzt_mit_primär_null.csv'
KRS_datacode = 100
link_to_AMR12_metadata = './resources/including metadata/AMR12_testfile_updated.csv'
AMR12_datacode = 200
link_to_AMR15_metadata = './resources/including metadata/AMR15_testfile_updated.csv'
AMR15_datacode = 300
link_to_bund_metadata = './resources/including metadata/bund_testfile_updated.csv'
bund_datacode = 400
#
load_meta_data_to_db(link_to_KRS_metadata, KRS_datacode,
link_to_AMR12_metadata, AMR12_datacode,
link_to_AMR15_metadata, AMR15_datacode,
link_to_bund_metadata, bund_datacode)
print('7/8... done loading in the meta data')
retrieve_db_data(connections).insert_all_years_into_db()
print('8/8... done updating the years')
print('Cleaning up...')
print('Done!')