def addToUik(fileName, session, regionID): success = True dbsession = session stat = dict() stat['db'] = {'all': 0, 'edited': 0} stat['import'] = {'no_import': 0, 'updated': 0, 'created': 0} with transaction.manager: # getting all precisions from db precisions_db = dbsession.query(GeocodingPrecision).all() precisions = dict() for precision in precisions_db: precisions[precision.name] = precision.id precisions['district'] = precisions['region'] # getting uiks from the region with edited label uiks_with_versions = dict() uiks_with_versions_from_db = dbsession.query(Uik, UikVersions) \ .outerjoin(UikVersions, Uik.id == UikVersions.uik_id) \ .filter(Uik.region_id == regionID) \ .distinct(Uik.id) # creating dictionary for store uik and edited label uiks_with_versions = dict() for uik in uiks_with_versions_from_db: stat['db']['all'] += 1 if uik[1] is not None: stat['db']['edited'] += 1 uiks_with_versions[uik[0].number_official] = { 'edited': False if uik[1] is None else True, 'uik': uik[0] } con = lite.connect(fileName) con.row_factory = lite.Row cur = con.cursor() import os table_name = os.path.splitext(os.path.basename(fileName))[0] for row in cur.execute("SELECT * FROM '%(table_name)s'" % {'table_name': table_name}): is_db_existed = row['uik'] in uiks_with_versions is_uik_edited = (row['uik'] in uiks_with_versions) and ( uiks_with_versions[row['uik']]['edited']) print '\n\rREG: %(reg_id)s\tTIK: %(tik_id)s\tUIK: %(uik_id)s\tIn_DB: %(is_db_existed)s\tEdited: %(is_uik_edited)s' % { 'reg_id': regionID, 'tik_id': row['tik_id'], 'uik_id': row['uik'], 'is_db_existed': is_db_existed, 'is_uik_edited': is_uik_edited } # when uik has versions don't import it if is_uik_edited: stat['import']['no_import'] += 1 print "UIK with %(id)s has not been imported because it has EDITED state" % { 'id': uiks_with_versions[row['uik']]['uik'].id } continue uikRec = { 'region_id': regionID, 'tik_id': row['tik_id'], 'number_official': row['uik'], 'number_composite': '_'.join([str(row['tik_id']), str(row['uik'])]), 'address_voting': row['addr_v'], 'place_voting': row['place_v'], 'address_office': row['addr_o'], 'place_office': row['place_o'], 'comment': None, 'is_applied': False, 'geocoding_precision_id': precisions[row['g_status']], 'point': WKTSpatialElement("POINT(%s %s)" % (row['lon'], row['lat']), 4326) } if row['uik'] in uiks_with_versions: # when uik exist in db update it uik_id = uiks_with_versions[row['uik']]['uik'].id dbsession.query(Uik)\ .filter(Uik.id == uik_id)\ .update(uikRec, synchronize_session=False) stat['import']['updated'] += 1 print 'UIK with %(id)s was updated' % {'id': uik_id} else: # when uik doesn't exist in db create it uik = Uik() uik.number_official = uikRec['number_official'] uik.number_composite = uikRec['number_composite'] uik.address_voting = uikRec['address_voting'] uik.place_voting = uikRec['place_voting'] uik.address_office = uikRec['address_office'] uik.place_office = uikRec['place_office'] uik.comment = uikRec['comment'] uik.point = uikRec['point'] uik.is_applied = False uik.geocoding_precision_id = uikRec['geocoding_precision_id'] uik.tik_id = uikRec['tik_id'] uik.region_id = uikRec['region_id'] dbsession.add(uik) stat['import']['created'] += 1 print 'UIK was created' region = dbsession.query(Region).filter(Region.id == regionID) region.update({'imported': True}) region = region.first() print '\n\rThe region "%(id)s - %(name)s" is marked as imported' % { 'id': region.id, 'name': region.name } print '\n\r%s' % stat return success
def addToUik(fileName, session, regionID): success = True dbsession = session stat = dict() stat['db'] = { 'all': 0, 'edited': 0 } stat['import'] = { 'no_import': 0, 'updated': 0, 'created': 0 } with transaction.manager: # getting all precisions from db precisions_db = dbsession.query(GeocodingPrecision).all() precisions = dict() for precision in precisions_db: precisions[precision.name] = precision.id precisions['district'] = precisions['region'] # getting uiks from the region with edited label uiks_with_versions_from_db = dbsession.query(Uik, UikVersions) \ .outerjoin(UikVersions, Uik.id == UikVersions.uik_id) \ .filter(Uik.region_id == regionID) \ .distinct(Uik.id) # creating dictionary for store uik and edited label uiks_with_versions = dict() for uik in uiks_with_versions_from_db: stat['db']['all'] += 1 if uik[1] is not None: stat['db']['edited'] += 1 uiks_with_versions[uik[0].number_official] = { 'edited': False if uik[1] is None else True, 'uik': uik[0] } con = lite.connect(fileName) con.row_factory = lite.Row cur = con.cursor() sqlite_table_name = os.path.splitext(os.path.basename(fileName))[0] get_sqlite_table_columns = cur.execute('PRAGMA table_info("%(table_name)s");' % {'table_name': sqlite_table_name}) sqlite_table_columns = {} for column_info in get_sqlite_table_columns: sqlite_table_columns[column_info[1]] = True for row in cur.execute("SELECT * FROM '%(table_name)s'" % {'table_name': sqlite_table_name}): is_db_existed = row['uik'] in uiks_with_versions is_uik_edited = (row['uik'] in uiks_with_versions) and (uiks_with_versions[row['uik']]['edited']) print '\n\rREG: %(reg_id)s\tTIK: %(tik_id)s\tUIK: %(uik_id)s\tIn_DB: %(is_db_existed)s\tEdited: %(is_uik_edited)s' % { 'reg_id': regionID, 'tik_id': row['tik_id'], 'uik_id': row['uik'], 'is_db_existed': is_db_existed, 'is_uik_edited': is_uik_edited } # when uik has versions don't import it if is_uik_edited: stat['import']['no_import'] += 1 print "UIK with %(id)s has not been imported because it has EDITED state" % { 'id': uiks_with_versions[row['uik']]['uik'].id } continue uikRec = { 'region_id': regionID, 'tik_id': row['tik_id'], 'number_official': row['uik'], 'number_composite': '_'.join([str(row['tik_id']), str(row['uik'])]), 'address_voting': row['addr_v'], 'place_voting': row['place_v'] if 'place_v' in sqlite_table_columns else None, 'address_office': row['addr_o'] if 'addr_o' in sqlite_table_columns else None, 'place_office': row['place_o'] if 'place_o' in sqlite_table_columns else None, 'comment': None, 'is_applied': False, 'geocoding_precision_id': precisions[row['g_status']], 'point': WKTSpatialElement("POINT(%s %s)" % (row['lon'], row['lat']), 4326) } if row['uik'] in uiks_with_versions: # when uik exist in db update it uik_id = uiks_with_versions[row['uik']]['uik'].id dbsession.query(Uik)\ .filter(Uik.id == uik_id)\ .update(uikRec, synchronize_session=False) stat['import']['updated'] += 1 print 'UIK with %(id)s was updated' % { 'id': uik_id } else: # when uik doesn't exist in db create it uik = Uik() uik.number_official = uikRec['number_official'] uik.number_composite = uikRec['number_composite'] uik.address_voting = uikRec['address_voting'] uik.place_voting = uikRec['place_voting'] uik.address_office = uikRec['address_office'] uik.place_office = uikRec['place_office'] uik.comment = uikRec['comment'] uik.point = uikRec['point'] uik.is_applied = False uik.geocoding_precision_id = uikRec['geocoding_precision_id'] uik.tik_id = uikRec['tik_id'] uik.region_id = uikRec['region_id'] dbsession.add(uik) stat['import']['created'] += 1 print 'UIK was created' region = dbsession.query(Region).filter(Region.id == regionID) region.update({'imported': True}) region = region.first() print '\n\rThe region "%(id)s - %(name)s" is marked as imported' % { 'id': region.id, 'name': region.name } print '\n\r%s' % stat return success
def addToUik(fileName, session, regionID=None): """ Если указан regionID, то из файла импортируются УИКи данного региона. Внимание!!! Здесь не удаляются УИКи -- они должны быть удалены вместе с ТИКами. Если не указан regionID, то импортируется все УИКи. """ processAll = (regionID==None) success = True dbsession = session with transaction.manager: shp = Reader(fileName) count_shapes = len(shp.shapes()) records = shp.shapeRecords() shapes = shp.shapes() for i in range(count_shapes - 1): record = records[i] row = record.record if processAll or row[0] == regionID: prec = dbsession.query(GeocodingPrecision).filter_by(name=row[16]).one() uikRec = { 'region_id': row[0], 'tik_id': row[17], 'number_official': row[2], 'number_composite': '_'.join([str(row[17]), str(row[2])]), 'address_voting': row[3], 'place_voting': row[4], 'address_office': row[5], 'place_office': row[6], 'comment': row[8], 'geocoding_precision_id': prec.id, 'point' : "POINT(%s %s)" % (record.shape.points[0][0], record.shape.points[0][1]) } uik = Uik() uik.number_official = uikRec['number_official'] uik.number_composite = uikRec['number_composite'] uik.address_voting = uikRec['address_voting'] uik.place_voting = uikRec['place_voting'] uik.address_office = uikRec['address_office'] uik.place_office = uikRec['place_office'] uik.comment = uikRec['comment'] uik.point = WKTSpatialElement(uikRec['point'], 4326) uik.is_applied = False uik.geocoding_precision_id = uikRec['geocoding_precision_id'] uik.tik_id = uikRec['tik_id'] uik.region_id = uikRec['region_id'] dbsession.add(uik) return success