def _import_data_to_table(self, file, table_name, name_list, type_list):
result = 0
# read file
with open(file, newline='') as csvfile:
csvreader = csv.reader(csvfile, delimiter=',', quotechar='"')
firstLine = True
for row in csvreader:
# first row denotes table head
if firstLine:
firstLine = False
elif len(row) == len(name_list):
values = []
for i in range(len(row)):
v = SqlHelper.type_to_dbstatement(row[i], type_list[i])
values.append(v)
insert_query = SqlHelper.sql_insert_query(
table_name, values)
success = SqlHelper.execute_statement(
self.connection, insert_query)
result += int(success == True)
else:
print('ignoring unexpected row: {0}'.format(row))
return result
def _set_primarykey(self,
table_name,
primary_key,
col_list=[],
type_list=[]):
sql_stm = 'ALTER TABLE {0} ADD PRIMARY KEY ({1});'.format(
table_name, primary_key)
return SqlHelper.execute_statement(self.connection, sql_stm)
def _create_table(self, file, indices):
table_name = SqlHelper.get_tablename(file)
result_create = 0
result_data = 0
result_key = 0
# create table in database
name_list, type_list = SqlImporter._extract_name_and_types(file)
if len(name_list) > 0:
create_query = SqlHelper.sql_create_table(table_name, name_list,
type_list)
create_success = SqlHelper.execute_statement(
self.connection, create_query)
self.connection.commit()
if create_success:
result_create = 1
# import data into table
result_data = self._import_data_to_table(file, table_name,
name_list, type_list)
self.connection.commit()
# attempt to set primary key in first integer-type column
primary_key = -1
for i in range(len(type_list)):
t = type_list[i]
if t == DbType.INTEGER:
success = self._set_primarykey(table_name, i, name_list,
type_list)
self.connection.commit()
if success:
result_key = 1
primary_key = i
break
# set indices on _id named columns if specified
if indices:
for i in range(len(type_list)):
t = type_list[i]
if i != primary_key and t == DbType.INTEGER and (
name_list[i] == 'id' or '_id_' in name_list[i]
or name_list[i].endswith('_id')):
self._set_index(table_name, i, name_list, type_list)
return result_create, result_data, result_key
def _set_primarykey(self,
table_name,
primary_key,
col_list=[],
type_list=[]):
# ALTER TABLE table_name ADD PRIMARY KEY (primary_key)
# doesn't work for sqlite3
# see: https://www.sqlitetutorial.net/sqlite-primary-key/
sql_stm = 'PRAGMA foreign_keys=off;'
SqlHelper.execute_statement(self.connection, sql_stm)
sql_stm = 'BEGIN TRANSACTION;'
SqlHelper.execute_statement(self.connection, sql_stm)
sql_stm = 'ALTER TABLE {0} RENAME TO temp_table;'.format(table_name)
SqlHelper.execute_statement(self.connection, sql_stm)
sql_stm = SqlHelper.sql_create_table(table_name, col_list, type_list,
primary_key)
SqlHelper.execute_statement(self.connection, sql_stm)
sql_stm = 'INSERT INTO {0} SELECT * FROM temp_table;'.format(
table_name)
success = SqlHelper.execute_statement(self.connection, sql_stm)
sql_stm = 'DROP TABLE temp_table;'
SqlHelper.execute_statement(self.connection, sql_stm)
if success:
sql_stm = 'COMMIT;'
SqlHelper.execute_statement(self.connection, sql_stm)
else:
sql_stm = 'ROLLBACK;'
SqlHelper.execute_statement(self.connection, sql_stm)
sql_stm = 'PRAGMA foreign_keys=on;'
SqlHelper.execute_statement(self.connection, sql_stm)
return success
def _set_index(self, table_name, index, col_list=[], type_list=[]):
sql_stm = 'CREATE INDEX {0}_{1}_idx ON {0}({1});'.format(
table_name, col_list[index])
return SqlHelper.execute_statement(self.connection, sql_stm)
