def get_all():
sql_string = "SELECT id, name FROM region"
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string)
return [
RegionEntity(_id=get_item(row, index=0),
_name=get_item(row, index=1))
for row in cursor.fetchall()
]
def get_by_region_id(region_id: int):
sql_string = "SELECT id, name, region_id FROM city WHERE region_id=?"
prepared_statements = (region_id,)
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string, prepared_statements)
return [
CityEntity(
_id=get_item(row, index=0),
_name=get_item(row, index=1),
_region_id=get_item(row, index=2),
) for row in cursor.fetchall()
]
def get_stat():
sql_string = """SELECT region.id, region.name, COUNT(distinct comment.id) AS comment_count FROM region
INNER JOIN city ON city.region_id = region.id
INNER JOIN comment ON comment.city_id = city.id
GROUP BY region.id
HAVING COUNT(distinct comment.id) > 5;
"""
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string)
return [
RegionStatEntity(
_id=get_item(row, index=0),
_name=get_item(row, index=1),
_comment_count=get_item(row, index=2),
) for row in cursor.fetchall()
]
def get_stat(region_id: int):
sql_string = """SELECT city.id, city.name, COUNT(distinct comment.id) AS comment_count FROM city
INNER JOIN comment ON comment.city_id = city.id
WHERE city.region_id=?
GROUP BY city.id;
"""
prepared_statements = (region_id,)
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string, prepared_statements)
return [
CityStatEntity(
_id=get_item(row, index=0),
_name=get_item(row, index=1),
_comment_count=get_item(row, index=2),
) for row in cursor.fetchall()
]
def add(request, groups):
"""Add new comment"""
response_headers = [
('Content-Type', 'text/html; charset=utf-8'),
]
request_method = request.environ.get('REQUEST_METHOD')
if request_method == 'POST':
last_name = get_item(request.form_input.get('last_name', []), None)
first_name = get_item(request.form_input.get('first_name', []),
None)
second_name = get_item(request.form_input.get('second_name', []),
None)
city = get_item(request.form_input.get('city', []), None)
phone = get_item(request.form_input.get('phone', []), None)
email = get_item(request.form_input.get('email', []), None)
text = get_item(request.form_input.get('text', []), None)
try:
city_id = city
except ValueError:
city_id = None
result = None
if last_name and first_name and text:
comment_entity = CommentEntity(
_last_name=last_name,
_first_name=first_name,
_text=text,
_city_id=city_id,
_second_name=second_name,
_phone=phone,
_email=email,
)
result = CommentRepository.create(comment_entity)
if result:
return redirect(url='/comment/success/')
else:
return redirect(url='/comment/fail/')
elif request_method == 'GET':
regions = RegionRepository.get_all()
option_template = get_template('comment/option.html')
region_options = [
option_template.safe_substitute(**{
'id': region.id,
'name': region.name
}) for region in regions
]
response_context = {'region_options': "".join(region_options)}
response_body = get_template('comment/add.html').safe_substitute(
**response_context)
response_status = 200
return (response_body, response_headers, response_status)
else:
return method_not_allowed(request, groups)
def get_all():
sql_string = """SELECT comment.id, comment.first_name, comment.second_name, comment.last_name,
comment.phone, comment.email, comment.text AS region_name FROM comment
ORDER BY id DESC;
"""
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string)
return [
CommentEntity(
_first_name=get_item(row, index=1),
_last_name=get_item(row, index=3),
_text=get_item(row, index=6),
_id=get_item(row, index=0),
_second_name=get_item(row, index=2),
_phone=get_item(row, index=4),
_email=get_item(row, index=5),
) for row in cursor.fetchall()
]