def initialize_device():
"""
Set up the Device info into the database.
By this time, the HardwareDefinition and SoftwareDefinition
entries must be entered.
"""
session = Session()
hd = session.query(HardwareDefinition).one()
sd = session.query(SoftwareDefinition).one()
device = Device(id=hd.serial_number,
interior_sensor=hd.interior_sensor,
exterior_sensor=hd.exterior_sensor)
device.hardware_version = hd.hardware_version
device.software_version = sd.software_version
device.database_service = True # database always set to True
device.device_service = sd.device_service
device.grainbin_service = sd.grainbin_service
session.add(device)
# set grainbin info
if sd.grainbin_service:
grainbins = initialize_grainbin(device.id, hd.grainbin_reader_count)
device.grainbin_count = len(grainbins)
session.add_all(grainbins)
session.commit()
session.close()
return
def new_book_save():
status, msg = True, ''
b_name = request.args.get('name')
s = Session()
b = Book(b_name)
for key, val in request.args.items():
if key == 'name':
continue
a = s.query(Author).filter_by(id=key).first()
b.authors.append(a)
s.add(b)
try:
s.commit()
except IntegrityError:
status = False
msg = 'The book with name %s already exists' % b_name
s.close()
return json.dumps({'ok': status, 'msg': msg})
def PUT(self, id):
data = web.data()
item = json.loads(data)
if id == '0':
id = None
code = item['code'] if ('code' in item) else 'code'
title = item['title'] if ('title' in item) else 'title'
try:
posttime = datetime.datetime.strptime(item['posttime'], '%Y-%m-%d')
except (KeyError, ValueError):
posttime = datetime.datetime.now()
remark = item['remark'] if ('remark' in item) else ''
articleobj = ArticleModel(id, code, title, posttime, remark)
articledict = {
'code': code,
'title': title,
'posttime': posttime,
'remark': remark
}
if id:
Session.query(ArticleModel).filter(
ArticleModel.id == id).update(articledict)
else:
Session.add(articleobj)
Session.commit()
def create_default_user():
session = Session()
default_user = User('admin', 'password1234', '*****@*****.**')
session.add(default_user)
session.commit()
session.close()
return
def make_account(username, password):
session = Session()
a = Account(username, password)
session.add(a)
bot = MBotData("testerbot", 0, 0, )
session.commit()
def mythicspoiler_crawl(self, context):
local_session = Session()
cards = self.ms.get_cards_from_news()
for page, image_url, card_set in cards:
if image_url not in self.mythicspoiler_futur_cards_url:
config.bot_logger.info(
f"New card detected from mythicspoiler: {page}")
# New card detected on mythic spoiler, save it
self.mythicspoiler_futur_cards_url.append(image_url)
# Try to see if it has already been spoiled
im = Image(location=image_url,
descr=im_utils.descript_image(image_url))
if not self.sd.is_duplicate(
im, [s.image.descr for s in self.spoiled]):
# card not recognize as a duplicate, save then publish it
local_session.add(im)
sp = Spoiler(url=page,
source=SpoilerSource.MYTHICSPOILER.value,
source_id=SpoilerSource.MYTHICSPOILER.value,
found_at=datetime.now(),
set_code=card_set)
sp.image = im
sp.set = local_session.query(Set).filter(
Set.code == card_set).first()
local_session.add(sp)
self.spoiled.append(sp)
self.send_spoiler(sp, context)
local_session.commit()
def insert_db_weather(weather_values):
"""Function for inserting scraped data from Weather API into database"""
session = Session()
new_data = Weather(
coord_lon=weather_values["coord"]["lon"],
coord_lat=weather_values["coord"]["lat"],
weather_id=weather_values["weather"][0]["id"],
weather_main=weather_values["weather"][0]["main"],
weather_description=weather_values["weather"][0]["description"],
weather_icon=weather_values["weather"][0]["icon"],
base=weather_values["base"],
main_temp=weather_values["main"]["temp"],
main_pressure=weather_values["main"]["pressure"],
main_humidity=weather_values["main"]["humidity"],
main_temp_min=weather_values["main"]["temp_min"],
main_temp_max=weather_values["main"]["temp_max"],
visibility=weather_values["visibility"],
wind_speed=weather_values["wind"]["speed"],
wind_deg=weather_values["wind"]["deg"],
clouds_all=weather_values["clouds"]["all"],
dt=datetime.fromtimestamp(weather_values["dt"]),
sys_type=weather_values["sys"]["type"],
sys_id=weather_values["sys"]["id"],
sys_message=weather_values["sys"]["message"],
sys_country=weather_values["sys"]["country"],
sys_sunrise=datetime.fromtimestamp(weather_values["sys"]["sunrise"]),
sys_sunset=datetime.fromtimestamp(weather_values["sys"]["sunset"]),
city_id=weather_values["id"],
city_name=weather_values["name"],
cod=weather_values["cod"])
session.add(new_data)
session.commit()
session.close()
def scryfall_cards_crawl(self, context):
local_session = Session()
futur_cards = scryfall.get_futur_cards()
for futur_card in futur_cards:
if not futur_card.get("id") in self.scryfall_futur_cards_id:
config.bot_logger.info(
f"New card detected from scryfall: {futur_card.get('name')}"
)
# New card detected, add it to scryfall list
self.scryfall_futur_cards_id.append(futur_card.get("id"))
# Try to see if it has already been spoiled
for i_url in scryfall.get_image_urls(futur_card):
im = Image(location=i_url,
descr=im_utils.descript_image(i_url))
if not self.sd.is_duplicate(
im, [s.image.descr for s in self.spoiled]):
# card not recognize as a duplicate, save then publish it
local_session.add(im)
sp = Spoiler(url=scryfall.get_card_url(futur_card),
source=SpoilerSource.SCRYFALL.value,
source_id=futur_card.get("id"),
found_at=datetime.now(),
set_code=futur_card.get("set_code", None))
sp.image = im
local_session.add(sp)
self.spoiled.append(sp)
sp.set = local_session.query(Set).filter(
Set.code == futur_card.get("set_code")).first()
self.send_spoiler(sp, context)
local_session.commit()
def syncSeason(self, serie, season):
qry = Session.query(Serie).filter_by(path=serie)
if qry.count() == 0:
raise Exception("No serie linked to %s/%s" % (serie, saison))
serie = qry.one()
path = season
num = getSeasonNumFromFoldername(season)
if num is None:
raise Exception("This is not a season (%s)" % season)
season = filter(lambda season: season.num == num, serie.seasons)
if len(season) == 0:
season = Season(num=num, path=path)
Session.add(season)
serie.seasons.append(season)
else:
assert 1 == len(season)
season = season[0]
season.num = num
season.path = path
episodes = glob(os.path.join(self.basepath, serie.path, season.path, "*"))
episodes = filter(lambda episode: os.path.isfile(episode), episodes)
for episode in [os.path.basename(e) for e in episodes]:
try:
self.syncEpisode(serie.path, season.path, episode)
except:
pass
def create_statuses():
session = Session()
existing = set(item.name for item in session.query(Status))
for status in ["PASS", "FAIL", "OK", "ERROR", "TIMEOUT", "CRASH",
"ASSERT", "SKIP", "NOTRUN"]:
if status not in existing:
session.add(Status(name=status))
session.commit()
def add_order(self):
s = Session()
ord = Order()
ord.manager_id = self.parent.user.id
ord.device = unicode(self.ui.device.text())
ord.description = unicode(self.ui.description)
ord.client_id = s.query(Client).filter_by(id=unicode(self.ui.client.itemText(self.ui.client.currentIndex())).split()[0]).one().id
s.add(ord)
s.commit()
s.close()
self.close()
def mul():
session = Session()
# select the rows that doesn't have result
for datarow in session.query(ClientData).all():
if not datarow.result:
datarow.result = mul_data(
datarow.rawData) # add result to the data row
session.add(datarow) # update the datarow in the DB
session.commit() # commit all changes to DB
session.close()
def post(self):
user_id = int(self.get_cookie('user_id'))
content = self.get_argument('content')
session = Session()
weibo = Weibo(user_id=user_id,
content=content,
created=datetime.datetime.now())
session.add(weibo)
session.commit()
self.redirect('/user/show?weibo_id=%s' % weibo.id)
def post(self):
content = self.get_argument('content')
wb_id = self.get_argument('wb_id')
user_id = self.get_cookie('user_id')
session = Session()
comment = Comment(user_id=user_id,
wb_id=wb_id,
content=content,
created=datetime.datetime.now())
session.add(comment)
session.commit()
self.redirect('/user/show?weibo_id=%s' % wb_id)
def add_provider(db_enabled, lastname, firstname, credentials, addr1, addr2, city, zipcode, state,latitude, longitude):
# if db is enabled, then open a session with the database
if db_enabled:
session = Session()
# create an instance of the Provider type
provider = Provider(lastname=lastname, firstname=firstname,
credentials=credentials, addr1=addr1, addr2=addr2,
city=city, zipcode=zipcode,state=state,
latitude=latitude, longitude=longitude)
#To check if the record already exists in database
p = select([Provider.firstname]).where(Provider.firstname+Provider.lastname+Provider.credentials
+Provider.addr1+Provider.addr2 == firstname+lastname+credentials+addr1+addr2)
res = session.execute(p)
prov_data = res.fetchone()
session.close()
#To check if record exists, then this step will be skipped
if not(prov_data):
session = Session()
# To fetch the Geographical coordinates from Zip_geotable
z = select([Zip_geo.latitude, Zip_geo.longitude]).where(Zip_geo.zipcode == zipcode)
result = session.execute(z)
geo_data = result.fetchone()
if geo_data:
latitude = geo_data.latitude
longitude= geo_data.longitude
#print db_enabled, lastname, firstname, credentials, addr1, addr2, city, zipcode, state,latitude, longitude
# create an instance of the Provider type
provider = Provider(lastname=lastname, firstname=firstname,
credentials=credentials, addr1=addr1, addr2=addr2,
city=city, zipcode=zipcode,state=state,
latitude=latitude, longitude=longitude)
# if db is enabled, then add to the recordset and commit the txn
session.add(provider)
session.commit()
session.close()
return provider
def add_to_list(uuid, product_id, trees_difference):
msg = "Success"
try:
session = Session()
user = session.query(User).filter(User.greenlist_uuid == uuid).one_or_none()
if session.query(UserList).filter(
UserList.user_id == user.id).filter(UserList.product_id == product_id).first() is None:
product = session.query(Product).filter(Product.id == product_id).one_or_none()
product_to_user = UserList(user, product, trees_difference)
session.add(product_to_user)
session.commit()
session.close()
except Exception as e:
msg = e
finally:
return msg
def add_zipdata(db_enabled, zipcode, latitude, longitude):
# if db is enabled, then open a session with the database
if db_enabled:
session = Session()
# create an instance of the Zip_Geo type
zip_geo= Zip_geo(zipcode=zipcode, latitude=latitude, longitude=longitude)
# if db is enabled, then add to the recordset and commit the txn
if db_enabled:
session.add(zip_geo)
session.commit()
session.close()
return zip_geo
def syncSerie(self, serie):
self._setDetails(serie)
for season_bs in self.details:
season_num = 'S%s' % str(season_bs['number']).zfill(2)
season = filter(lambda season: season.num == season_num, serie.seasons)
if len(season) == 0:
season = Season(num=season_num)
Session.add(season)
serie.seasons.append(season)
else:
assert(len(season) == 1)
season = season[0]
self.syncSeason(serie, season)
def weather_to_db(data):
session = Session()
weather = json.loads(data)
print(weather)
print(type(weather), len(weather))
timestamp_dt = weather.get('dt')
print(timestamp_dt)
time_standard_dt = timestamp_convert(timestamp_dt)
timestamp_sunrise = weather.get('sys').get('sunrise')
time_standard_surise = timestamp_convert(timestamp_sunrise)
timestamp_sunset = weather.get('sys').get('sunset')
time_standard_sunset = timestamp_convert(timestamp_sunset)
kwargs ={
'coord_lon':weather.get('coord').get('lon'),
'coord_lat':weather.get('coord').get('lat'),
'weather_id':weather.get('weather')[0]['id'],
'weather_main':weather.get('weather')[0]['main'],
'weather_description': weather.get('weather')[0]['description'],
'weather_icon':weather.get('weather')[0]['icon'],
'base':weather.get('base'),
'main_temp': weather.get('main').get('temp'),
'main_feels_like':weather.get('main').get('feels_like'),
'main_temp_max':weather.get('main').get('temp_max'),
'main_temp_min': weather.get('main').get('temp_min'),
'main_pressure':weather.get('main').get('pressure'),
'main_humidity': weather.get('main').get('humidity'),
'visibility': weather.get('visibility'),
'wind_speed': weather.get('wind').get('speed'),
'wind_deg': weather.get('wind').get('speed'),
'clouds_all': weather.get('clouds').get('all'),
'dt':time_standard_dt,
'sys_type':weather.get('sys').get('type'),
'sys_id': weather.get('sys').get('id'),
'sys_country': weather.get('sys').get('country'),
'sys_sunrise':time_standard_surise,
'sys_sunset':time_standard_sunset,
'timezone':weather.get('timezone'),
'city_id':weather.get('id'),
'name': weather.get('name'),
'cod':weather.get('cod'),
}
row_weather = Weather(**kwargs)
session.add(row_weather)
session.commit()
session.close()
return
class ParsePhone(SourcePhone):
"""
解析phone.dat phone数据
"""
def __init__(self):
super(ParsePhone, self).__init__()
self.session = Session()
def get_data_mapping(self):
mapping = {}
start_offset = 8
while True:
end_offset = start_offset + self.buf[start_offset:-1].find(b'\x00')
if not len(self.buf[start_offset:end_offset]) > 1:
break
record_content = self.buf[start_offset:end_offset].decode()
province, city, zip_code, area_code = record_content.split('|')
r = Region(province=province,
city=city,
zip_code=zip_code,
area_code=area_code)
self.session.add(r)
self.session.commit()
mapping[start_offset] = r.id
start_offset = end_offset + 1
return mapping
def get_phone_data(self, mapping):
current_offset = self.first_phone_record_offset
while current_offset < len(self.buf):
buffer = self.buf[current_offset:current_offset +
self.phone_fmt_length]
number, region_offset, phone_type = struct.unpack(
self.phone_fmt, buffer)
p = Phone(number=number,
type=phone_type,
region_id=mapping[region_offset])
self.session.add(p)
self.session.commit()
current_offset += self.phone_fmt_length
return None
def main(self):
mapping = self.get_data_mapping()
self.get_phone_data(mapping)
def new_author_save():
status, msg = True, ''
a_name = request.args.get('name')
s = Session()
s.add(Author(a_name))
try:
s.commit()
except IntegrityError:
status = False
msg = 'The author with name %s already exists' % a_name
s.close()
return json.dumps({'ok': status, 'msg': msg})
class Connection:
execution_options = None
def __init__(self, execution_options=None):
self.execution_options = execution_options
self.s: Optional[Session] = None
def __enter__(self):
"""
create connection object
:return:
"""
self.engine = c.engine
Session.configure(bind=self.engine)
self.s = Session()
if self.execution_options:
self.s.connection(execution_options=self.execution_options)
return self
def __exit__(self, exc_type, exc_val, exc_tb):
"""
remove session
"""
Session.remove()
def upsert_from_form(self, model_class, form):
args_dict = {}
for column in model_class.__table__.columns:
if hasattr(form, column.name) and getattr(form, column.name).data:
# TODO Noneや''の時に更新されない
args_dict[column.name] = getattr(form, column.name).data
if form.id.data:
# update (if form has id)
id_ = form.id.data
self.s.query(model_class).filter(model_class.id == id_).update(
args_dict, synchronize_session=False)
else:
# create
new_model = model_class(**args_dict)
self.s.add(new_model)
self.s.flush()
self.s.refresh(new_model)
id_ = new_model.id
self.s.commit()
return id_
def syncSerie(self, serie):
qry = Session.query(Serie).filter_by(path=serie)
if qry.count() == 0:
Session.add(Serie(path=serie))
serie = qry.one()
if serie.seasons is None:
serie.seasons = []
seasons = glob(os.path.join(self.basepath, serie.path, "*"))
seasons = filter(lambda season: os.path.isdir(season), seasons)
for season in [os.path.basename(s) for s in seasons]:
try:
self.syncSeason(serie.path, season)
except:
pass
def stations_to_db(data):
session = Session()
stations = json.loads(data)
print(type(stations), len(stations))
for station in stations:
timestamp = station.get('last_update')
time_standard = timestamp_convert(timestamp)
kwargs = {'station_id': int(station.get('number')),
'bike_stands': int(station.get('bike_stands')),
'available_bike_stands': int(station.get('available_bike_stands')),
'available_bikes': int(station.get('available_bikes')),
'status': station.get('status'),
'last_update': time_standard
}
row_bike = Bike(**kwargs)
session.add(row_bike)
session.commit()
session.close()
return
def PUT(self, id):
data = web.data()
item = json.loads(data)
if id == '0':
id = None
code = item['code'] if ('code' in item) else 'code'
title = item['title'] if ('title' in item) else 'title'
try:
posttime = datetime.datetime.strptime(item['posttime'], '%Y-%m-%d')
except (KeyError, ValueError):
posttime = datetime.datetime.now()
remark = item['remark'] if ('remark' in item) else ''
articleobj = ArticleModel(id, code, title, posttime, remark)
articledict = {'code': code, 'title': title, 'posttime': posttime, 'remark': remark}
if id:
Session.query(ArticleModel).filter(ArticleModel.id==id).update(articledict)
else:
Session.add(articleobj)
Session.commit()
def check_realm(server):
logging.debug("Checking realm status")
realmInfo = connection.get_realm(EUROPE, wowconfig["realm"])
session = Session()
realm = session.query(Realm).filter(Realm.name == realmInfo.name).first()
if realm is None:
logging.debug("Never seen realm '{0}', creating new cache entry".format(realmInfo.name))
realm = Realm(name=realmInfo.name, lastseen=datetime.datetime.now())
session.add(realm)
prevonline = realm.online
prevlastseen = realm.lastseen
realm.online = realmInfo.status
realm.lastchecked = datetime.datetime.now()
if realm.online:
realm.lastseen = realm.lastchecked
set_pvparea(session, realm, realmInfo.tolbarad)
set_pvparea(session, realm, realmInfo.wintergrasp)
session.commit()
if (prevonline != realm.online):
if realm.online:
send_message(
server,
"announcements",
u"{0} just came online! (offline for {1})".format(
realm.name,
humanize.naturaldelta(datetime.datetime.now() - prevlastseen)
)
)
else:
send_message(
server,
wowconfig["announcements"]["realm"],
u"{0} just went offline".realm.name
)
def system_setup_init(status):
"""
Create a SystemSetup entry in the database.
Specify if the device is standalone configuration
(status=True) or alongside a FarmMonitor (status=False)
"""
session = Session()
system_setup = SystemSetup()
if status == 'True':
system_setup.standalone_configuration = True
else:
system_setup.standalone_configuration = False
session.add(system_setup)
session.commit()
initialize_routes(session)
session.close()
return
def post(self):
nickname = self.get_argument('nickname')
password = self.get_argument('password')
gender = self.get_argument('gender')
city = self.get_argument('city')
bio = self.get_argument('bio')
safe_password = self.gen_password(password)
session = Session()
user = User(nickname=nickname,
password=safe_password,
gender=gender,
city=city,
bio=bio)
session.add(user)
session.commit()
self.redirect('/user/login')
def syncSeason(self, serie, season):
self._setDetails(serie)
details = self.details
season_bs = filter(lambda s: int(s['number']) == int(season.num[1:]), self.details)
assert(len(season_bs) == 1)
season_bs = season_bs[0]
for episode_bs in self.api.resultsToArray(season_bs, 'episodes'):
episode_num = episode_bs['number']
episode = filter(lambda episode: episode.num == episode_num, season.episodes)
if len(episode) == 0:
episode = Episode(num=episode_num)
Session.add(episode)
season.episodes.append(episode)
else:
assert(len(episode) == 1)
episode = episode[0]
episode.name = name=episode_bs['title']
def init():
from model import engine, Model
Model.metadata.drop_all(engine)
Model.metadata.create_all(engine)
session = Session()
user1 = User(name="xvl", uid="24238285", key="C3D680D6B15EEE17BCB2BF24419A8F82", # key="luYFaqqDDeoqDtUA--cmcMVEIVj7zWqUAfSBKnhFN6mMHNPOMUFaT9Ss-Xc-D-cPcHebCpPHgkGFMHLZXlNlSw__", "YgpRlQNyqI5dOn9A-YKYTTvVNXTMPvvQjH7Q8wNnUbx8cmWdGTB77a0UPXfx22vQDK7KA4TC9-ikajn2qtOFgw__"
idfa="37B9B63C-FCF7-4D47-854E-FD17D606E300",
openid="oFfv-s7eYZ8n7Z6buxKiMhql7ehE")
# user2 = User(name="yyc", uid="24805851", key="F84630346706B10753B0E8E6EED2DC28",
# idfa="A5AB7442-38D1-40D5-989C-D601AF6EB4D0",
# openid="oFfv-s9Xqqr1-szHZU9eIaVBZzy8")
# user3 = User(name="linfun", uid="24238341", key="E3F3255F24B9B071C7F98E880BDC0382",
# idfa="D7641C67-53D9-4302-B0E6-338795AFB917",
# openid="oFfv-s3m_ecL40zdVYzOND5_lG-I")
# user4 = User(name="ny", uid="24806519", key="9B111476D11387EE32C076022189FD41",
# idfa="99F4D3A2-3D96-4F53-B1E2-3179778308DD",
# openid="oFfv-s7lJfM2r1H1UtKNBwNm3RGw")
# user5 = User(name="apache", uid="24100028", key="20CACDC7AC201F2DB7D1201984F3BBAA",
# idfa="A3AF8EC0-974C-415E-8F62-03196B59A923",
# openid="oFfv-s2cYU9GaX1Kz98ki5RoRsAk")
# user6 = User(name="czd", uid="24839995", key="23A5793304CE1B63A222254C98738FF8",
# idfa="AC07D175-D624-4872-9071-4DA7B3695594",
# openid="oFfv-s8f5RhGss5m1Z2FsDgNCXIg")
# user7 = User(name="lft", uid="24260077", key="7DCF1968B8BE79447536C4A015AFD8E5",
# idfa="D221E8CF-BBA2-449D-8A19-876C27146421",
# openid="oFfv-sxR2n63-mXBVtFDa9MRBDiI")
# user2 = User(name="tony", uid="25784878", key="Qnfk9CXuzUaH69JE7mlkp-c9QvnLekmbjE7PfqfzDXAk7fEI82o3FDMLKJKicfNl*d5k8tV0BKTsodgby9Mkkg__",
# idfa="85438CFA-8658-4EC9-95A5-8D83029485C2",
# openid="oFfv-s7cJiU2lHWsDhpdMNQMXP8o")
session.add(user1)
# session.add(user2)
# session.add(user3)
# session.add(user4)
# session.add(user5)
# session.add(user6)
# session.add(user7)
session.commit()
def syncEpisode(self, serie, season, episode):
qry = Session.query(Season).filter_by(path=season).join(Serie).filter_by(path=serie)
if qry.count() == 0:
raise Exception("No season linked to %s/%s/%s" % (serie, saison, episode))
season = qry.one()
path = episode
ext = os.path.splitext(episode)[1][1:].lower()
if ext not in ["avi", "mkv", "mov", "mpg"]:
raise Exception("Not supported extention %s" % ext)
num = getEpisodeNumFromFilename(os.path.basename(episode))
episode = filter(lambda episode: episode.num == num, season.episodes)
if len(episode) == 0:
episode = Episode(num=num, path=path)
Session.add(episode)
season.episodes.append(episode)
else:
assert len(episode) == 1
episode = episode[0]
episode.num = num
episode.path = path
def make_account(username, password):
session = Session()
a = Account(username, password)
session.add(a)
session.commit()
def add_data(sensor, data):
s = Session()
s.add(Data(sensor, data))
s.commit()
