def load_countries():
"""Load countries from output.txt into database."""
print "Start loading Countries"
# Delete all rows in table, so if we need to run this a second time,
# we won't be trying to add duplicate items
Country.query.delete()
# Read file and insert data
for row in open("seed_data/output.txt"):
r = row.splitlines()
for rn in r:
name, country_id = rn.split(",")
country = Country(name=name, country_id=country_id)
# Add to the session
db.session.add(country)
# Add Soviet Union
sovu = Country(name='Soviet Union', country_id='SU')
db.session.add(sovu)
# Commit
db.session.commit()
print "Countries"
def post(self):
post_country = Country()
post_country.Name = self.request.get("countryname")
post_country.Total_Production = float(
self.request.get("totalproduction"))
post_country.Total_Usage = float(self.request.get("totalusage"))
post_country.Energy_Shortage = float(self.request.get("shorageDay"))
post_country.Region = self.request.get("region")
post_country.Population = float(self.request.get("population"))
api = self.request.get("api")
post_country.API = api
post_country.Energy_API = self.request.get("energy_api")
post_country.Production_API = self.request.get("production_api")
post_country.Video_API = self.request.get("video_api")
# parse from apis
try:
response = urllib2.urlopen(wikiURL + api)
data = json.load(response)
keys = data["query"]["pages"].keys()
temp = data["query"]["pages"][keys[0]]["extract"]
except urllib2.URLError:
temp = ""
post_country.description = temp
post_country.put()
self.redirect("/api/add/country")
def add_country(iso3, name):
"""Add a new country to db."""
country = Country(isocode3=iso3, name=name)
# db.session.add(country)
# db.session.commit()
return country
def write_country_to_db(line_to_write):
if not sess.query(Country).filter_by(
country_code=line_to_write['country_code']).all():
continent = sess.query(Continent).filter_by('')
country = Country(name=line_to_write['country_full'],
country_code=line_to_write['country_code'])
sess.add(country)
sess.commit()
def write_countrydb(countries):
"""Write country names and codes to database using countries dictionary"""
for country in countries:
dbcountry = Country(country_code=countries[country],
country_name=country)
db.session.add(dbcountry)
db.session.commit()
def import_countries(self, country_csv):
countries = []
lines = country_csv.split('\n')
for line in lines:
if len(line.strip()) > 0:
fields = line.split(';')
country = Country()
country.order = int(fields[0])
country.name = fields[1].strip()
country.local_name = fields[2].strip()
country.put()
countries.append(country)
return countries
def load_currency():
"""Load country & currency data from u.country into travelers db"""
for i, row in enumerate(open("seed_data/u.country_code")):
row = row.rstrip()
country_name, country_code, currency_name, currency_code = row.split(
",")
country = Country(country_name=country_name,
country_code=country_code,
currency_name=currency_name,
currency_code=currency_code)
db.session.add(country)
db.session.commit()
def post(self):
json_data = request.get_json(force=True)
if json_data is None:
return {'message': 'No input data provided'}, 400
data, errors = country_schema.load(json_data)
if errors:
return errors, 422
country = Country.query.filter_by(name=data['name']).first()
if country:
return {'message': 'Country already exists'}, 400
country = Country(name=json_data['name'])
db.session.add(country)
db.session.commit()
result = country_schema.dump(country).data
return {'status': 'success', 'data': result}, 200
def parseTable(page: str) -> List[Country]:
list = []
soup = BeautifulSoup(page, "html.parser")
codeTable = soup.find_all('table', attrs={'class':
'wikitable'})[0].find_all('tr')
for countryTable in codeTable:
country = Country()
try:
country.code = countryTable.span.text
country.name = countryTable.a.text
# country.flag_page_link = Flag(domain + countryTable.a['href']) # 需要svg格式时
country.flag_page_link = Flag(
"https:" +
countryTable.img['src'].replace("22px", "480px")) # 需要png格式
list.append(country)
except AttributeError:
pass
return list
def load_countries():
print "Countries"
url = "http://api.travelpayouts.com/data/countries.json?token={}".format(
travel_payouts_api)
r = requests.get(url)
data = r.text
results = json.loads(data)
for key in results:
country_name = key['name']
country_code = key['code']
country = Country(name=country_name, code=country_code)
db.session.add(country)
db.session.commit()
def load_countries():
Country.query.delete()
for row in open("country_data_geo.csv"):
row = row.rstrip()
items = row.split(",")
country_code = items[0]
country_code = country_code.replace('"','')
country_name = items[1]
country_name = country_name.replace('"', '')
country_lat = float(items[2])
country_long = float(items[3])
country = Country(country_code=country_code,
country_name=country_name,
country_lat=country_lat,
country_long=country_long)
db.session.add(country)
db.session.commit()
print("countries loaded")
def write_to_db():
"""
Writes the results from the fetch_data function to the Sqlite Database.
If the database if already populated it updates the values for each country.
"""
collect_entries = []
for idx, c in enumerate(fetch_data(URL)):
numbers = 0
if len(c[4]) > 5:
logging.info(f'len(c[4]) < 6: {numbers}')
numbers = int(c[4].replace(',', ''))
if len(c[4]) < 5 and len(c[4]) > 1:
logging.info(f'{c[4]} between 1 and 1000')
numbers = c[4]
if len(c[4]) == 1:
logging.warning(f'{c[4]} Does not exist')
numbers = 0
# Exists or not
value = db.query(Country).filter(Country.country == c[1]).first()
if value:
value.total_cases = int(c[2].replace(',', '')) or 0
value.new_cases = int(c[3][1:].replace(',', '')) if c[3] else 0
value.total_deaths = numbers
value.new_deaths = int(c[5][1:].replace(',', '')) if c[5] else 0
else:
collect_entries.append(
Country(
country=c[1],
total_cases=int(c[2].replace(',', '')) or 0,
new_cases=int(c[3][1:].replace(',', '')) if c[3] else 0,
total_deaths=numbers,
new_deaths=int(c[5][1:].replace(',', '')) if c[5] else 0,
population=int(c[13].replace(',', ''))
if ',' in c[13] else 0))
db.add_all(collect_entries)
db.commit()
db.close()
def create_missing_country_city():
to_save = []
cnt_added = 0
city_add = 0
with open('../../data/json/finale.json') as ffop:
data = json.load(ffop)
for line in data:
if 'continent_code' in line.keys():
if not sess.query(Country).filter_by(
country_code=line['country_code']).all():
continent = sess.query(Continent).filter_by(
continent_code=line['continent_code']).all()[0]
continent_id = continent.id
country = Country(name=line['country_full'],
country_code=line['country_code'],
continent_id=continent_id)
sess.add(country)
sess.commit()
print('Added a country:', line['country_full'])
cnt_added += 1
print(cnt_added)
else:
country = sess.query(Country).filter_by(
country_code=line['country_code']).all()[0]
city = City(name=line['city_name'],
city_id=line['city_id'],
country_id=country.id,
lat=line['lat'],
lon=line['lon'])
city_add += 1
to_save.append(city)
print('Added a city:', line['city_name'])
print(city_add)
sess.bulk_save_objects(to_save)
sess.commit()
def load_countries():
# open csv file (us_states)
d3worldfile = open("data/d3_world_data.csv")
#read each line
for line in d3worldfile:
# split on "," --> list
line_list = line.split("|")
# each item in list --> remove whitespace .strip()
for i in range(len(line_list)):
line_list[i] = line_list[i].strip()
country_id, country_name = line_list[0], line_list[1]
print "COUNTRY_ID: %s, COUNTRY_NAME: %s" % (country_id, country_name)
# # make State(....) object
country = Country(country_id=country_id, country_name=country_name)
# add to session
db.session.add(country)
# commit session
db.session.commit()
debug()
def install():
Base.metadata.create_all(Session().bind)
data = [('Chicago', 'United States', ('60601', '60602', '60603', '60604')),
('Montreal', 'Canada', ('H2S 3K9', 'H2B 1V4', 'H7G 2T8')),
('Edmonton', 'Canada', ('T5J 1R9', 'T5J 1Z4', 'T5H 1P6')),
('New York', 'United States', ('10001', '10002', '10003', '10004',
'10005', '10006')),
('San Francisco', 'United States', ('94102', '94103', '94104',
'94105', '94107', '94108'))]
countries = {}
all_post_codes = []
for city, country, postcodes in data:
try:
country = countries[country]
except KeyError:
countries[country] = country = Country(country)
city = City(city, country)
pc = [PostalCode(code, city) for code in postcodes]
Session.add_all(pc)
all_post_codes.extend(pc)
for i in xrange(1, 51):
person = Person(
"person %.2d" % i,
Address(street="street %.2d" % i,
postal_code=all_post_codes[random.randint(
0,
len(all_post_codes) - 1)]))
Session.add(person)
Session.commit()
# start the demo fresh
Session.remove()
def get(self):
config = Configuration()
ruler = """
--------------------------------------------------------------------------------
0________1_________2_________3_________4_________5_________6_________7_________8
1 0 0 0 0 0 0 0 0
--------------------------------------------------------------------------------
"""
template = Template(config.get('WELCOME_MAIL_TEXT'))
member = Member()
member.name = 'Ola Normann'
member.address = 'Norskeveien 1'
member.zipcode = '9876'
member.city = 'Olabyen'
member.country = Country().all().order('order').fetch(1)[0]
member.email = '*****@*****.**'
member.phone = '916 75 105'
member.phone_home = '939 90 115'
member.phone_work = '101 33 116'
member.number = '9669'
member.access_code = 'BBQWTF'
member.member_type = MemberType.all().order('order').fetch(1)[0]
sample_data = {
'year': 2014,
'fee': 400,
'account_no': config.get('GIRO_ACCOUNT_NO'),
'member': member,
'profile_url': constants.PROFILE_URL
}
sample_text = template.render(sample_data)
# Merge template before submitting text
data = {'text': ruler + sample_text + ruler}
template = JINJA_ENVIRONMENT.get_template(
'templates/settings/email_preview.html')
self.response.write(template.render(data))
def get_countries(r, region):
"""Query the RESTCountries API, get country-related information back, and
commit it to the database. Takes a request object and a region string, like "Africa"."""
info = r.json()
for country_dict in info:
name = country_dict["name"]
name = name.encode('utf-8', 'ignore')
alpha_code = country_dict["alpha3Code"]
demonym = country_dict["demonym"]
subregion = country_dict["subregion"]
if region == "Americas":
if subregion == "Northern America" or subregion == "Central America":
new_region = "North America"
else:
new_region = subregion
else:
new_region = region
capital = country_dict["capital"]
lang_list = []
iso_list = country_dict["languages"]
for lang in iso_list:
lang = lang.encode('utf-8', 'ignore')
lang = translate_lang(lang)
lang_list.append(lang)
if name not in countries_to_exclude:
country = Country(
country_name=name,
alpha_code=alpha_code,
demonym=demonym,
continent_name=new_region,
languages=lang_list,
capital=capital
)
db.session.add(country)
db.session.commit()
from sqlalchemy import Column, DateTime, Integer, String, Boolean
from sqlalchemy.ext.declarative import declarative_base
import pycountry
from model import Base, Country, City, Post, Continent
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///project.db')
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
session = DBSession()
import pycountry
my_countries = list(pycountry.countries)
for i in my_countries:
country = Country()
country.name = i.name
session.add(country)
continents = [
'Europe', 'Africa', 'Asia', 'North America', 'South America', 'Australia'
]
for i in continents:
con = Continent()
con.name = i
session.add(con)
session.commit()
def task_country_restore(items):
for item in items:
country = Country(key_name=item['countryId'])
country.order = item['order']
country.name = item['name']
country.put()
