def getData():
db.connect()
db.create_tables([ Einrichtung ])
url = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vTbxaxdxBH9aYfQgsi7diiIJ-OBdIWo63BXlXOkv-hZiw_0H8cr2Ko0VDUAaH0TdpHOKWYz1pv9uloJ/pub?gid=0&single=true&output=csv'
r = requests.get(url, allow_redirects=True)
r.encoding = 'utf-8'
reader = csv.DictReader(StringIO(r.text), delimiter=',')
for row in reader:
if len(row.get('Name')) == 0 or len(row.get('Longitude')) == 0:
continue
einrichtung = Einrichtung()
einrichtung.id = str(uuid.uuid4())
einrichtung.name = row.get('Name')
einrichtung.adresse = row.get('Adresse')
einrichtung.plz = row.get('PLZ')
einrichtung.ort = row.get('Ort')
einrichtung.laengengrad = row.get('Longitude')
einrichtung.breitengrad = row.get('Latitude')
einrichtung.website = row.get('Website')
einrichtung.telefon = row.get('Telefon')
einrichtung.vollblutspende = (row.get('Vollblutspende') == 'ja')
einrichtung.thrombozytenspende = (row.get('Thrombozytenspende') == 'ja')
einrichtung.plasmaspende = (row.get('Plasmaspende') == 'ja')
einrichtung.erythrozytenspende = (row.get('Erythrozyten') == 'ja')
einrichtung.save(force_insert=True)
def put_user():
if not request.json:
abort_msg(400, 'Expected JSON parameters')
if 'password' in request.json:
passwd = request.json['password']
if not passwd:
passwd = utils.generatePassword(20)
_db = db.connect(settings.settings())
db.update(_db,
'users',
'pass="******"' % (passwd),
where='name="%s"' % auth.username())
data = {'user': auth.username(), 'password': passwd}
return jsonify(data)
if 'api_key' in request.json:
apikey = utils.generateApiKey()
_db = db.connect(settings.settings())
db.update(_db,
'users',
'apikey="%s"' % (apikey),
where='name="%s"' % auth.username())
data = {'user': auth.username(), 'api_key': apikey}
return jsonify(data)
abort_msg(400, 'Expected "password" or "api_key"')
def drop_tables():
db.connect(reuse_if_open=True)
db.drop_tables([LeagueAverageShootingPct])
db.drop_tables([PlayerSeason, Roster, Shot])
db.drop_tables([Player])
db.drop_tables([Team, Franchise])
db.drop_tables([Season])
def create_tables():
db.connect(reuse_if_open=True)
db.create_tables([
Season,
Franchise,
Player,
Team,
PlayerSeason,
Roster,
Shot,
LeagueAverageShootingPct,
])
def machine_del(machine_id):
user = auth.username()
_db = db.connect(settings.settings())
if user == 'admin':
res = db.select(_db, 'machines', where='id=\'%s\'' % machine_id)
else:
res = db.select(_db,
'machines',
where='id=\'%s\' AND owner=\'%s\'' %
(machine_id, user))
if not res:
abort_msg(400, 'Machine "%s" not found' % (machine_id))
res = res[0]
inf = infra.provider(settings.settings())
dom = inf.getDomain(res[0])
if not dom or dom is None:
abort(400)
if dom.isActive():
dom.destroy()
flags = 0
if dom.hasManagedSaveImage():
flags |= libvirt.VIR_DOMAIN_UNDEFINE_MANAGED_SAVE
if dom.snapshotNum() > 0:
flags |= VIR_DOMAIN_UNDEFINE_SNAPSHOTS_METADATA
vol_provider = images.volume_provider(settings.settings())
vols = inf.deviceItems(dom, 'disk')
error_msg = ''
try:
dom.undefineFlags(flags)
except:
error_msg = 'ERROR: Undefining domain: %s' % macine_id
print(error_msg)
error_msg += vol_provider.removeVolumes(vols)
_db = db.connect(settings.settings())
db.delete(_db, 'machines', where='id=\'%s\'' % machine_id)
data = {'removed': machine_id}
if error_msg:
data['error'] = error_msg
return jsonify(data)
def run(self):
db.connect()
camps = {
"username": "******",
"password": "******",
}
data = dict()
for camp in camps.keys():
data[camp] = input(camps[camp])
create_user(**data)
print("Usuário criado com sucesso!")
def search(self):
self.results.clear()
self.results.add_item('No items found!')
string = self.search_bar.get()
if string == None:
return
tokens = tokenize(string)
if len(tokens) <= 0:
return
db = connect()
indexes = None
for token in tokens:
values = db.postings['publishers_word'].get_values(token)
if len(values) > 0:
indexes = list(intersect(indexes, values))
if indexes == None or len(indexes) <= 0:
return
self.results.clear()
for idx in indexes:
publisher = db.tables['publishers'].load(idx)
item = ListItem(publisher, publisher['name'])
self.results.add_item(item)
self.ui.move_focus(self.results)
def post_user():
if auth.username() != 'admin':
abort_msg(400, 'Access denied, admin needed!')
if not request.json:
abort_msg(400, 'Expected JSON parameters')
if 'user' not in request.json:
abort_msg(400, 'Value for "user" not defined')
user = request.json['user']
passwd = ''
if 'password' in request.json:
passwd = request.json['password']
if user == 'admin':
abort_msg(400, 'Can\'t create admin')
_db = db.connect(settings.settings())
res = db.select(_db, 'users', where='name=\'%s\'' % user)
if res:
abort_msg(400, 'User \"%s\" already exists' % user)
if not passwd:
passwd = utils.generatePassword(20)
db.insert(_db, 'users', [user, passwd, utils.generateApiKey()])
data = {'user': request.json['user'], 'password': passwd}
return jsonify(data)
def rethink_connect():
return connect(
ReDB_HOST,
ReDB_PORT,
user=ReDB_USER,
password=ReDB_PASS
)
def get(self):
conn = db.connect()
return conn.execute("select 5")
return {
'categories':
list(map(lambda x: x.json(), CategoryModel.query.all()))
}
def __init__(self, ui: py_cui.PyCUI, publisher):
self.ui = ui
self.root = ui.create_new_widget_set(3, 3)
self.publisher = publisher
self.id = publisher['id']
self.info = self.root.add_text_block(
'Publisher 🕮',
0,
0,
initial_text=publisher['description'],
column_span=3)
self.games = self.root.add_scroll_menu('Games 🎲',
1,
0,
row_span=2,
column_span=3)
self.games.add_key_command(py_cui.keys.KEY_ENTER, self.select_game)
db = connect()
games = []
for game_id, _ in db.get_by_posting('game_publisher', 'publisher',
self.id):
game = db.get_by_key('games', game_id)
games.append((game, game_rating(game, db)))
games = sorted(games, key=lambda x: x[1], reverse=True)
for game, rating in games:
item = ListItem(game, f"({rating:.1f}) - {game['name']}")
self.games.add_item(item)
def test_successful_connection():
connection = connect(ReDB_HOST,
ReDB_PORT,
ReDB_DEFAULT_DB,
user=ReDB_USER,
password=ReDB_PASS)
assert connection is not None
def _insert_skill(self, skill: str, count: int) -> None:
# print(f'skill: {skill} | count: {count}')
ins = HeadHunterTable.insert().values(word=self.search_word,
skill=skill,
count=count)
conn = db.connect()
result = conn.execute(ins)
def createMachine(self, res):
extras = []
extra = ''
base = ''
inf = infra.provider(settings.settings())
volume = self.get_volume_from_image(res['image'], utils.generateID() + '_', resize=res['size'])
if volume:
base = os.path.basename(res['image'])
extras.append(inf.fileStorage(volume))
cdrom = self.get_cdrom_image(res['cdrom'])
if cdrom:
if not base:
base = os.path.basename(cdrom)
extras.append(inf.cdromStorage(cdrom))
image_extra_loader = None
if volume or cdrom:
item = cdrom
if volume:
item = volume
image_extra_loader = self.image_extra_config(os.path.basename(item), res['name'])
image_extra_userdata = {}
if image_extra_loader is not None:
print ('Found image loader: %s' % (image_extra_loader.base()))
extra_device = image_extra_loader.extraDeviceConfig(inf)
if extra_device:
extras.append(extra_device)
image_extra = image_extra_loader.extra()
if image_extra:
extra += image_extra
image_extra_userdata = image_extra_loader.userdata()
# TODO: Support other features
if 'network_name' in settings.settings():
extras.append(inf.defineNetworkInterface(settings.settings()['network_name']))
extradevices = '\n'.join(extras)
dom_xml = inf.customDomain(res['name'], res['cpus'], res['memory'], extradevices=extradevices, extra=extra)
dom = inf.createDomain(dom_xml)
dom_res = dom.create()
_db = db.connect(settings.settings())
config_data = json.dumps(image_extra_userdata)
config_data = config_data.replace('\'', '\\\'')
db.update(_db, 'machines',
'config=\'%s\'' % (config_data),
where='id="%s"' % (res['name']))
def before_request():
setup.create_db()
_db = db.connect(settings.settings())
res = db.select(_db, 'users', ['pass'], 'name=\'%s\'' % ('admin'))
if res is None or not res:
admin_pass = utils.generatePassword(20)
db.insert(_db, 'users', ['admin', admin_pass, utils.generateApiKey()])
print('Password for admin: "%s", keep this in safe place!\n' %
(admin_pass))
def configure_database():
connection = connect(ReDB_HOST,
ReDB_PORT,
user=ReDB_USER,
password=ReDB_PASS)
create_table(database, empty_table, connection)
delete_all(database, table, connection)
data = [{
'name':
'The Dark Knight Rises',
'year':
2012,
'runtime':
163,
'categories': ['adventure', 'thriller'],
'release-date':
'2012-07-20',
'director':
'Christopher Nolan',
'writer':
['Jonathan Nolan', 'David S. Goyer', 'Christopher Nolan', 'Bob Kane'],
'actors': ['Christian Bale', 'Tom Hardy', 'Anne Hathway'],
'storyline':
'Eight years after the Joker\'s reign of anarchy,'
' the Dark Knight, with the help of the enigmatic'
' Catwoman, is forced from his imposed exile to save'
' Gotham City, now on the edge of total annihilation,'
' from the brutal guerrilla terrorist Bane.'
}, {
'name':
'Avengers: Infinity War',
'year':
2018,
'runtime':
149,
'categories': ['Action', 'Adventure', 'Fantasy'],
'release-date':
'2018-04-27',
'director': ['Anthony Russo', 'Joe Russo'],
'writer': ['Christopher Markus', 'Stephen McFeely'],
'actors': [
'Robert Downey Jr.', 'Chris Hemsworth', 'Mark Ruffalo',
'Chris Evans', 'Scarlett Johansson', 'Benedict Cumberbatch'
],
'storyline':
'The Avengers and their allies must be willing to'
' sacrifice all in an attempt to defeat the'
' powerful Thanos before his blitz of devastation'
' and ruin puts an end to the universe.'
}]
result = insert(database, table, data, connection)
global unique_id
unique_id = result['generated_keys'][0]
def text_handler(message):
if message.text == 'OK':
html = ''
with db.connect() as conn:
stmt = text_table.select().where(
text_table.c.user == message.from_user.id)
result_set = conn.execute(stmt)
for i in result_set:
html = i[2]
stmt = text_table.update(
).where(text_table.c.user == message.from_user.id).values(
text=
'<!doctype html> <head> <style> body { font-size: 20pt; } </style> <meta charset="utf-8"> </head> <body>'
)
conn.execute(stmt)
pdfkit.from_string(html, 'pdfs/' + str(message.from_user.id) + '.pdf')
with codecs.open('pdfs/' + str(message.from_user.id) + '.pdf') as f:
bot.send_document(message.from_user.id, f)
markup = telebot.types.ReplyKeyboardRemove()
bot.send_message(message.from_user.id,
"Your PDF file:",
reply_markup=markup)
for filename in os.listdir('images'):
name = filename.split('_')[0]
if name == str(message.from_user.id):
os.remove('images/' + filename)
os.remove('pdfs/' + str(message.from_user.id) + '.pdf')
elif message.text != "OK":
if message.text[0] == '#':
text = '<h1>' + message.text[1:] + '</h1>'
else:
text = '<p>' + message.text + '</p>'
with db.connect() as conn:
stmt = text_table.update().where(
text_table.c.user == message.from_user.id).values(
text=text_table.c.text + text)
conn.execute(stmt)
markup = telebot.types.ReplyKeyboardMarkup(True, False)
markup.row('OK')
bot.send_message(message.from_user.id,
'Text has been added.',
reply_markup=markup)
async def main():
db.init(os.getenv("DB"))
db.connect()
db.create_tables([User, Raid, Log, PriorActivity])
async with Session() as kol:
await kol.login(os.getenv("KOL_USER"), os.getenv("KOL_PASS"))
cache = pickle.load(open("oaf.cache", "rb"))
bot = commands.Bot("!")
bot.kol = kol
bot.raid_guild = int(os.getenv("DISCORD_GUILD"))
bot.raid_channel = int(os.getenv("DISCORD_CHANNEL"))
bot.add_cog(Verification(bot))
bot.add_cog(RaidLogs(bot, clans))
bot.add_cog(Whitelist(bot, clans))
bot.add_cog(OAF(bot, cache))
bot.add_cog(Stop(bot))
await bot.login(os.getenv("DISCORD_TOKEN"))
await bot.connect()
pickle.dump(cache, open("oaf.cache", "wb"))
def user():
data = {'user': auth.username()}
_db = db.connect(settings.settings())
res = db.select(_db,
'users',
items=['apikey'],
where='name=\'%s\'' % auth.username())
if res:
res = res[0]
data['api_key'] = res[0]
return jsonify(data)
def configure_database(host="localhost",
port=28015,
db=None,
user=ReDB_USER,
password=ReDB_PASS):
connection = connect(host, port, db, user, password)
if db:
connection.use(db)
if connection:
return connection
else:
return False
def search(self):
db = connect()
game_search = list(tokenize(self.search_box.get()))
game_ids = None
mechanics_ids = None
categories_ids = None
if len(game_search) > 0:
ids = []
for token in game_search:
# Append array of ids
ids.append(
list(
map(lambda g: g['id'],
db.get_by_posting('games', 'word', token))))
game_ids = list(reduce(reduce_intersection, ids))
if len(self.mechanics) > 0:
ids = []
for mechanic_item in self.mechanics:
mechanic_id = mechanic_item.value
ids.append(
list(
map(
lambda tuple: tuple[0],
db.get_by_posting('game_mechanic', 'mechanic',
mechanic_id))))
mechanics_ids = list(reduce(reduce_intersection, ids))
if len(self.categories) > 0:
ids = []
for category_item in self.categories:
category_id = category_item.value
ids.append(
list(
map(
lambda tuple: tuple[0],
db.get_by_posting('game_category', 'category',
category_id))))
categories_ids = list(reduce(reduce_intersection, ids))
results = list(
intersect(intersect(game_ids, mechanics_ids), categories_ids)
or [])
self.present_results(results)
def machine():
_db = db.connect(settings.settings())
user = auth.username()
if user == 'admin':
res = db.select(_db, 'machines')
else:
res = db.select(_db, 'machines', where='owner=\'%s\'' % user)
items = [x[0] for x in res]
return jsonify({
'machines': [{
'uri': url_for('machine_id', machine_id=x, _external=True),
'id': x
} for x in items]
})
def put_machine(machine_id):
if not request.json:
abort_msg(400, 'Expected JSON input')
user = auth.username()
_db = db.connect(settings.settings())
if user == 'admin':
res = db.select(_db, 'machines', where='id=\'%s\'' % machine_id)
else:
res = db.select(_db,
'machines',
where='id=\'%s\' AND owner=\'%s\'' %
(machine_id, user))
if not res:
abort_msg(400, 'Machine "%s" not found' % (machine_id))
res = res[0]
inf = infra.provider(settings.settings())
dom = inf.getDomain(res[0])
if not dom or dom is None:
abort_msg(400, 'Machine "%s" not in backedn' % (machine_id))
if 'operation' not in request.json:
abort_msg(400, 'No operation defined')
data = {}
if request.json['operation'] == 'reboot':
data['result'] = dom.reboot()
elif request.json['operation'] == 'reset':
data['result'] = dom.reset()
elif request.json['operation'] == 'shutdown':
data['result'] = dom.shutdown()
elif request.json['operation'] == 'start':
st = dom.state()[0]
if st in [0, 4, 5, 6]:
data['result'] = dom.create()
else:
abort_msg(400,
'Can\'t start, invalid state: %s' % (inf.domState(dom)))
elif request.json['operation'] == 'forceoff':
data['result'] = dom.destroy()
elif request.json['operation'] == 'suspend':
data['result'] = dom.suspend()
elif request.json['operation'] == 'resume':
data['result'] = dom.resume()
else:
abort_msg(400, 'Unknown operation: %s' % (request.json['operation']))
data[request.json['operation']] = machine_id
return jsonify(data)
def setup_admin(show_passwd=False):
_db = db.connect(settings.settings())
res = db.select(_db, 'users', ['pass'], 'name=\'%s\'' % ('admin'))
if res is None or not res:
admin_pass = utils.generatePassword(20)
db.insert(_db, 'users',
['admin',
admin_pass,
utils.generateApiKey()])
print ('Password for admin: "%s", keep this in safe place!' % (admin_pass))
elif show_passwd:
print ('Admin password: %s' % (res[0]))
else:
print ('Admin already set up')
def load():
try:
# create connection to sqlite db file
conn = db.connect()
# load feature data from sqlite
df = pd.read_sql_table(
table_name="features", # table name
con=conn)
return df
except Exception as e:
raise e
finally:
conn.close()
def configure_database(
host='localhost',
port=28015,
db=None,
user='******',
password=''
):
connection = connect(host, port, db, user, password)
if db:
connection.use(db)
if connection:
return connection
else:
return False
def search_mechanics(self):
db = connect()
self.mechanics_result.clear()
ids = []
for token in tokenize(self.mechanics_search.get()):
for mechanic in db.get_by_posting('mechanics', 'word', token):
if mechanic['id'] not in ids:
ids.append(mechanic['id'])
item = ListItem(mechanic['id'], mechanic['name'])
self.mechanics_result.add_item(item)
if is_in(self.mechanics, item):
self.mechanics_result.mark_item_as_checked(item)
if len(self.mechanics_result.get_item_list()) > 0:
self.ui.move_focus(self.mechanics_result)
def search_categories(self):
db = connect()
self.categories_result.clear()
ids = []
for token in tokenize(self.categories_search.get()):
for category in db.get_by_posting('categories', 'word', token):
if category['id'] not in ids:
ids.append(category['id'])
item = ListItem(category['id'], category['name'])
self.categories_result.add_item(item)
if is_in(self.categories, item):
self.categories_result.mark_item_as_checked(item)
if len(self.categories_result.get_item_list()) > 0:
self.ui.move_focus(self.categories_result)
def post_machine():
if not request.json:
abort_msg(400, 'Expected JSON input')
# TODO: quota per user
res = {
'memory': 256 * 1024, # FIXME some reasonable default
'cpus': 1,
'name': utils.generateID(),
'net': '',
'image': '',
'size': '',
'cdrom': '',
}
if 'mem' in request.json:
res['memory'] = request.json['mem']
if 'memory' in request.json:
res['memory'] = request.json['memory']
if 'size' in request.json:
res['size'] = request.json['size']
if 'cpus' in request.json:
try:
res['cpus'] = int(request.json['cpus'])
except:
pass
if 'image' in request.json:
res['image'] = request.json['image']
if 'cdrom' in request.json:
res['cdrom'] = request.json['cdrom']
if 'name' in request.json:
res['name'] = request.json['name']
_db = db.connect(settings.settings())
db.insert(_db, 'machines', [res['name'], '', '', auth.username(), ''])
act = actions.provider(settings.settings())
act.sendActionNow({'createMachine': res}, None)
data = {
'uri': url_for('machine_id', machine_id=res['name'], _external=True),
'id': res['name']
}
return jsonify(data)
def import_stock_data():
conn = db.connect(name="proj1part2",
usr="******",
host="35.243.220.243",
pwd="2262")
if conn is None:
exit(1)
else:
cur = conn.cursor()
cur.execute("DELETE FROM stocks;")
cur.execute("DELETE FROM stock_history;")
import_stock(cur)
conn.commit()
import_stock_history(cur)
conn.commit()
def get_weathers():
with requests.Session() as s:
s.mount('http://v.juhe.cn/weather', HTTPAdapter(max_retries=2))
r = s.get('http://v.juhe.cn/weather/citys',
params={'key': config['APP_KEY']},
timeout=config['REQUEST_TIMEOUT'])
data = r.json()
if 'result' not in data or data['resultcode'] != '200':
logger.error(u'无法得到区域列表，返回内容:{}'.format(r.text))
return
districts = [row.get('district') for row in data['result']
if row.get('city') in config['REQUEST_CITIES']]
resps = [s.get('http://v.juhe.cn/weather/index',
params={'cityname': district, 'key': config['APP_KEY'], 'format': 1},
timeout=config['REQUEST_TIMEOUT'])
for district in districts]
with db.connect() as connection:
for resp, district in zip(resps, districts):
data = resp.json()
if 'result' not in data or data['resultcode'] != '200':
logger.error(u'无法得到{}地区的天气信息，返回内容:{}'.format(district, resp.text))
continue
actuality = data['result']['sk']
stmt = actualities.insert().values(
district=district,
temp=decimal.Decimal(actuality['temp']),
wind_direction=actuality['wind_direction'],
wind_strength=actuality['wind_strength'],
humidity=decimal.Decimal(actuality['humidity'].strip('%')) / 100,
time=strptime(actuality['time'], ACTUAL_TIME_FORMAT).time()
)
connection.execute(stmt)
future_data = data['result']['future']
if isinstance(future_data, dict):
future_data = future_data.values()
for future in future_data:
date = strptime(future['date'], FUTURE_DATE_FORMAT).date()
values = {
'district': district,
'temperature': future['temperature'],
'weather': future['weather'],
'weather_id_fa': future['weather_id']['fa'],
'weather_id_fb': future['weather_id']['fb'],
'wind': future['wind'],
'date': date
}
restrict = lambda query: query \
.where(futures.c.date == date) \
.where(futures.c.district == district)
stmt = restrict(futures.select())
if connection.execute(stmt).fetchone():
stmt = restrict(futures.update().values(**values))
else:
stmt = futures.insert().values(**values)
connection.execute(stmt)
