def get_state_localities(state_pid):
with get_db_cursor() as cursor:
s = sql.SQL('''SELECT locality_pid
FROM {dbschema}.locality WHERE state_pid = {pid}
ORDER BY locality_pid;''').format(pid=sql.Literal(state_pid),
dbschema=dbschema)
cursor.execute(s)
with open('localities_state_{}.txt'.format(state_pid), 'w') as f:
for row in cursor.fetchall():
r = reg(cursor, row)
f.write(str(r.locality_pid) + '\n')
def get_state_addresses(state_pid):
with get_db_cursor() as cursor:
s = sql.SQL('''SELECT address_detail_pid
FROM {dbschema}.address_detail
WHERE locality_pid
IN (SELECT locality_pid FROM gnaf.locality WHERE state_pid = {pid})
ORDER BY address_detail_pid;''').format(
pid=sql.Literal(state_pid), dbschema=dbschema)
cursor.execute(s)
with open('addresses_state_{}.txt'.format(state_pid), 'w') as f:
for row in cursor.fetchall():
r = reg(cursor, row)
f.write(str(r.address_detail_pid) + '\n')
#!/usr/bin/env python3
import cgi
import db
form = cgi.FieldStorage()
phone = form.getvalue("phone")
email = form.getvalue("email")
password = form.getvalue("password")
db.reg(phone, email, password)
print("Content-type: text/html")
print("Location: http://localhost:8000/enter.html")
import db
text = "!"
while text != "":
command = input(
"Введите что будете проверять(reg - для регистрации, enter - для входа): "
)
if command == "reg":
phone = input("Введите телефон: ")
email = input("Введите почту: ")
password = input("Введите пароль: ")
print(db.reg(phone, email, password))
db.print_db()
elif command == "enter":
login = input("Введите логин: ")
password = input("Введите пароль: ")
print(db.is_reg(login, password))
elif command == "print":
db.print_db()
else:
break
def __init__(self, identifier, db_cursor=None):
self.id = identifier
self.uri = config.URI_LOCALITY_INSTANCE_BASE + identifier
# DB connection
if db_cursor is not None:
self.cursor = db_cursor
self._cursor_context_manager = None
else:
self._cursor_context_manager = get_db_cursor()
self.cursor = self._cursor_context_manager.__enter__()
self.locality_name = None
self.latitude = None
self.longitude = None
self.date_created = None
self.date_retired = None
self.locality_class_uri = None
self.locality_class_label = None
self.geocode_type = None
self.geometry_wkt = None
self.state_pid = None
self.alias_localities = dict()
self.locality_neighbours = dict()
# get data from DB
s = sql.SQL('''SELECT
l.locality_name,
l.date_created,
l.date_retired,
l.primary_postcode,
lp.latitude,
lp.longitude,
s.uri AS state_uri,
s.prefLabel AS state_label,
lc.uri AS locality_class_uri,
lc.prefLabel AS locality_class_label
FROM {dbschema}.locality l
LEFT JOIN {dbschema}.locality_point lp on l.locality_pid = lp.locality_pid
LEFT JOIN codes.state s ON CAST(l.state_pid AS text) = s.code
LEFT JOIN codes.locality lc on l.locality_class_code = lc.code
WHERE l.locality_pid = {id} ;''') \
.format(dbschema=sql.Identifier(config.DB_SCHEMA), id=sql.Literal(self.id))
self.cursor.execute(s)
rows = self.cursor.fetchall()
for row in rows:
r = reg(self.cursor, row)
self.locality_name = r.locality_name.title()
self.latitude = r.latitude
self.longitude = r.longitude
self.date_created = r.date_created
self.date_retired = r.date_retired
self.state_uri = r.state_uri
self.state_label = r.state_label
if self.latitude is not None and self.longitude is not None:
self.geometry_wkt = self.make_wkt_literal(
longitude=self.longitude, latitude=self.latitude)
try:
self.locality_class_label = r.locality_class_label
except (AttributeError, KeyError):
pass
try:
self.locality_class_uri = r.locality_class_uri
except (AttributeError, KeyError):
pass
break
else:
raise NotFoundError()
s2 = sql.SQL('''SELECT locality_alias_pid, name, uri, prefLabel
FROM {dbschema}.locality_alias
LEFT JOIN codes.alias a ON {dbschema}.locality_alias.alias_type_code = a.code
WHERE locality_pid = {id}''') \
.format(dbschema=sql.Identifier(config.DB_SCHEMA), id=sql.Literal(self.id))
# get just IDs, ordered, from the address_detail table, paginated by class init args
self.cursor.execute(s2)
rows = self.cursor.fetchall()
for row in rows:
r = reg(self.cursor, row)
alias = dict()
alias['locality_name'] = r.name.title()
alias['subclass_uri'] = r.uri
alias['subclass_label'] = r.preflabel
self.alias_localities[r.locality_alias_pid] = alias
# get a list of localityNeighbourIds from the locality_alias table
s3 = sql.SQL('''SELECT
a.neighbour_locality_pid,
b.locality_name
FROM {dbschema}.locality_neighbour a
INNER JOIN {dbschema}.locality_view b ON a.neighbour_locality_pid = b.locality_pid
WHERE a.locality_pid = {id}''') \
.format(dbschema=sql.Identifier(config.DB_SCHEMA), id=sql.Literal(self.id))
# get just IDs, ordered, from the address_detail table, paginated by class init args
self.cursor.execute(s3)
rows = self.cursor.fetchall()
for row in rows:
r = reg(self.cursor, row)
self.locality_neighbours[
r.neighbour_locality_pid] = r.locality_name.title()
def export_html(self, view='gnaf'):
if view == 'gnaf':
# initialise parameters in case no results are returned from SQL
latitude = None
longitude = None
geocode_type = None
locality_name = None
geometry_wkt = None
street_string = None
# make a human-readable street
s = sql.SQL('''SELECT
a.street_name,
a.street_type_code,
a.street_suffix_code,
a.latitude,
a.longitude,
a.geocode_type,
a.locality_pid,
b.locality_name,
st.prefLabel AS street_type_label,
st.uri AS street_type_uri,
ss.prefLabel AS street_suffix_label,
ss.uri AS street_suffix_uri
FROM {dbschema}.street_view a
INNER JOIN {dbschema}.locality_view b ON a.locality_pid = b.locality_pid
LEFT JOIN codes.street st ON a.street_type_code = st.code
LEFT JOIN codes.streetsuffix ss ON a.street_suffix_code = ss.code
WHERE street_locality_pid = {id}''') \
.format(id=sql.Literal(self.id), dbschema=sql.Identifier(config.DB_SCHEMA))
cursor = self.cursor
# get just IDs, ordered, from the address_detail table, paginated by class init args
cursor.execute(s)
rows = cursor.fetchall()
for row in rows:
r = reg(cursor, row)
self.street_name = r.street_name.title()
self.street_type = r.street_type_code.title()
self.street_suffix = r.street_suffix_code.title() if r.street_suffix_code is not None else None
latitude = r.latitude
longitude = r.longitude
geocode_type = r.geocode_type.title() if r.geocode_type is not None else None
self.locality_pid = r.locality_pid
locality_name = r.locality_name.title()
self.street_type_label = r.street_type_label
self.street_type_uri = r.street_type_uri
self.street_suffix_label = r.street_suffix_label
self.street_suffix_uri = r.street_suffix_uri
geometry_wkt = self.make_wkt_literal(
longitude=longitude, latitude=latitude
) if latitude is not None else None
street_string = '{}{}{}'.format(
self.street_name,
' ' + self.street_type.title() if self.street_type is not None else '',
' ' + self.street_suffix.title() if self.street_suffix is not None else ''
)
break
else:
raise NotFoundError()
# aliases
s2 = sql.SQL('''SELECT
street_locality_alias_pid,
street_name,
street_type_code,
street_suffix_code,
alias_type_code
FROM {dbschema}.street_locality_alias
WHERE street_locality_pid = {id}''') \
.format(id=sql.Literal(self.id), dbschema=sql.Identifier(config.DB_SCHEMA))
cursor.execute(s2)
rows = cursor.fetchall()
street_string = "Not Found"
for row in rows:
r = reg(cursor, row)
street_string = '{}{}{}'.format(
r.street_name,
' ' + r.street_type_code.title() if r.street_type_code is not None else '',
' ' + r.street_suffix_code.title() if r.street_suffix_code is not None else ''
)
self.street_locality_aliases[r.street_locality_alias_pid] = street_string
break
view_html = render_template(
'class_streetLocality_gnaf.html',
street_string=street_string,
street_locality_pid=self.id,
street_name=self.street_name,
street_type=self.street_type,
street_type_label=self.street_type_label,
street_type_uri=self.street_type_uri,
street_suffix=self.street_suffix,
street_suffix_label=self.street_suffix_label,
street_suffix_uri=self.street_suffix_uri,
locality_name=locality_name,
latitude=latitude,
longitude=longitude,
geocode_uri='http://linked.data.gov.au/def/gnaf/code/GeocodeTypes#StreetLocality',
geocode_label=geocode_type,
geometry_wkt=geometry_wkt,
locality_pid=self.locality_pid,
street_locality_aliases=self.street_locality_aliases
)
elif view == 'ISO19160':
view_html = render_template(
'class_streetLocality_ISO19160.html',
)
elif view == 'dct':
s = sql.SQL('''SELECT
street_name,
street_type_code,
street_suffix_code,
latitude,
longitude,
geocode_type,
locality_pid
FROM {dbschema}.street_view
WHERE street_locality_pid = {id}''') \
.format(id=sql.Literal(self.id), dbschema=sql.Identifier(config.DB_SCHEMA))
cursor = self.cursor
# get just IDs, ordered, from the address_detail table, paginated by class init args
cursor.execute(s)
for record in cursor:
self.street_name = record[0]
self.street_type = record[1]
self.street_suffix = record[2]
latitude = record[3]
longitude = record[4]
geocode_type = record[5].title()
self.locality_pid = record[6]
geometry_wkt = self.make_wkt_literal(longitude=longitude, latitude=latitude)
street_string = '{} {} {}'\
.format(self.street_name, self.street_type, self.street_suffix)
break
else:
raise NotFoundError()
view_html = render_template(
'class_streetLocality_dct.html',
identifier=self.id,
title='Street ' + self.id,
description=street_string,
coverage=geometry_wkt,
source='G-NAF, 2016',
type='Street'
)
else:
return NotImplementedError("HTML representation of View '{}' for StreetLocality is not implemented.".format(view))
return view_html