def createDstTable(self): fields = self.convertHash(sql_helpers.selectHashIterator(self.sq_connection.execute("select * from {0}".format(self.src))).next()) for key in fields: fields[key] = "varchar" fields["ref_id"] = "integer" sql_helpers.makeTableIfNotExists(self.sq_connection, self.dst, fields, ["foreign key (ref_id) references {0}(id) on delete cascade".format(self.src), "unique(ref_id)"])
def __init__(self, sqlite_file, encoding = 'cp866'): self.encoding = encoding self.sq_connection = sqlite3.connect(sqlite_file, detect_types = sqlite3.PARSE_DECLTYPES) sql_helpers.makeTableIfNotExists(self.sq_connection, "processed_files", {"full_path": "varchar not null", "processed": "integer not null", "table_name" : "varchar not null"}, ["unique(full_path)"]) self.sq_connection.execute("pragma foreign_keys = on") # register wrappers for dbf compatible types def datetimeconvert(date): return datetime.datetime(*map(int, re.split("[-T:\.]", date))) def dateconvert(date): return datetime.date(*map(int, re.split("[-T:\.]", date))) sqlite3.register_adapter(datetime.datetime, datetime.datetime.isoformat) sqlite3.register_adapter(datetime.date, datetime.date.isoformat) sqlite3.register_converter('datetime', datetimeconvert) sqlite3.register_converter('date', dateconvert) sqlite3.register_adapter(decimal.Decimal, decimal.Decimal.__str__) sqlite3.register_converter('decimal', lambda a: decimal.Decimal(a)) sqlite3.register_converter('bool', lambda a: a == 1) sqlite3.register_adapter(bool, lambda a: a and 1 or 0) sqlite3.register_adapter(str, lambda a:a.decode())
def processTable(self, table_name): """processes table table_name if any file is assigned to it""" if self.sq_connection.execute("select * from processed_files where table_name = '{0}'".format(table_name)).fetchall() == [] : raise Exception("there is no files attached to table {0}".format(table_name)) files_list = map(lambda a: a[0], self.sq_connection.execute("select full_path from processed_files where table_name = '{0}' and processed = 0".format(table_name)).fetchall()) if files_list == []: log.log("no one file assigned to table {0}".format(table_name)) if not sql_helpers.isTableAre(self.sq_connection, table_name): #таблицы еще нет в базе - создаем, узнаем типы и имена полей из дбф файлов, проверяем соответствия полей типам for filename in files_list: log.log("reading structure of {0}".format(filename)) dbfcon = ydbf.open(filename, encoding = self.encoding) if not vars().has_key("fields"): # если еще не поеределили переменную fields = {} # тут храним имена и типы полей, которые будем создавать for field in dbfcon.fields: fields[field[0]] = [field[1], field[3]] else: # переменная уже определена, если встретятся поля с другим типом - выбросим исключение for field in dbfcon.fields: if fields.has_key(field[0]): if fields[field[0]] != [field[1], field[3]]: raise Exception("file {file} has field {field} with type {type1}, another fields in another files has type {type2}".format(file = filename, field = field[0], type1 = field[1], type2 = fields[field[0]])) else: fields[field[0]] = [field[1], field[3]] dbfcon.close() # теперь надо создать таблицу в базе def mapdatatype(a): # отображение типов из dbf в типы sqlite3 if a[0] == 'C': return "text" elif a[0] == 'D': return "date" elif a[0] == 'N' and a[1]: return "decimal" elif a[0] == 'N' and not a[1]: return "integer" elif a == 'L': return "bool" else: raise Exception("can not create field with type {0}".format(a)) for field in fields: fields[field] = mapdatatype(fields[field]) sql_helpers.makeTableIfNotExists(self.sq_connection, table_name, fields) sql_helpers.makeTableIfNotExists(self.sq_connection, "file_assigns", {"file_id" : "integer", "record_id": "integer"}, ["foreign key (file_id) references processed_files(id) on delete cascade", "unique(file_id, record_id)"]) #таблица существует - заносим в нее данные из файлов table_id = sql_helpers.getIdForTable(self.sq_connection, table_name) assign_id = sql_helpers.getIdForTable(self.sq_connection, "file_assigns") try: for file_tuple in self.sq_connection.execute("select id, full_path from processed_files where table_name = '{0}' and processed = 0".format(table_name)).fetchall(): log.log("inserting records from {0}".format(file_tuple[1])) for rec in ydbf.open(file_tuple[1], encoding = self.encoding): rec["id"] = table_id sql_helpers.insertInto(self.sq_connection, table_name, rec) sql_helpers.insertInto(self.sq_connection, "file_assigns", {"id" : assign_id, "file_id" : file_tuple[0], "record_id" : table_id}) table_id += 1 assign_id += 1 self.sq_connection.execute("update processed_files set processed = 1 where id = {0}".format(file_tuple[0])) except: self.sq_connection.rollback() raise else: self.sq_connection.commit()