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