def init(grouped, dbtype):
hp.askfor_update()
if os.path.exists(".report"):
hp.instruct_continue(
"Já existe um projeto em andamento nessa pasta. Deseja reiniciá-lo? Isso implicará a perda de tudo que foi feito até agora."
)
errors = hp.check_folder_structure()
if errors:
print(
"\nSua estrutura de pastas parece não atender aos critérios necessários. Leia o tutorial no SINFWeb."
)
print("\nPossíveis erros encontrados: ")
# print("\nPossíveis erros encontrados:")
for error in errors:
cprint(f" -> {error}", "yellow")
# print(error)
print("\n")
hp.show_options_cancel(
"O que deseja fazer?",
['Continuar mesmo assim. Os erros detectados são falsos.'],
cancel_option=True)
# hp.instruct_continue("")
hp.reset(dbtype=dbtype)
sleep(1)
os.mkdir('.report')
os.system("attrib +h " + ".report")
shutil.copytree(settings.app_dir / "scripts", ".report\\scripts")
hp.set_working_dir_scripts()
shutil.copytree(settings.app_dir / "notebooks", ".report\\notebooks")
shutil.copy(settings.app_dir / "reader/static/image/desconhecido.png",
".report\\desconhecido.png")
hp.set_working_dir_notebooks()
shutil.copytree(settings.app_dir / "config_files/config",
Path(".report/config"))
config_manager.set_grouped(grouped)
from database import init_db
print(f"Gerando banco de dados {dbtype}")
if dbtype != 'sqlite':
hp_db.create_database_localdb(type=dbtype)
hp_db.drop_orphan_databases(type=dbtype,
exclude=[config_manager.database_name])
n_cpu = multiprocessing.cpu_count()
config_manager.data['n_workers'] = n_cpu if n_cpu <= 8 else 8
config_manager.save()
config_manager.load_database_name()
db_connect()
importlib.reload(models)
init_db()
hp.copy_config_files(overwrite=True)
shutil.copy(settings.app_dir / "go/starter_normal.exe",
constants.ANALYZER_EXE_NAME)
print(
"\nAmbiente preparado. Antes de processar não se esqueça de editar os arquivos \"config_source.yaml\" que se encontram dentro de cada pasta de fonte de dados."
)
def get_order(orderNumber):
if 'id' not in session:
abort(403, '未登入系統')
else:
json_data = {}
if orderNumber == '':
conn, mycursor = db_connect(db)
mycursor.execute('SELECT * FROM orders WHERE user_id=%s',
(session["id"], ))
get_all = mycursor.fetchall()
db_close(conn, mycursor)
# print(get_all[0])
print(get_all)
if get_all == []:
json_data['data'] = []
else:
order_list = []
for order in get_all:
data = {}
print('error', order)
trip = json.loads(order[2])
data['number'] = order[0]
data['price'] = trip['price']
data['trip'] = trip['trip']['attraction']['name']
data['date'] = trip['trip']['date']
data['status'] = order[3]
order_list.append(data)
order_list.reverse()
json_data['data'] = order_list
# print(json_data)
else:
conn, mycursor = db_connect(db)
mycursor.execute(
'SELECT number, trip_order, status FROM orders WHERE user_id=%s AND number=%s',
(session["id"], orderNumber))
get_one = mycursor.fetchone()
db_close(conn, mycursor)
if get_one == None:
json_data['data'] = None
else:
order = json.loads(get_one[1])
data = {}
data['number'] = get_one[0]
data['price'] = order['price']
data['trip'] = order['trip']
data['contact'] = order['contact']
data['status'] = get_one[2]
json_data['data'] = data
return jsonify(json_data), 200
def train_svm(iters=10):
names = {}
connection = db.db_connect()
with open('svm_model.pickle', 'rb') as f:
svm = pickle.load(f)
data = db.db_getData(connection)
get_names = db.get_names(connection)
for n in get_names:
names[n] = get_names.index(n)
train_x = []
train_y = []
for d in data:
b = np.array([float(x) for x in d[1][3:-2].split(' ') if x != ''])
train_x.append(b)
train_y.append(names[str(d[0])])
i = 1
while (i <= iters):
print('Done with ({}/{})'.format(i, iters))
svm.fit(train_x, train_y)
i += 1
with open('svm_model.pickle', 'wb') as f:
pickle.dump(svm, f)
print("Done Saving")
connection.close()
def create_recipe(recipe_id, recipe_name):
db = database.db_connect()
c = db.cursor()
qry = "INSERT INTO Recipe (ID, Name) VALUES ("+database.INSERT_SYM+", "+database.INSERT_SYM+")"
c.execute(qry, (recipe_id, recipe_name))
db.commit()
return True
def get(self, c_id):
# db
db = database.db_connect() #connection객체
sql = 'select u.id, title from getitem as gi join user as u on gi.u_id = u.u_id where c_id = \'{}\''.format(
c_id)
curs = db.cursor() #cursor()메소드 호출 - fetch동작 관리
curs.execute(sql) #sql문장을 DB서버에 보냄
rows = curs.fetchall() #데이터를 서버로부터 가져온 후, fetch된 데이터 사용
curs.close()
db.close()
result = {
'status': 200,
'c_id': c_id,
'item_count': len(rows),
'items': list()
}
#u_id말고 id로
for row in rows:
temp = {'id': row[0], 'title': row[1]}
result['items'].append(temp)
return result, 200
def create_plot_for_statistic(currency, from_date, to_date, bot, chat_id):
'''
создает график для валюты currency, начиная с даты from_date, заканчивая
датой to_date. охраянет картинку и отправляет запросившему
'''
con = database.db_connect(config.db_name)
cur = con.cursor()
query = 'SELECT date, price FROM prices WHERE currency_code = "{}" AND date >= "{}" ' \
'AND date <= "{}"'.format(currency.upper(), from_date, to_date)
cur.execute(query)
text = cur.fetchall()
date_list = []
price_list = []
for a in text:
date_list.append(a[0])
price_list.append(a[1])
plt.plot(date_list, price_list)
plt.title('Курс {} с {} по {}'.format(currency, from_date, to_date))
plt.xlabel('дата', fontsize=15)
plt.ylabel('цена в рублях', fontsize=15)
n = len(date_list)
labels = []
for i in range(10):
labels.append(i * n / 10)
labels.append(n - 1)
plt.xticks(labels, rotation=50)
path = 'img/' + str(config.my_chat_id) + 'statistic_plot.png'
plt.tight_layout()
plt.savefig(path, format='png', dpi=100)
plt.close()
with open(path, 'rb') as plot:
bot.send_photo(chat_id, plot)
def add_item_type(itemname, attribute, value, slotname, description):
db = database.db_connect()
c = db.cursor()
itemId = 1
itemTypeId = 1
attribId = 1
slotId = 1
qry = "INSERT INTO Item_Type VALUES (" + database.INSERT_SYM + "," + database.INSERT_SYM + ")"
c.execute(qry, (itemTypeId, itemname))
itemid = c.lastrowid
qry = "INSERT INTO Item VALUES ("+ database.INSERT_SYM + "," + database.INSERT_SYM + "," + database.INSERT_SYM +"," + database.INSERT_SYM + ")"
c.execute(qry, (itemId, itemid, itemname, description))
qry = "INSERT INTO Attribute VALUES (" + database.INSERT_SYM + "," + database.INSERT_SYM + ")"
c.execute(qry, (attribId, attribute))
attrid = c.lastrowid
qry = "INSERT INTO Item_Attributes VALUES ("+database.INSERT_SYM +","+database.INSERT_SYM+","+database.INSERT_SYM+")"
c.execute(qry, (itemid, attrid, value))
qry = "INSERT INTO Slot VALUES ("+database.INSERT_SYM + "," + database.INSERT_SYM+")"
c.execute(qry, (slotId, slotname))
slotid = c.lastrowid
qry = "INSERT INTO Item_Slot VALUES (" +database.INSERT_SYM +","+database.INSERT_SYM + ")"
c.execute(qry, (itemTypeId, slotId))
db.commit()
return (itemid, attrid, slotid)
def run(self):
try:
self.engine, self.dbsession = db_connect()
print("Renderizando audios gerais")
query = self.dbsession.query(File).filter(File.type_ == 'audio')
query = self.report_bundle.filter(File, query)
n_pages = get_page(
query,
only_count=True,
per_page=config_manager.report_config['per_page']['audio'])
for i in range(n_pages):
page = i + 1
pagination = get_page(
query,
page=page,
per_page=config_manager.report_config['per_page']['audio'])
# Adiciona informação nos anexos se o chat foi incluido ou não
add_chat_included(pagination['items'])
context = {'pagination': pagination}
dest_file = os.path.join(self.report_bundle.report_folder,
'html_files',
f"general-audios_page_{page}.html")
self.renderizer.render_template('general-audios.html',
dest_file, context)
finally:
self.engine.dispose()
def reset_achievement_completed():
db = database.db_connect()
c = db.cursor()
c.execute("CREATE TABLE IF NOT EXISTS Achievement_Completed ( Player_ID INTEGER NOT NULL, Achievement_ID INTEGER NOT NULL, FOREIGN KEY (Player_ID) REFERENCES Player (ID), FOREIGN KEY (Achievement_ID) REFERENCES Achievement (ID) )")
c.execute("DELETE FROM Achievement_Completed")
db.commit()
def reset_achievements():
db = database.db_connect()
c = db.cursor()
c.execute("DROP TABLE Achievement")
c.execute("CREATE TABLE IF NOT EXISTS Achievement (ID INTEGER PRIMARY KEY AUTOINCREMENT, Name CHAR (64), Descr CHAR (255))")
c.execute("DELETE FROM Achievement")
db.commit()
def get_character(id):
db = database.db_connect()
c = db.cursor()
qry = "SELECT * FROM `Character` WHERE ID=" + database.INSERT_SYM
c.execute(qry, (id,))
result = c.fetchone()
return result
def get_attraction_by_id(attractionid):
if attractionid == '' or attractionid == '0':
return redirect('/api/attraction/1')
try:
attractionid = int(attractionid)
except:
abort(400, '景點編號不正確')
else:
result = {}
select = f'{select_spot} where id ={attractionid}'
try:
conn, mycursor = db_connect(db)
mycursor.execute(select)
data = list(list(mycursor)[0])
except:
db_close(conn, mycursor)
abort(500, '伺服器錯誤')
else:
column_names = mycursor.column_names #tuple
db_close(conn, mycursor)
spot = spot_handle(data, column_names)
result['data'] = spot #data:{spot}
return jsonify(result), 200
def main():
rs.login()
conn = db.db_connect(SQLVars.DATABASE.value)
exists = db.check_if_table_exists(conn, SQLVars.TICKERS.value)
if not exists:
sql_create_tickers_table = """ CREATE TABLE IF NOT EXISTS existing_tickers (
ticker text,
first_dt text
) """
db.execute_query(conn, sql_create_tickers_table)
current_symbols = db.get_col_from_table(conn, TickersColumns.TICKER.value,
SQLVars.TICKERS.value)
current_symbols = [
i[getIndexOfEnum('ticker', TickersColumns)] for i in current_symbols
]
stocks_to_check = ['IPO' + chr(i + ord('a')).upper() for i in range(26)]
stocks_to_check_filtered = list(
set(stocks_to_check) - set(current_symbols))
curr_date = datetime.datetime.today().strftime('%Y-%m-%d')
new_tickers = []
for symbol in stocks_to_check_filtered:
quotes = rs.get_quote(symbol)
if quotes:
new_tickers.append((symbol, curr_date))
order_by_dollars(symbol, 1000)
db.insert_into_table(conn, SQLVars.TICKERS.value, new_tickers)
rs.logout()
def reset_item_attributes():
db = database.db_connect()
c = db.cursor()
c.execute("DROP TABLE IF EXISTS Item_Attributes")
c.execute("CREATE TABLE IF NOT EXISTS Item_Attributes ( Item_ID INT NOT NULL, Attribute_ID INT NOT NULL, Value INT, FOREIGN KEY (Item_ID) REFERENCES Item (ID), FOREIGN KEY (Attribute_ID) REFERENCES Attribute (ID), PRIMARY KEY (Item_ID, Attribute_ID) )")
c.execute("DELETE FROM Item_Attributes")
db.commit()
def reset_attributes():
db = database.db_connect()
c = db.cursor()
c.execute("DROP TABLE IF EXISTS Attribute")
c.execute("CREATE TABLE IF NOT EXISTS Attribute (ID INT NOT NULL PRIMARY KEY, Text CHAR (64))")
c.execute("DELETE FROM Attribute")
db.commit()
def reset_items():
db = database.db_connect()
c = db.cursor()
c.execute("DROP TABLE IF EXISTS Item")
c.execute("CREATE TABLE IF NOT EXISTS Item (ID INT NOT NULL PRIMARY KEY, Item_Type_ID INT NOT NULL, Name CHAR(64), Description CHAR(255), FOREIGN KEY (Item_Type_ID) REFERENCES Item_Type (ID))")
c.execute("DELETE FROM Item")
db.commit()
def create_recipe_out(recipe_id, item_id, quantity):
db = database.db_connect()
c = db.cursor()
qry = "INSERT INTO Recipe_Out (Recipe_ID, Item_ID, Quantity) VALUES ("+database.INSERT_SYM+", "+database.INSERT_SYM+", "+database.INSERT_SYM+")"
c.execute(qry, (recipe_id, item_id, quantity))
db.commit()
return True
def reset_item_slots():
db = database.db_connect()
c = db.cursor()
c.execute("DROP TABLE IF EXISTS Item_Slot")
c.execute("CREATE TABLE IF NOT EXISTS Item_Slot (Item_Type_ID INT NOT NULL, Slot_ID INT NOT NULL, PRIMARY KEY (Item_Type_ID, Slot_ID), FOREIGN KEY (Item_Type_ID) REFERENCES Item_Type (ID), FOREIGN KEY (Slot_ID) REFERENCES Slot (ID))")
c.execute("DELETE FROM Item_Slot")
db.commit()
def reset_item_types():
db = database.db_connect()
c = db.cursor()
c.execute("DROP TABLE IF EXISTS Item_Type")
c.execute("CREATE TABLE IF NOT EXISTS Item_Type (ID INT NOT NULL PRIMARY KEY, Name CHAR(64))")
c.execute("DELETE FROM Item_Type")
db.commit()
def reset_slots():
db = database.db_connect()
c = db.cursor()
c.execute("DROP Table IF EXISTS Slot")
c.execute("CREATE TABLE IF NOT EXISTS Slot (ID INT NOT NULL PRIMARY KEY, Name CHAR(64))")
c.execute("DELETE FROM Slot")
db.commit()
def worker(chunk):
engine, dbsession = db_connect()
for item in chunk:
attachment = dbsession.query(File).get(item[0])
file_ = dbsession.query(File).filter(
File.message_id == None,
File.extracted_path == attachment.extracted_path,
File.read_source_id == attachment.read_source_id).first()
if file_:
attachment.size = attachment.size or file_.size
attachment.filename = attachment.filename or file_.filename
attachment.original_path = attachment.original_path or file_.original_path
attachment.extracted_path = attachment.extracted_path or file_.extracted_path
attachment.content_type = attachment.content_type or file_.content_type
attachment.creation_time = attachment.creation_time or file_.creation_time
attachment.access_time = attachment.access_time or file_.access_time
attachment.sha256 = attachment.sha256 or file_.sha256
attachment.md5 = attachment.md5 or file_.md5
attachment.deleted_state = attachment.deleted_state or file_.deleted_state
attachment.type_ = attachment.type_ or file_.type_
attachment.page_renderized = attachment.page_renderized or file_.page_renderized
attachment.analise_thumb = attachment.analise_thumb or file_.analise_thumb
attachment.corrupted = attachment.corrupted or file_.corrupted
dbsession.add(attachment)
dbsession.delete(file_)
dbsession.commit()
engine.dispose()
def get(self, i_id):
result = {
'status': 200,
'i_id': i_id,
'image_count': 0,
'images': list()
}
db = database.db_connect()
sql = 'SELECT url FROM dbdbdp.lostimage WHERE i_id={};'.format(i_id)
curs = db.cursor()
curs.execute(sql)
rows = curs.fetchall()
curs.close()
result['image_count'] = len(rows)
for row in rows:
link = 'http://oditkhu.dasom.io/img/items/' + row[0]
result['images'].append(link)
# db close - 연결 닫아
db.close()
return result, 200
def get(self):
try:
req_parser = reqparse.RequestParser()
req_parser.add_argument('cnum', type=int)
req_parser.add_argument('dnum', type=int)
req_parser.add_argument('date', type=int)
req_args = req_parser.parse_args()
cnum = req_args['cnum']
dnum = req_args['dnum']
date = req_args['date']
session = db_connect('../db/money.db')()
if cnum:
ranking = session.query(Trade.eul_uid,
func.sum(Trade.reduce_price).label('sum'))\
.filter_by(completed=False)\
.group_by(Trade.eul_uid)\
.order_by(text('sum desc'))\
.limit(cnum)
return [{'uid': rank[0], 'sum': rank[1]} for rank in ranking]
elif dnum:
ranking = session.query(Trade.gab_uid,
func.sum(Trade.reduce_price).label('sum'))\
.filter_by(completed=False)\
.group_by(Trade.gab_uid)\
.order_by(text('sum desc'))\
.limit(dnum)
return [{'uid': rank[0], 'sum': rank[1]} for rank in ranking]
except Exception as e:
return {'error': str(e)}
def get(self, i_id):
result = {
'status': 200,
'i_id': i_id,
'comment_count': 0,
'comments': list()
}
db = database.db_connect()
sql = 'select u.id, u.name, allcomment.content from(select g_com.u_id, g_com.content from (getcomment as g_com join getitem as g_i on g_i.i_id = g_com.i_id) where g_i.i_id = {}) as allcomment join user as u on allcomment.u_id = u.u_id;'.format(
i_id)
curs = db.cursor()
curs.execute(sql)
rows = curs.fetchall()
curs.close()
result['comment_count'] = len(rows)
for row in rows:
temp = {'author_id': '', 'author_name': '', 'comment': ''}
temp['author_id'] = row[0]
temp['author_name'] = row[1]
temp['comment'] = row[2]
result['comments'].append(temp)
# db close - 연결 닫아
db.close()
return result, 200
def get_achievement_description(name):
db = database.db_connect()
c = db.cursor()
qry = "SELECT Achievement.Descr FROM Achievement WHERE Achievement.Name=" + database.INSERT_SYM
c.execute(qry, (name,))
result = c.fetchone()
return result
def get_missions():
db = database.db_connect()
c = db.cursor()
c.execute("SELECT M.ID, MT.Name As MissionType, Reward_ID FROM Mission M JOIN Mission_Type MT on M.Mission_Type_ID = MT.ID")
result = []
for row in c:
result.append(row)
return result
def save_properties(minTemp, weekdays):
con = db_connect()
cur = con.cursor()
for day in weekdays:
heating_start = ""
heating_end = ""
sql = "INSERT INTO DailyConfig (daynumber, heating_start, heating_end) VALUES (?,?)"
cur.execute(sql, (consts.DAYS[day["day"]], heating_start, heating_end))
def reset_recipe_out():
db = database.db_connect()
c = db.cursor()
c.execute("DELETE FROM Recipe_Out")
#c.executescript('''DROP TABLE IF EXISTS Recipe_Out''')
#c.execute('''CREATE TABLE Recipe_Out (Recipe_ID INT, Item_ID INT, Quantity INT, FOREIGN KEY (Recipe_ID) REFERENCES Recipe (ID), FOREIGN KEY (Item_ID) REFERENCES Item (ID))''')
db.commit()
db.close()
def reset_recipe():
db = database.db_connect()
c = db.cursor()
c.execute("Delete From Recipe")
#c.executescript('''DROP TABLE IF EXISTS Recipe''')
#c.execute('''CREATE TABLE Recipe (ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Name CHAR(255) NOT NULL)''')
db.commit()
db.close()
def get_all_characters():
db = database.db_connect()
c = db.cursor()
qry = "SELECT * FROM `Character`"
c.execute(qry)
result = []
for row in c:
result.append(row)
return result
def get_inventory(charid):
db = database.db_connect()
c = db.cursor()
qry = "SELECT Item.Name, Quantity FROM Inventory_Item JOIN Item ON Item_ID=Item.ID WHERE Character_ID="+database.INSERT_SYM
c.execute(qry, (charid,)) #result should contain Name of items and their quantity.
result = []
for row in c:
result.append(row)
return result
def get_equipment(charid):
db = database.db_connect()
c = db.cursor()
qry = "SELECT Item.Name, Slot.Name FROM Equipped_Item JOIN Slot ON Slot_ID=Slot.ID JOIN Item ON Item_ID=Item.ID WHERE Character_ID="+database.INSERT_SYM
c.execute(qry, (charid,)) #result should contain name and slot of items equipped by this character.
result = []
for row in c:
result.append(row)
return result
def print_missions():
db = database.db_connect()
c = db.cursor()
c.execute("SELECT * FROM Mission")
result = c.fetchone()
while (result != None):
print "- ", result, "\n"
result = c.fetchone()
print "----\n"
def main():
default_file_path = "todolist.db"
conn = database.db_connect(default_file_path)
root = tk.Tk()
app = ToDo(conn, master=root)
app.mainloop()
database.db_close(conn)
def get_all_achievements():
db = database.db_connect()
c = db.cursor()
qry = "SELECT Achievement.Name FROM Achievement"
c.execute(qry, ())
result = []
for row in c:
result.append(row[0])
return result
def get_recipe_in(recipe_id):
db = database.db_connect()
c = db.cursor()
qry = "SELECT Item.ID, Quantity FROM Recipe_In JOIN Item ON (Recipe_In.Item_ID = Item.ID) WHERE Recipe_ID="+database.INSERT_SYM
c.execute(qry, (recipe_id,))
result = []
for row in c:
result.append(row)
return result
def get_items():
db = database.db_connect()
c = db.cursor()
qry = '''SELECT Item.Name, Description, Item_Type.Name
FROM Item JOIN Item_Type ON (Item.Item_Type_ID=Item_Type.ID)'''
c.execute(qry)
result = []
for row in c:
result.append(row)
return result
def book():
json_data = {}
if 'id' in session:
if request.method == 'GET':
print('get booking')
if 'data' in session:
json_data['data'] = session['data']
else:
json_data['data'] = None
print(json_data)
elif request.method == 'POST':
print('post booking')
print(request.json)
if 'data' in session:
session.pop('data')
if request.json['date'] == '' or request.json['price'] == None:
abort(400, '建立失敗')
try:
conn, mycursor = db_connect(db)
mycursor.execute(
f'select id,name,address,images from spot where id like {request.json["attractionId"]}'
)
except:
db_close(conn, mycursor)
abort(500)
db_spot = mycursor.fetchone()
column_name = mycursor.column_names
db_close(conn, mycursor)
book_data = {}
attraction = {}
for column, info in zip(column_name, db_spot):
if column == 'images':
attraction['image'] = info.split()[0]
else:
attraction[column] = info
book_data['attraction'] = attraction
book_data['date'] = request.json['date']
book_data['time'] = request.json['time']
book_data['price'] = request.json['price']
session['data'] = book_data
json_data['ok'] = True
elif request.method == 'DELETE':
print('delete booking')
session.pop('data')
print(session)
json_data['ok'] = True
else:
abort(500)
else:
abort(403, '未登入系統')
return jsonify(json_data), 200
def get_quantity(charid, itemid):
db = database.db_connect()
c = db.cursor()
qry = "SELECT Quantity FROM Inventory_Item JOIN Item ON Item_ID=Item.ID WHERE Character_ID="+database.INSERT_SYM+" AND Item_ID="+database.INSERT_SYM
c.execute(qry, (charid,itemid,))
result = c.fetchone()
if result != None:
result = result[0]
else:
result = 0
return result
def get_player_id(username):
db = database.db_connect()
c = db.cursor()
qry = "SELECT Player.ID FROM Player WHERE Username="+database.INSERT_SYM
c.execute(qry, (username,))
result = c.fetchone()
if (result != None):
result = result[0]
else:
result = None
return result
def get_reward_exp(rewardID):
db = database.db_connect()
c = db.cursor()
qry = '''SELECT *
FROM Reward
WHERE ID ='''+database.INSERT_SYM;
c.execute(qry, (rewardID,))
result = []
for row in c:
result.append(row)
return result
def get_player_achievement_by_id(playerId):
db = database.db_connect()
c = db.cursor()
qry = "SELECT Achievement_Completed.Achievement_ID FROM Achievement_Completed WHERE Achievement_Completed.Player_ID=" + database.INSERT_SYM
c.execute(qry, (playerId,))
result = []
for row in c:
result.append(row)
return result
def add_item(charid, itemid, quantity):
db = database.db_connect()
c = db.cursor()
curr_quantity = get_quantity(charid, itemid)
if curr_quantity > 0:
quantity= curr_quantity + quantity
qry = "UPDATE Inventory_Item SET Quantity="+database.INSERT_SYM+" WHERE Item_ID="+database.INSERT_SYM+" AND Character_ID="+database.INSERT_SYM
c.execute(qry, (quantity, itemid, charid))
else:
qry = "INSERT INTO Inventory_Item VALUES("+database.INSERT_SYM+","+database.INSERT_SYM+","+database.INSERT_SYM+")"
c.execute(qry, (charid, itemid, quantity))
db.commit()
def get_reward_items(rewardID):
db = database.db_connect()
c = db.cursor()
qry = '''SELECT Name AS ItemName, Quantity
FROM Reward_Item
JOIN Item ON Item_ID = ID
WHERE Reward_ID='''+database.INSERT_SYM;
c.execute(qry, (rewardID,))
result = []
for row in c:
result.append(row)
return result
def create_character(username, charname):
db = database.db_connect()
c = db.cursor()
id = get_player_id(username)
if (id != None):
qry = "INSERT INTO `Character` (Player_ID, Name) VALUES ("+database.INSERT_SYM+", "+database.INSERT_SYM+")"
c.execute(qry, (id, charname))
db.commit()
success = True
else:
success = False
return success
def post(self):
req_json = request.get_json(force=True)
_i_id = req_json['i_id']
# db
db = database.db_connect() # connection객체
sql1 = 'INSERT INTO completeitem (`u_id`, `title`, `content`, `getlost_date`, `c_id`) SELECT u_id, title, content, lost_date, c_id FROM lostitem WHERE i_id = \'{}\''.format(
_i_id)
sql2 = 'DELETE FROM lostitem WHERE i_id = \'{}\''.format(_i_id)
curs = db.cursor()
curs.execute(sql1)
curs.execute(sql2)
db.commit()
def getallloggedinuserdata():
"""Get the last 24h of data from loggedin users in steam"""
# Get Steam User statistics
jsoncontent = urllib.request.urlopen("http://store.steampowered.com/stats/userdata.json").read()
content = json.loads(jsoncontent)[0]
datapoints = content['data']
#start database connection
database.db_connect()
# deactivate autocommit so script can save the files faster
database.database.set_autocommit(False)
database.database.begin()
for datapoint in datapoints:
userdate = datetime.datetime.utcfromtimestamp(float(datapoint[0])/1000.)
try:
userlogged = database.UserLoggedIn.create(time=userdate,userloggedin=datapoint[1])
except Exception as e:
continue
database.database.commit()
# activate autocommit
database.database.set_autocommit(True)
#close database connection
database.db_close()
def saveKommen(dat, tim):
try:
con = db.db_connect()
cur = con.cursor()
cur.execute("INSERT OR IGNORE INTO worktime(DATUM,VON) VALUES(?,?)",
(dat, tim))
con.commit()
except sqlite3.Error as error:
print("Module saveKommen: ", error)
finally:
if cur:
cur.close()
if con:
con.close()
def deleteDate(date):
try:
con = db.db_connect()
cur = con.cursor()
cur.execute("DELETE FROM worktime WHERE DATUM=?", [date])
con.commit()
messagebox.showinfo("Info", date + " wurde erfolgreich gelöscht")
except sqlite3.Error as error:
print("Module deleteDate: ", error)
finally:
if cur:
cur.close()
if con:
con.close()
def saveGehen(dat, tim, didbreak, custombreak):
try:
con = db.db_connect()
cur = con.cursor()
cur.execute("UPDATE worktime SET BIS=? WHERE DATUM=?", (tim, dat))
calcWorktime(dat, didbreak, custombreak, con, cur)
except sqlite3.Error as error:
print("Module saveGehen: ", error)
finally:
if cur:
cur.close()
if con:
con.close()
def post(self):
req_json = request.get_json(force=True)
try:
_id = req_json['id']
_pw = req_json['password']
except KeyError:
return {'status': 400, 'message': 'Invalid parameter.'}, 400
# db
db = database.db_connect()
sql = "SELECT count(id), password, u_id, id, name, email, phone_number FROM dbdbdp.user WHERE id=\'{}\'".format(
_id)
curs = db.cursor()
curs.execute(sql)
rows = curs.fetchall()
row = rows[0]
# db close
curs.close()
db.close()
# password check
hashed = str(row[1]).encode('utf-8')
pw_flag = False
try:
pw_flag = bcrypt.checkpw(_pw.encode('utf-8'), hashed)
except:
return {
'status': 401,
'message': 'Error with id or password.'
}, 401
if int(row[0]) >= 1 and pw_flag:
user = {
'u_id': row[2],
'id': row[3],
'name': row[4],
'email': row[5],
'phone': row[6]
}
return {
'status': 200,
'access_token': auth.generateAccessToken(user)
}
else:
return {
'status': 401,
'message': 'Error with id or password.'
}, 401
def loadSetting(name):
try:
con = db.db_connect()
cur = con.cursor()
cur.execute("SELECT VALUE FROM settings WHERE NAME=?", [name])
value = cur.fetchone()
cur.close()
return value
except sqlite3.Error as error:
print("Module loadSetting: ", error)
finally:
if cur:
cur.close()
if con:
con.close()
def worker(data):
engine, dbsession = db_connect()
thumbs_generator = data[0]
chunk = data[1]
for file_ in chunk:
file = dbsession.query(File).get(file_[0])
filename = thumbs_generator.generate_video_thumb(file.path)
if filename:
file.analise_thumb = Path(filename).name
file.corrupted = False
else:
file.analise_thumb = None
file.corrupted = True
dbsession.add(file)
dbsession.commit()
engine.dispose()
def test(image):
with open('svm_model.pickle', 'rb') as f:
svm = pickle.load(f)
face_emb, boxes = get_encodings(face_model, image, get_box=True)
face_encodings = [svm.predict(np.array(fe)) for fe in face_emb]
connection = db.db_connect()
get_names = db.get_names(connection)
names = []
for f in face_encodings:
#names.append(get_names[f[0]])
print(get_names[f[0]])
'''
def insertWeekhours(weekhours):
try:
con = db.db_connect()
cur = con.cursor()
cur.execute(
"UPDATE settings SET VALUE=? WHERE NAME=?",
(weekhours, "workweekhours"),
)
con.commit()
except sqlite3.Error as error:
print("Module insertWeekhours: ", error)
finally:
if cur:
cur.close()
if con:
con.close()
def run(self, chunk):
self.engine, self.db_session = db_connect()
for file_el in chunk:
file_ = File()
try:
file_.deleted_state = file_el.attrib['deleted']
except:
file_.deleted_state = "Unknow"
file_.original_path = file_el.attrib['path']
file_.size = file_el.attrib['size']
value = file_el.find(
f".//{self.namespace}timestamp[@name='CreationTime']")
file_.creation_time = parser.parse(
value.text) if value is not None else None
value = file_el.find(
f".//{self.namespace}timestamp[@name='ModifyTime']")
file_.modify_time = parser.parse(
value.text) if value is not None else None
value = file_el.find(
f".//{self.namespace}timestamp[@name='AccessTime']")
file_.access_time = parser.parse(
value.text) if value is not None else None
value = file_el.find(
f".//{self.namespace}item[@name='Local Path']")
file_.extracted_path = value.text if value is not None else None
value = file_el.find(f".//{self.namespace}item[@name='SHA256']")
file_.sha256 = value.text if value is not None else None
value = file_el.find(f".//{self.namespace}item[@name='MD5']")
file_.md5 = value.text if value is not None else None
value = file_el.find(f".//{self.namespace}item[@name='Tags']")
file_.type_ = value.text if value is not None else None
value = file_el.find(
f".//{self.namespace}item[@name='ContentType']")
file_.content_type = value.text if value is not None else None
self.add(file_)
self.commit()
self.engine.dispose()
def post(self, i_id):
try:
req_json = request.get_json(force=True)
_u_id = req_json['u_id']
_content = req_json['content']
except KeyError:
return {'status': 400, 'message': 'Invalid parameter.'}, 400
# db
db = database.db_connect()
sql = 'INSERT INTO getcomment(u_id, i_id, content) VALUES ({}, {}, \'{}\')'.format(
_u_id, i_id, _content)
curs = db.cursor()
curs.execute(sql)
inserted_id = curs.lastrowid
db.commit()
return {'status': 200, 'get_com_id': inserted_id}
def get(self):
try:
req_parser = reqparse.RequestParser()
req_parser.add_argument('uid', type=str)
req_args = req_parser.parse_args()
uid = req_args['uid']
session = db_connect('../db/money.db')()
if uid is None:
all_users = session.query(User).all()
return [user.as_dict() for user in all_users]
else:
user = session.query(User).filter_by(uid=uid).first()
return user.as_dict()
except Exception as e:
return {'error': str(e)}
def get(self):
parser = reqparse.RequestParser()
parser.add_argument('query', type=str, required=True)
parser.add_argument('board', type=str, required=True)
args = parser.parse_args()
query = args['query'].replace('"', '').strip()
sql = 'SELECT item.i_id, item.title, item.register_date, u.id, u.name FROM {} as item JOIN user as u ON item.u_id = u.u_id WHERE content LIKE "%{}%";'
if args['board'] == 'get':
sql = sql.format('getitem', query)
elif args['board'] == 'lost':
sql = sql.format('lostitem', query)
elif args['board'] == 'complete':
sql = sql.format('completeitem', query)
else:
return {'status': 400, 'message': 'Invalid parameter.'}, 400
db = database.db_connect()
curs = db.cursor()
curs.execute(sql)
rows = curs.fetchall()
curs.close()
result = {
'status': 200,
'query': '',
'item_count': 0,
'items': list()
}
result['item_count'] = len(rows)
for row in rows:
temp = {
'i_id': row[0],
'title': row[1],
'register_date': row[2].strftime('%Y-%m-%d %H:%M:%S'),
'author_id': row[3],
'author_name': row[4]
}
result['items'].append(temp)
db.close()
return result, 200
def post(self, i_id):
data = parser.parse_args()
if data['uploadfile'] == "":
return {'status': 400, 'message': 'File not found.'}, 400
photo = data['uploadfile']
if photo:
filename = 'getitem_{}.jpg'.format(i_id)
photo.save(os.path.join(UPLOAD_FOLDER, filename))
# db
db = database.db_connect()
sql = 'INSERT INTO getimage(i_id, url) VALUES ({}, \'{}\')'.format(
i_id, filename)
curs = db.cursor()
curs.execute(sql)
db.commit()
return {'status': 200}, 200