def getcity(city_id):
dbman.connect()
info = {}
sql = '''SELECT * FROM city WHERE id=%s LIMIT 1'''
result = dbman.execute(sql, city_id)
if len(result) == 1:
register = result[0]
cityID, name, countryCode, district, population = register
p_url, n_url = None, None
sql = '''SELECT id FROM city WHERE id<%s ORDER BY id DESC LIMIT 1'''
p_result = dbman.execute(sql, city_id)
if len(p_result) == 1:
p_url = url_for('getcity', city_id=p_result[0][0], _external=True)
n_result = dbman.execute(sql, city_id)
sql = '''SELECT id FROM city WHERE id>%s ORDER BY id LIMIT 1'''
if len(n_result) == 1:
n_url = url_for('getcity', city_id=n_result[0][0], _external=True)
self_url = url_for('getcity', city_id=cityID, _external=True)
parent_url = url_for('getcities', _external=True)
country_url = url_for('getcountry', code=countryCode, _external=True)
info = {'urls': {'self': self_url, 'parent': parent_url,
'next': n_url, 'previous': p_url,
'country': country_url},
'city': {'city_id': cityID,
'name': name,
'country_code': countryCode,
'district': district,
'population': population}}
dbman.disconnect()
return prepare_response('city.xml', info)
def getworld():
info = {
'urls': {
'self': url_for('getworld', _external=True),
'cities': url_for('getcities', _external=True),
'countries': url_for('getcountries', _external=True),
'countrieslanguages': url_for('getcountrieslanguages',
_external=True)
}
}
return prepare_response('world.xml', info)
def getcountrieslanguages():
dbman.connect()
try:
limit = int(request.args.get('limit', 20))
offset = int(request.args.get('offset', 0))
if limit < 0:
raise AttributeError('LIMIT must be positive')
if offset < 0:
raise AttributeError('OFFSET must be positive')
except Exception:
return make_response(jsonify({'error': 'Invalid Filters'}), 400)
sql = '''SELECT * FROM countrylanguage ORDER BY countrycode LIMIT %s, %s'''
result = dbman.execute(sql, offset, limit)
info = {}
if len(result) > 0:
p_url, n_url = None, None
p_offset = 0 if offset < limit else offset - limit
sql = '''SELECT * FROM countrylanguage ORDER BY countrycode LIMIT %s, %s'''
if len(dbman.execute(sql, p_offset, limit)) > 0 and offset != 0:
p_url = url_for('getcountrieslanguages', offset=p_offset, limit=limit, _external=True)
n_offset = offset + limit
sql = '''SELECT * FROM countrylanguage ORDER BY countrycode LIMIT %s, %s'''
if len(dbman.execute(sql, n_offset, limit)) > 0:
n_url = url_for('getcountrieslanguages', offset=n_offset, limit=limit, _external=True)
info = {'count': len(result), 'countries': {}, 'urls': []}
for register in result:
countrycode, language, is_official, percentage = register
if countrycode not in info['countries'].keys():
url = url_for('getcountrylanguages', countrycode=countrycode, _external=True)
info['countries'][countrycode] = {'languages': [], 'url': url}
info['countries'][countrycode]['languages'].append({
'language': language,
'is_official': is_official,
'percentage': percentage, })
self_url = url_for('getcountrieslanguages', offset=offset, limit=limit, _external=True)
parent_url = url_for('getworld', _external=True)
info['urls'] = {'self': self_url, 'parent': parent_url,
'next': n_url, 'previous': p_url}
dbman.disconnect()
return prepare_response('countrieslanguages.xml', info)
def getcities():
dbman.connect()
try:
limit = int(request.args.get('limit', 20))
offset = int(request.args.get('offset', 0))
if limit < 0:
raise AttributeError('LIMIT must be positive')
if offset < 0:
raise AttributeError('OFFSET must be positive')
except Exception:
return make_response(jsonify({'error': 'Invalid Filters'}), 400)
sql = '''SELECT * FROM city ORDER BY id LIMIT %s, %s'''
result = dbman.execute(sql, offset, limit)
info = {}
if len(result) > 0:
p_url, n_url = None, None
p_offset = 0 if offset < limit else offset - limit
sql = '''SELECT * FROM city ORDER BY id LIMIT %s, %s'''
if len(dbman.execute(sql, p_offset, limit)) > 0 and offset != 0:
p_url = url_for('getcities', offset=p_offset, limit=limit, _external=True)
sql = '''SELECT * FROM city ORDER BY id LIMIT %s, %s'''
if len(dbman.execute(sql, offset + limit, limit)) > 0:
n_url = url_for('getcities', offset=(offset + limit), limit=limit, _external=True)
info = {'count': len(result), 'cities': [], 'urls': []}
for register in result:
cityID, name, countryCode, district, population = register
info['cities'].append({'city_id': cityID,
'name': name,
'country_code': countryCode,
'district': district,
'population': population,
'url': url_for('getcity', city_id=cityID, _external=True)})
self_url = url_for('getcities', offset=offset, limit=limit, _external=True)
parent_url = url_for('getworld', _external=True)
info['urls'] = {'self': self_url, 'parent': parent_url,
'next': n_url, 'previous': p_url}
dbman.disconnect()
return prepare_response('cities.xml', info)
def getcountrylanguages(countrycode):
dbman.connect()
info = {}
sql = 'SELECT * FROM countrylanguage WHERE countrycode=%s'
result = dbman.execute(sql, countrycode)
if len(result) > 0:
p_url, n_url = None, None
p_result = dbman.execute(sql, countrycode)
sql = '''SELECT countrycode FROM countrylanguage WHERE countrycode<%s
ORDER BY countrycode DESC LIMIT 1'''
if len(p_result) == 1:
p_url = url_for('getcountrylanguages', countrycode=p_result[0][0], _external=True)
sql = '''SELECT countrycode FROM countrylanguage
WHERE countrycode>%s ORDER BY countrycode LIMIT 1'''
n_result = dbman.execute(sql, countrycode)
if len(n_result) == 1:
n_url = url_for('getcountrylanguages', countrycode=n_result[0][0], _external=True)
self_url = url_for('getcountrylanguages', countrycode=countrycode, _external=True)
parent_url = url_for('getcountrieslanguages', _external=True)
country_url = url_for('getcountry', code=countrycode, _external=True)
info = {'urls': {'self': self_url, 'parent': parent_url,
'next': n_url, 'previous': p_url,
'country': country_url},
'country_code': countrycode,
'count': len(result),
'languages': []}
for register in result:
countrycode, language, is_official, percentage = register
info['languages'].append(
{'language': language,
'is_official': is_official,
'percentage': percentage, })
dbman.disconnect()
return prepare_response('countrylanguages.xml', info)
def getcountrycities(countrycode):
dbman.connect()
info = {}
sql = '''SELECT * FROM city WHERE countrycode=%s'''
result = dbman.execute(sql, countrycode)
if len(result) > 0:
p_url, n_url = None, None
p_result = dbman.execute(sql, countrycode)
sql = '''SELECT code FROM country WHERE code<%s ORDER BY code DESC LIMIT 1'''
if len(p_result) == 1:
p_url = url_for('getcountrycities', countrycode=p_result[0][0], _external=True)
sql = '''SELECT code FROM country WHERE code>%s ORDER BY code LIMIT 1'''
n_result = dbman.execute(sql, countrycode)
if len(n_result) == 1:
n_url = url_for('getcountrycities', countrycode=n_result[0][0], _external=True)
self_url = url_for('getcountrycities', countrycode=countrycode, _external=True)
parent_url = url_for('getcities', _external=True)
country_url = url_for('getcountry', code=countrycode, _external=True)
info = {'country_code': countrycode,
'count': len(result),
'urls': {'self': self_url, 'parent': parent_url,
'next': n_url, 'previous': p_url,
'country': country_url},
'cities': []}
for register in result:
cityID, name, countryCode, district, population = register
info['cities'].append({'city_id': cityID,
'name': name,
'district': district,
'population': population})
dbman.disconnect()
return prepare_response('cities.xml', info)
def getcountry(code):
dbman.connect()
info = {}
sql = '''SELECT * FROM country WHERE code=%s LIMIT 1'''
result = dbman.execute(sql, code)
if len(result) == 1:
register = result[0]
code, name, continent, region, surface_area, independent_day, \
population, life_expectation, gnp, gnp_old, local_name, \
government_form, head_of_state, capital, code2 = register
p_url, n_url = None, None
p_result = dbman.execute(sql, code)
sql = '''SELECT code FROM country WHERE code<%s ORDER BY code DESC LIMIT 1'''
if len(p_result) == 1:
p_url = url_for('getcountry', code=p_result[0][0], _external=True)
sql = '''SELECT code FROM country WHERE code>%s ORDER BY code LIMIT 1'''
n_result = dbman.execute(sql, code)
if len(n_result) == 1:
n_url = url_for('getcountry', code=n_result[0][0], _external=True)
self_url = url_for('getcountry', code=code, _external=True)
parent_url = url_for('getcountries', _external=True)
cities_url = url_for('getcountrycities',
countrycode=code,
_external=True)
languages_url = url_for('getcountrylanguages',
countrycode=code,
_external=True)
info = {
'urls': {
'self': self_url,
'parent': parent_url,
'next': n_url,
'previous': p_url,
'cities': cities_url,
'languages': languages_url
},
'country': {
'code': code,
'name': name,
'continent': continent,
'region': region,
'surface_area': surface_area,
'independent_day': independent_day,
'population': population,
'life_expectation': life_expectation,
'gnp': gnp,
'gnp_old': gnp_old,
'local_name': local_name,
'government_form': government_form,
'head_of_state': head_of_state,
'capital': capital,
'code2': code2
}
}
dbman.disconnect()
return prepare_response('country.xml', info)
def getcountries():
dbman.connect()
try:
limit = int(request.args.get('limit', 20))
offset = int(request.args.get('offset', 0))
if limit < 0:
raise AttributeError('LIMIT must be positive')
if offset < 0:
raise AttributeError('OFFSET must be positive')
except Exception:
return make_response(jsonify({'error': 'Invalid Filters'}), 400)
sql = '''SELECT * FROM country ORDER BY code LIMIT %s, %s'''
result = dbman.execute(sql, offset, limit)
info = {}
if len(result) > 0:
p_url, n_url = None, None
sql = '''SELECT * FROM country ORDER BY code LIMIT %s, %s'''
p_offset = 0 if offset < limit else offset - limit
if len(dbman.execute(sql, p_offset, limit)) > 0 and offset != 0:
p_url = url_for('getcountries',
offset=p_offset,
limit=limit,
_external=True)
sql = '''SELECT * FROM country ORDER BY code LIMIT %s, %s'''
if len(dbman.execute(sql, offset + limit, limit)) > 0:
n_url = url_for('getcountries',
offset=(offset + limit),
limit=limit,
_external=True)
info = {'count': len(result), 'countries': [], 'urls': []}
for register in result:
code, name, continent, region, surface_area, independent_day, \
population, life_expectation, gnp, gnp_old, local_name, \
government_form, head_of_state, capital, code2 = register
info['countries'].append({
'code':
code,
'name':
name,
'continent':
continent,
'region':
region,
'surface_area':
surface_area,
'independent_day':
independent_day,
'population':
population,
'life_expectation':
life_expectation,
'gnp':
gnp,
'gnp_old':
gnp_old,
'local_name':
local_name,
'government_form':
government_form,
'head_of_state':
head_of_state,
'capital':
capital,
'code2':
code2,
'url':
url_for('getcountry', code=code, _external=True)
})
self_url = url_for('getcountries',
offset=offset,
limit=limit,
_external=True)
parent_url = url_for('getworld', _external=True)
info['urls'] = {
'self': self_url,
'parent': parent_url,
'next': n_url,
'previous': p_url
}
dbman.disconnect()
return prepare_response('countries.xml', info)