def insert_iCare_data(template_name, file_name):
'''(str, str, int, int) -> None
Insert template values into the database for the specific template
'''
connection = get_db_connection()
try:
# get the column names for this Template
column_names = get_template_attributes(template_name)
connection.autocommit = False
cursor = connection.cursor()
# get all the row values in the xlsx file
values = pyxl.parse_xlsx(file_name, column_names)
column_names_post = [
"`" + column_name + "`" for column_name in column_names
]
column_formatted = ",".join(column_names_post)
tmp = "%s," * len(column_names_post)
sql = ("INSERT INTO `{}` ({}) VALUES ".format(template_name,
column_formatted) + "(" +
("%s," * len(column_names_post))[:-1] + ")")
cursor.execute("START TRANSACTION")
for value in values:
cursor.execute(sql, value)
cursor.execute("COMMIT")
print("Data has been successfully added to the database")
finally:
connection.close()
def test_04_negative_range(self):
file_name = 'pyxl_test_03.xlsx'
column_names = ['A', 'B', 'C']
row_start = 10
row_end = 4
expected = []
result = pyxl.parse_xlsx(file_name, column_names, row_start, row_end)
self.assertEqual(expected, result, "negative range fails")
def test_03_multiple_rows(self):
file_name = 'pyxl_test_03.xlsx'
column_names = ['A', 'B', 'C']
row_start = 1
row_end = 3
expected = [['Bob', 'Joe', 'Alice'], ['Ada', 'Cobol', 'C']]
result = pyxl.parse_xlsx(file_name, column_names, row_start, row_end)
self.assertEqual(expected, result, "multiple rows fails")
def test_02_single_row(self):
file_name = 'pyxl_test_02.xlsx'
column_names = ['A', 'B', 'C']
row_start = 1
row_end = 2
expected = [['Bob', 'Joe', 'Alice']]
result = pyxl.parse_xlsx(file_name, column_names, row_start, row_end)
self.assertEqual(expected, result, "one row fails")
def test_01_no_columns(self):
file_name = ''
column_names = []
row_start = 1
row_end = 3
expected = []
result = pyxl.parse_xlsx(file_name, column_names, row_start, row_end)
self.assertEqual(expected, result, "empty column list fails")
def insert_data_for(template_name, file_name):
connection = get_db_connection()
try:
connection.autocommit = False
cursor = connection.cursor()
sql = ("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` " +
"WHERE `TABLE_SCHEMA`='{}' AND `TABLE_NAME`='{}'".format(
config.database, template_name))
cursor.execute(sql)
column_names = [column_name[0] for column_name in cursor]
values = pyxl.parse_xlsx(file_name, column_names)
column_names_post = [
"`" + column_name + "`" for column_name in column_names
]
column_formatted = ",".join(column_names_post)
tmp = "%s," * len(column_names_post)
sql = ("INSERT INTO `{}` ({}) VALUES ".format(template_name,
column_formatted) + "(" +
("%s," * len(column_names_post))[:-1] + ")")
cursor.execute("START TRANSACTION;")
for value in values:
print("Adding:", value)
cursor.execute(sql, value)
cursor.execute("COMMIT;")
print("Data has been successfully added to the database")
finally:
connection.close()