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
