def truncate_init_tables(): conn = psql_client.get_client() query = """TRUNCATE {tables} CASCADE""".format(tables=', '.join(INIT_TABLES)) try: yield momoko.Op(conn.execute, query) except Exception, ex: print ex
def delete_kinder_garden(): conn = psql_client.get_client() try: yield momoko.Op(conn.execute, u"""DELETE FROM schools WHERE (title like '%%дет. сад%%' and title not like '%%шк.%%' and title not like '%%школ%%') or (title like '%%дет. сад%%' and (title like '%%при%% шк.%%' or title like '%%при%% школ%%' or title like '%%Дошкольник%%'));""") except (psycopg2.Warning, psycopg2.Error) as error: raise Exception(str(error))
def insert_data(): conn = psql_client.get_client() try: # inserting countries names country_names = [] for lang in ['en', 'ru']: path = r'/opt/data/countries_{}.pkl'.format(lang) f = open(path, 'rb') country_names.append(cPickle.load(f)) f.close() for i in range(1, 236): for item in country_names[0]: if item[u'cid'] == i: title_en = item[u'title'] for item in country_names[1]: if item[u'cid'] == i: title_ru = item[u'title'] table = u"countries" columns = [u"title_en", u"title_ru"] data = {u"title_en": title_en, u"title_ru": title_ru} query = get_insert_query(table, data, columns) yield momoko.Op(conn.execute, query) # inserting all another data for i in range(1, 234): print "country No {}, time {} ".format(i, time.strftime("%c")) path = r'/opt/data/{}.pkl'.format(i) f = open(path, 'rb') cities = cPickle.load(f) f.close() try: path = r'/opt/data/main_cities/main_cities_{}.pkl'.format(i) f = open(path, 'rb') main_cities = cPickle.load(f) f.close() except IOError, ex: continue #inserting cities title, area and region for city in cities: keys = city.keys() if u'region' in keys: region = city[u'region'] if "'" in region: region = region.replace(r"'", "''") else: region = None if u'area' in keys: area = city[u'area'] if "'" in area: area = area.replace(r"'", "''") else: area = None city_title = city[u'title'] if "'" in city_title: city_title = city_title.replace(r"'", "''") table = u"cities" if not region and not area: columns = [u"country_id", u"title"] data = {u"country_id": i, u"title": city_title} query = get_insert_query(table, data, columns) city_id = yield momoko.Op(conn.execute, query) elif not region: columns = [u"country_id", u"area", u"title"] data = {u"country_id": i, u"area": area, u"title": city_title} query = get_insert_query(table, data, columns) city_id = yield momoko.Op(conn.execute, query) elif not area: columns = [u"country_id", u"region", u"title"] data = {u"country_id": i, u"region": region, u"title": city_title} query = get_insert_query(table, data, columns) city_id = yield momoko.Op(conn.execute, query) else: columns = [u"country_id", u"region", u"area", u"title"] data = {u"country_id": i, u"region": region, u"area": area, u"title": city_title} query = get_insert_query(table, data, columns) city_id = yield momoko.Op(conn.execute, query) city_id = city_id.fetchone()[0] #inserting main cities for item in main_cities: if city[u'cid'] == item[u'cid']: table = u"main_cities" columns = [u"country_id", u"city_id", u"title"] data = {u"country_id": i, u"city_id": city_id, u"title": city_title} query = get_insert_query(table, data, columns) yield momoko.Op(conn.execute, query) #inserting schools if u'schools' in keys: schools = city[u'schools'] for school in schools: school_title = school[u'title'] if "'" in school_title: school_title = school_title.replace(r"'", "''") table = u"schools" columns = [u"city_id", u"title"] data = {u"city_id": city_id, u"title": school_title} query = get_insert_query(table, data, columns) yield momoko.Op(conn.execute, query) # inserting universities if u'universities' in keys: universities = city[u'universities'] for university in universities: university_title = university[u'title'] if "'" in university_title: university_title = university_title.replace(r"'", "''") table = u"universities" columns = [u"city_id", u"title"] data = {u"city_id": city_id, u"title": university_title} query = get_insert_query(table, data, columns) university_id = yield momoko.Op(conn.execute, query) university_id = university_id.fetchone()[0] #inserting faculties uni_keys = university.keys() if u'faculties' in uni_keys: faculties = university[u'faculties'] for faculty in faculties: faculty_title = faculty[u'title'] if "'" in faculty_title: faculty_title = faculty_title.replace(r"'", "''") table = u"faculties" columns = [u"university_id", u"title"] data = {u"university_id": university_id, u"title": faculty_title} query = get_insert_query(table, data, columns) faculty_id = yield momoko.Op(conn.execute, query) faculty_id = faculty_id.fetchone()[0] #inserting chairs fac_keys = faculty.keys() if u'chairs' in fac_keys: chairs = faculty[u'chairs'] for chair in chairs: chair_title = chair[u'title'] if "'" in chair_title: chair_title = chair_title.replace(r"'", "''") table = u"chairs" columns = [u"faculty_id", u"title"] data = {u"faculty_id": faculty_id, u"title": chair_title} query = get_insert_query(table, data, columns) yield momoko.Op(conn.execute, query) except (psycopg2.Warning, psycopg2.Error) as error: raise Exception(str(error))