def get_all(cls, db_conn):
with get_cursor(db_conn) as cursor:
for row in cursor.execute(
f'select name, id from {cls.__name__};').fetchall():
region = cls(name=row[0], db_conn=db_conn)
region._id = row[1]
yield region
def view_stat(environ, start_response):
if environ['REQUEST_METHOD'].lower() == 'get':
with connection(DB_NAME) as conn:
with open('html/region_comment.html', 'rt') as stat_rows_template:
stat_rows_template_str = stat_rows_template.read()
with get_cursor(conn) as cur:
regions_ids = cur.execute('''select region_id from City
where id in (select city_id from Comment
group by city_id having count(*)>5);'''
).fetchall()
regions_ids = set(region_id[0] for region_id in regions_ids)
comments_count = {
region_id: len(tuple(Comment.get_by_region_id(region_id,
conn)))
for region_id in regions_ids
}
stat_rows = '\n'.join(
stat_rows_template_str.replace('{region_id}', str(region_id)).
replace('{name}',
Region.get_by_id(region_id, conn).name).replace(
'{comments_count}', str(comments_count[region_id]))
for region_id in regions_ids)
with open('html/stat.html', 'rt') as response:
start_response('200 OK,',
[('Content-Type', 'text/html; charset=UTF-8')])
return response.read().replace('{rows}', stat_rows)
def save(self):
with get_cursor(self._db_conn) as cursor:
cursor.execute(
f'insert into {self._table_name} (name, region_id) values (?, ?);',
(self.name, self.region.id))
self._id = cursor.execute(
'select last_insert_rowid();').fetchone()[0]
return self
def get_by_id(cls, id, db_conn):
with get_cursor(db_conn) as cursor:
row = cursor.execute(
f'select name, id from {cls.__name__} where id=?;',
(id, )).fetchone()
region = cls(name=row[0], db_conn=db_conn)
region._id = row[1]
return region
def get_all(cls, db_conn):
with get_cursor(db_conn) as cursor:
for row in cursor.execute(
f'select name, region_id, id from {cls.__name__};'
).fetchall():
region = Region.get_by_id(id=row[1], db_conn=db_conn)
city = cls(name=row[0], region=region, db_conn=db_conn)
city._id = row[2]
yield city
def get_by_id(cls, id, db_conn):
with get_cursor(db_conn) as cursor:
row = cursor.execute(
f'select name, region_id, id from {cls.__name__} where id=?;',
(id, )).fetchone()
region = Region.get_by_id(row[1], db_conn)
city = cls(name=row[0], region=region, db_conn=db_conn)
city._id = row[2]
return city
def save(self):
with get_cursor(self._db_conn) as cursor:
cursor.execute(
f'''insert into {self._table_name} (name, surname, patronymic, city_id, phone, email, comment)
values (?, ?, ?, ?, ?, ?, ?);''',
(self.name, self.surname, self.patronymic,
self.city.id if self.city else None, self.phone, self.email,
self.comment))
self._id = cursor.execute(
'select last_insert_rowid();').fetchone()[0]
return self
def get_by_region_id(cls, region_id, db_conn):
with get_cursor(db_conn) as cursor:
for row in cursor.execute(
'''select Comment.name, Comment.surname, Comment.comment, Comment.patronymic,
City.id, Comment.phone, Comment.email, Comment.id from Comment
inner join City on Comment.city_id=City.id and City.region_id=?;''',
(region_id, )).fetchall():
city = City.get_by_id(row[4], db_conn)
comment = cls(name=row[0],
surname=row[1],
comment=row[2],
db_conn=db_conn,
patronymic=row[3],
city=city,
phone=row[5],
email=row[6])
comment._id = row[7]
yield comment
def get_all(cls, db_conn):
with get_cursor(db_conn) as cursor:
for row in cursor.execute(
f'select name, surname, comment, patronymic, city_id, phone, email, id '
f'from {cls.__name__};').fetchall():
if row[4] is not None:
city = City.get_by_id(row[4], db_conn)
else:
city = None
comment = cls(name=row[0],
surname=row[1],
comment=row[2],
db_conn=db_conn,
patronymic=row[3],
city=city,
phone=row[5],
email=row[6])
comment._id = row[7]
yield comment
def populate_db(db_name):
exist_db = os.path.exists(db_name)
with connection(db_name) as conn:
with get_cursor(conn) as cursor:
if not exist_db:
with open('set_base.sql', 'rt') as set_base_script:
cursor.executescript(set_base_script.read())
if not exist_db:
with open('russian-subjects-master/native/regions.json',
'rt') as regions_json:
regions = json.loads(regions_json.read())
with open('russian-subjects-master/native/cities.json',
'rt') as cities_json:
cities = json.loads(cities_json.read())
for region_item in regions:
region = Region(name=region_item['name'], db_conn=conn).save()
for city_item in filter(
lambda each: each['region_id'] == region_item['id'],
cities):
City(name=city_item['name'], region=region,
db_conn=conn).save()
def remove_by_id(cls, id, db_conn):
with get_cursor(db_conn) as cursor:
cursor.execute(f'delete from {cls.__name__} where id=?;', (id, ))
