def getInfoStreet():
""" query data """
query = """
SELECT *
FROM street_tbl
TABLESAMPLE SYSTEM(1)
LIMIT 5
"""
query = """
SELECT *
FROM street_tbl
ORDER BY random()
LIMIT 5
"""
conn = None
try:
params = db.configDbConnection()
conn = db.psycopg2.connect(**params)
cur = conn.cursor(cursor_factory=db.RealDictCursor)
cur.execute(query)
#print("query: ", query)
#print("cursor size: ", cur.rowcount)
for row in db.iterRow(cur, 10):
rowValues = [row[desc] for desc in row]
print(rowValues)
dataStreet = streetDataClass()
print(dataStreet.set(row['code']))
cur.close()
except (Exception, db.psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def getInfoKladr(testing=False):
""" query data """
query = """
SELECT *
FROM kladr_tbl
TABLESAMPLE SYSTEM(1)
LIMIT 3
"""
conn = None
try:
params = db.configDbConnection()
conn = db.psycopg2.connect(**params)
cur = conn.cursor(cursor_factory=db.RealDictCursor)
cur.execute(query)
print("query: ", query)
print("cursor size: ", cur.rowcount)
for row in db.iterRow(cur, 10):
rowValues = [row[desc] for desc in row]
print(rowValues)
dataKladr = kladrDataClass()
print(dataKladr.set(row['code']))
if testing:
dataKladr = kladrDataClass()
title = 'new'
print(f'{title: <12}: {dataKladr}')
dataKladr.town = dataKladr.setValue('a', 3)
title = 'town=22'
print(f'{title: <12}: {dataKladr}')
title = 'getCode'
print(f'{title: <12}: {dataKladr.getCode()}')
dataKladr.set(row['code'])
title = 'set'
print(f'{title: <12}: {dataKladr}')
title = 'getCode'
print(f'{title: <12}: {dataKladr.getCode()}')
cur.close()
except (Exception, db.psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def getMostRecentEstimates():
# create command list
commands = [
"""
DROP TABLE IF EXISTS estimates_tbl;
""",
"""
CREATE TABLE estimates_tbl (
region varchar(100) NOT NULL,
sort_order integer NOT NULL,
recent_estimates integer NOT NULL,
region_code char(2) NOT NULL
);
""",
]
# execute command list
db.executeCommand(commands)
conn = None
try:
# read the connection parameters
params = db.configDbConnection()
# connect to the PostgreSQL server
conn = db.psycopg2.connect(**params)
cur = conn.cursor()
# store data from file to database
with open('most_recent_estimates.txt', 'r') as f:
cur.copy_from(f,
'estimates_tbl',
sep=',',
columns=('region', 'sort_order', 'recent_estimates',
'region_code'))
# close communication with the PostgreSQL database server
cur.close()
# commit the changes
conn.commit()
except (Exception, db.psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def getHousesByStreet(code):
""" query data """
query = """
SELECT * FROM doma_tbl
WHERE code LIKE %(code)s
LIMIT 50
"""
queryParameters = {'code': ''.join([code, '%'])}
conn = None
res = []
try:
params = db.configDbConnection()
conn = db.psycopg2.connect(**params)
cur = conn.cursor(cursor_factory=db.RealDictCursor)
cur.execute(query, queryParameters)
for row in db.iterRow(cur, 10):
res += row['name'].split(',')
cur.close()
except (Exception, db.psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return ','.join(res)
def mostRecentEstimates():
query = """
SELECT DISTINCT r.region, r.code
FROM rus_shot_region_tbl AS r
LEFT JOIN rus_shot_tbl AS s
ON substring(s.code, 1, 2) = r.code;
"""
queryParameters = {}
rg = [{
'rg': 'Москва',
'value': 12678079
}, {
'rg': 'Московская область',
'value': 7690863
}, {
'rg': 'Краснодарский край',
'value': 5675462
}, {
'rg': 'Санкт-Петербург',
'value': 5398064
}, {
'rg': 'Свердловская область',
'value': 4310681
}, {
'rg': 'Ростовская область',
'value': 4197821
}, {
'rg': 'Республика Башкортостан',
'value': 4038151
}, {
'rg': 'Республика Татарстан',
'value': 3902642
}, {
'rg': 'Тюменская область(с ХМАО и ЯНАО)',
'value': 3756536
}, {
'rg': 'Челябинская область',
'value': 3466369
}, {
'rg': 'Нижегородская область',
'value': 3202946
}, {
'rg': 'Самарская область',
'value': 3179532
}, {
'rg': 'Республика Дагестан',
'value': 3110858
}, {
'rg': 'Красноярский край',
'value': 2866255
}, {
'rg': 'Ставропольский край',
'value': 2803573
}, {
'rg': 'Новосибирская область',
'value': 2798170
}, {
'rg': 'Кемеровская область',
'value': 2657854
}, {
'rg': 'Пермский край',
'value': 2599260
}, {
'rg': 'Волгоградская область',
'value': 2491036
}, {
'rg': 'Саратовская область',
'value': 2421895
}, {
'rg': 'Иркутская область',
'value': 2391193
}, {
'rg': 'Воронежская область',
'value': 2324205
}, {
'rg': 'Алтайский край',
'value': 2317153
}, {
'rg': 'Оренбургская область',
'value': 1956835
}, {
'rg': 'Омская область',
'value': 1926665
}, {
'rg': 'Республика Крым',
'value': 1912622
}, {
'rg': 'Приморский край',
'value': 1895868
}, {
'rg': 'Ленинградская область',
'value': 1875872
}, {
'rg': 'Ханты-Мансийский Автономный округ - Югра',
'value': 1674676
}, {
'rg': 'Белгородская область',
'value': 1549151
}, {
'rg': 'Удмуртская Республика',
'value': 1500955
}, {
'rg': 'Чеченская Республика',
'value': 1478726
}, {
'rg': 'Тульская область',
'value': 1466127
}, {
'rg': 'Владимирская область',
'value': 1358416
}, {
'rg': 'Хабаровский край',
'value': 1315643
}, {
'rg': 'Пензенская область',
'value': 1305563
}, {
'rg': 'Кировская область',
'value': 1262402
}, {
'rg': 'Тверская область',
'value': 1260379
}, {
'rg': 'Ярославская область',
'value': 1253389
}, {
'rg': 'Ульяновская область',
'value': 1229824
}, {
'rg': 'Чувашская Республика',
'value': 1217818
}, {
'rg': 'Брянская область',
'value': 1192491
}, {
'rg': 'Вологодская область',
'value': 1160445
}, {
'rg': 'Липецкая область',
'value': 1139371
}, {
'rg': 'Архангельская область (с НАО)',
'value': 1136535
}, {
'rg': 'Рязанская область',
'value': 1108847
}, {
'rg': 'Курская область',
'value': 1104008
}, {
'rg': 'Томская область',
'value': 1079271
}, {
'rg': 'Забайкальский край',
'value': 1059700
}, {
'rg': 'Калининградская область',
'value': 1012512
}, {
'rg': 'Тамбовская область',
'value': 1006748
}, {
'rg': 'Астраханская область',
'value': 1005782
}, {
'rg': 'Калужская область',
'value': 1002575
}, {
'rg': 'Ивановская область',
'value': 997135
}, {
'rg': 'Республика Бурятия',
'value': 985937
}, {
'rg': 'Республика Саха (Якутия)',
'value': 971996
}, {
'rg': 'Смоленская область',
'value': 934889
}, {
'rg': 'Кабардино-Балкарская Республика',
'value': 868350
}, {
'rg': 'Курганская область',
'value': 827166
}, {
'rg': 'Республика Коми',
'value': 820473
}, {
'rg': 'Республика Мордовия',
'value': 790197
}, {
'rg': 'Амурская область',
'value': 790044
}, {
'rg': 'Мурманская область',
'value': 741404
}, {
'rg': 'Орловская область',
'value': 733498
}, {
'rg': 'Республика Северная Осетия — Алания',
'value': 696837
}, {
'rg': 'Республика Марий Эл',
'value': 679417
}, {
'rg': 'Костромская область',
'value': 633385
}, {
'rg': 'Псковская область',
'value': 626115
}, {
'rg': 'Республика Карелия',
'value': 614064
}, {
'rg': 'Новгородская область',
'value': 596508
}, {
'rg': 'Ямало-Ненецкий автономный округ',
'value': 544444
}, {
'rg': 'Республика Хакасия',
'value': 534262
}, {
'rg': 'Республика Ингушетия',
'value': 507061
}, {
'rg': 'Сахалинская область',
'value': 488257
}, {
'rg': 'Карачаево-Черкесская Республика',
'value': 465528
}, {
'rg': 'Республика Адыгея',
'value': 463088
}, {
'rg': 'Севастополь',
'value': 449138
}, {
'rg': 'Республика Тыва',
'value': 327383
}, {
'rg': 'Камчатский край',
'value': 313016
}, {
'rg': 'Республика Калмыкия',
'value': 271135
}, {
'rg': 'Республика Алтай',
'value': 220181
}, {
'rg': 'Еврейская автономная область',
'value': 158305
}, {
'rg': 'Магаданская область',
'value': 140149
}, {
'rg': 'Архангельская область без НАО',
'value': 92424
}, {
'rg': 'Тюменская область без ХМАО и ЯНАО',
'value': 53746
}, {
'rg': 'Чукотский автономный округ',
'value': 50288
}, {
'rg': 'Ненецкий автономный округ',
'value': 44111
}]
conn = None
res = []
try:
params = db.configDbConnection()
conn = db.psycopg2.connect(**params)
cur = conn.cursor(cursor_factory=db.RealDictCursor)
cur.execute(query, queryParameters)
#regions = [v['rg'] for v in rg]
for i, row in enumerate(db.iterRow(cur, 10)):
region = row['region'].split(' - ')[0].split(' -')[0].split(
' /')[0]
found = False
for v in rg:
if v['rg'].find(region) != -1:
res.append({
'region': region,
'sortOrder': 0,
'recentEstimates': v['value'],
'regionCode': row['code']
})
found = True
if not found:
print(f'not found {region}')
res.sort(key=lambda x: (-x['recentEstimates']))
with open('most_recent_estimates.txt', 'w') as f:
for i, v in enumerate(res):
v['sortOrder'] = i + 1
r = v['region']
s = v['sortOrder']
e = v['recentEstimates']
c = v['regionCode']
f.write(f'{r},{s},{e},{c}\n')
cur.close()
except (Exception, db.psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return res
def codeStreetDecomposition():
commands = ("""
DROP TABLE IF EXISTS street_code_tbl
""", """
CREATE TABLE street_code_tbl (
id serial,
code TEXT,
onlycode TEXT DEFAULT '',
region TEXT DEFAULT '',
district TEXT DEFAULT '',
town TEXT DEFAULT '',
locality TEXT DEFAULT '',
street TEXT DEFAULT '',
actuality TEXT DEFAULT '',
level TEXT DEFAULT ''
)
""")
db.executeCommand(commands)
""" query data """
query = """
SELECT code
FROM street_tbl
LIMIT 5
"""
query = """
SELECT code
FROM street_tbl
"""
#WHERE code LIKE '77%'
conn = None
try:
params = db.configDbConnection()
conn = db.psycopg2.connect(**params)
cur = conn.cursor(cursor_factory=db.RealDictCursor)
cur.execute(query)
for row in db.iterRow(cur, 10):
dataStreet = streetDataClass()
dataStreet.set(row['code'])
valuesToInsert = {
'code': row['code'],
'onlycode': dataStreet.getCodeWithoutActuality(),
'region': dataStreet.getRegion(),
'district': dataStreet.getDistrict(),
'town': dataStreet.getTown(),
'locality': dataStreet.getLocality(),
'street': dataStreet.getStreet(),
'actuality': dataStreet.actuality,
'level': dataStreet.level
}
queryInsert = """
INSERT INTO street_code_tbl
(code, onlycode, region, district, town, locality, street, actuality, level)
VALUES (%(code)s, %(onlycode)s, %(region)s, %(district)s, %(town)s,
%(locality)s, %(street)s, %(actuality)s, %(level)s)
"""
db.executeCommandWithParameters([(queryInsert, valuesToInsert)])
cur.close()
except (Exception, db.psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def createStreetList():
commands = [
"""
DROP TABLE IF EXISTS moscow_street_list_tbl
""", """
CREATE TABLE moscow_street_list_tbl (
id serial,
code TEXT,
street TEXT DEFAULT '',
index TEXT DEFAULT ''
)
""", """
DROP TABLE IF EXISTS house_list_tbl
""", """
CREATE TABLE house_list_tbl (
id serial,
code TEXT,
street TEXT DEFAULT '',
houses TEXT DEFAULT ''
)
"""
]
db.executeCommand(commands)
""" query data """
query = """
SELECT DISTINCT s.code,
k1.socr AS region_s, sb1.socrname AS region_sn, k1.name AS region_name,
k2.socr AS district_s, sb2.socrname AS district_sn, k2.name AS district_name,
k3.socr AS town_s, sb3.socrname AS town_sn, k3.name AS town_name,
k4.socr AS locality_s, sb4.socrname AS locality_sn, k4.name AS locality_name,
s1.socr AS street_s, sb5.socrname AS street_sn, s1.name AS street_name,
s1.index, d.name
FROM street_code_tbl AS s
LEFT JOIN street_tbl AS s1 ON s.code = s1.code
LEFT JOIN socrbase_tbl AS sb5 ON s1.socr = sb5.scname AND sb5.level = '5'
LEFT JOIN kladr_tbl AS k1 ON s.region = k1.code
LEFT JOIN socrbase_tbl AS sb1 ON k1.socr = sb1.scname AND sb1.level = '1'
LEFT JOIN kladr_tbl AS k2 ON s.district = k2.code
LEFT JOIN socrbase_tbl AS sb2 ON k2.socr = sb2.scname AND sb2.level = '2'
LEFT JOIN kladr_tbl AS k3 ON s.town = k3.code
LEFT JOIN socrbase_tbl AS sb3 ON k3.socr = sb3.scname AND sb3.level = '3'
LEFT JOIN kladr_tbl AS k4 ON s.locality = k4.code
LEFT JOIN socrbase_tbl AS sb4 ON k4.socr = sb4.scname AND sb4.level = '4'
LEFT JOIN doma_tbl AS d ON s.code = left(d.code, 17)
WHERE d.name != '' AND s.code LIKE '77%'
"""
#LIMIT 1000
conn = None
notFound = 0
try:
params = db.configDbConnection()
conn = db.psycopg2.connect(**params)
cur = conn.cursor(cursor_factory=db.RealDictCursor)
cur.execute(query)
"""
try:
f = open('streets.txt', 'w')
finally:
f.close()
"""
for row in db.iterRow(cur, 10):
houses = '' #getHousesByStreet(row['code'])
if False: #len(houses) == 0:
notFound += 1
else:
queries = []
street = ', '.join([
convertToStr(row['region_sn'], row['region_name']),
convertToStr(row['district_sn'], row['district_name']),
convertToStr(row['town_sn'], row['town_name']),
convertToStr(row['locality_sn'], row['locality_name']),
convertToStr(row['street_sn'], row['street_name'])
]).replace(',,', ',').replace(', ,', ',')
street = street.replace(', ,', ',')
valuesToInsert = {
'code': row['code'],
'street': street,
'houses': '', #houses,
'index': row['index']
}
queryInsert = """
INSERT INTO house_list_tbl
(code, street, houses)
VALUES (%(code)s, %(street)s, %(houses)s)
"""
#queries.append((queryInsert, valuesToInsert))
queryInsert = """
INSERT INTO moscow_street_list_tbl
(code, street, index)
VALUES (%(code)s, %(street)s, %(index)s)
"""
queries.append((queryInsert, valuesToInsert))
db.executeCommandWithParameters(queries)
#with open('streets.txt', 'a') as f:
# f.write(''.join([street, '\n']))
cur.close()
except (Exception, db.psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print(f'notFound: {notFound}')
def getStreets(request, attempt):
res = []
timeStart = datetime.datetime.now()
""" query data """
query = """
SELECT street_full AS street FROM msk_shot_tbl
WHERE street_shot LIKE %(value)s
LIMIT 10
"""
query = """
SELECT code, street_full AS street, street_shot <-> %(value)s AS dist
FROM msk_shot_tbl
ORDER BY dist
LIMIT 10
"""
query = """
SELECT code, street_full AS street, similarity(street_shot, %(value)s) AS sml
FROM msk_shot_tbl
WHERE street_shot %% %(value)s
ORDER BY sml DESC
LIMIT 10
"""
q = request.form['q']
value = queryNormalization(transliteration(q[:len(q) - attempt +
1])).strip()
res.append({'key': '_norm', 'value': ''.join(['[S] ', value])})
if request.form['v'] == 'r_metaphone':
if ' ' in value:
pos = value.find(' ')
query = """
WITH first_word AS
(
SELECT code, sort, street_full, street_metaphone
FROM rus_shot_tbl
WHERE street_metaphone
LIKE '%%'||regexp_replace(metaphone(%(value1)s),'\s','%%','g')||'%%'
)
SELECT code, sort, street_full AS street
FROM first_word
WHERE street_metaphone
LIKE '%%'||regexp_replace(metaphone(%(value2)s),'\s','%%','g')||'%%'
LIMIT 10
"""
queryParameters = {
'value1': value[:pos],
'value2': value[pos + 1:]
}
else:
query = """
SELECT code, sort, street_full AS street FROM rus_shot_tbl
WHERE street_metaphone LIKE
'%%'||regexp_replace(metaphone(%(value)s),'\s','%%','g')||'%%'
LIMIT 10
"""
queryParameters = {'value': value}
elif request.form['v'] == 'm_trgm':
if ']' in value:
value = value.split(']')[1].strip()
value = ''.join(['%', value, '%'])
queryParameters = {'value': value}
conn = None
_code = request.form['_id']
_street = request.form['_s']
_v_street = request.form['_vs']
_house = request.form['_h']
_v_house = request.form['_vh']
_full = request.form['_f']
if _house == 'y' and request.form['q'].find(_v_house) == -1:
_house = 'n'
_v_house = 'n'
_full = 'n'
elif _street == 'y' and request.form['q'].find(_v_street) == -1:
_code = 'n'
_street = 'n'
_v_street = 'n'
_house = 'n'
_v_house = 'n'
_full = 'n'
if _street == 'y':
value = request.form['q'].split(_v_street)[-1].strip()
start = 0
while start < len(value) and not value[start].isdigit():
start += 1
value = value[start:]
query = """
WITH houses AS (
SELECT
array_to_string(regexp_match(regexp_split_to_table(t.houses, ','), %(value)s||'[^ ]*', 'i'), '')
AS dom, r.code
FROM rus_shot_tbl AS r
LEFT JOIN td_tbl AS t ON t.code = r.code
WHERE r.code = %(code)s
)
SELECT DISTINCT dom, code
FROM houses
WHERE dom != ''
ORDER BY dom
LIMIT 10
"""
queryParameters = {'value': value.lower(), 'code': _code}
res.append({'key': '_id', 'value': _code})
res.append({'key': '_s', 'value': _street})
res.append({'key': '_vs', 'value': _v_street})
res.append({'key': '_h', 'value': _house})
res.append({'key': '_vh', 'value': _v_house})
res.append({'key': '_f', 'value': _full})
resFoundCount = 0
try:
params = db.configDbConnection()
conn = db.psycopg2.connect(**params)
cur = conn.cursor(cursor_factory=db.RealDictCursor)
if request.form['v'] == 'm_trgm':
cur.execute('SELECT set_limit(0)')
#logger.debug(cur.mogrify(query, queryParameters))
cur.execute(query, queryParameters)
for i, row in enumerate(db.iterRow(cur, 10)):
resFoundCount += 1
code = ''.join([str(i), row['code']])
if request.form['v'] == 'm_trgm':
if _street == 'y':
street = ', д '.join([_v_street, row['dom']])
else:
sml = row['sml']
v_street = row['street']
street = f'[{sml:.6f}] {v_street}'
elif request.form['v'] == 'r_metaphone':
if _street == 'y':
street = ', д '.join([_v_street, row['dom']])
else:
street = row['street']
else:
street = row['street']
res.append({'key': code, 'value': f'{street}'})
cur.close()
if resFoundCount == 0:
res.append({'key': '-1', 'value': f'Адрес не найден'})
except (Exception, db.psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
timeDelta = datetime.datetime.now() - timeStart
timeDeltaMicroseconds = int(timeDelta.microseconds // 1e3 +
timeDelta.seconds * 1e3)
res.append({'key': '_t', 'value': f'{timeDeltaMicroseconds}'})
return (res, resFoundCount)