def read_by_name(player_name):
connect, cursor = database.open_database()
cursor.execute("SELECT * FROM player WHERE playerName = ?", (player_name,))
data_list = cursor.fetchall()
database.close_databse(connect, cursor)
return data_list
def get_list():
connect, cursor = database.open_database()
cursor.execute("SELECT playerId FROM player")
id_list = cursor.fetchall()
database.close_databse(connect, cursor)
return id_list
def analysis_html(html_data):
soup = BeautifulSoup(html_data, 'html.parser')
player_profile = analysis.get_personal_data(soup)
player_result = analysis.get_player_result(soup)
connect, cursor = database.open_database()
cursor.execute(
"INSERT INTO playerProfile (playerId, playerName, playerAssco, playerGender, playerAge) VALUES (?, ?, ?, ?, ?)",
(player_profile[0], player_profile[1], player_profile[2], player_profile[4], player_profile[3])
)
player_id = player_profile[0]
for element in player_result[0]:
cursor.execute(
"INSERT INTO playerResult (playerId, playerResult) VALUES (?, ?)",
(player_id, element)
)
for element in player_result[1]:
cursor.execute(
"INSERT INTO playerOtherResult (playerId, playerResult) VALUES (?, ?)",
(player_id, element)
)
for element in player_result[2]:
cursor.execute(
"INSERT INTO playerDoubleResult (playerId, playerResult) VALUES (?, ?)",
(player_id, element)
)
database.close_databse(connect, cursor)
return
def api_game_list( id = None):
try:
db = database.open_database('localhost', 'oh_hell', 'db_config.txt')
players = database.get_table(db, 'player', 'player_id')
games = database.get_table(db, 'game', 'game_id')
jsonRep = { 'games' : [], 'players' : [] }
count = {}
for k in players.keys():
count[k] = 0
tieCount = 0
for id in games.keys():
game = games[id]
if game.winner_id > 0:
winner = players[game.winner_id].first_name
count[game.winner_id] = count[game.winner_id] + 1
else:
winner = 'Tie'
tieCount = tieCount + 1
jsonRep['games'].append( create_game_stat(id, game.time, game.winner_id))
for k in count.keys():
jsonRep['players'].append( create_player_stat(k, players[k].first_name, count[k]))
return jsonRep
finally:
if db != None:
db.close()
def get_female_rank():
connect, cursor = database.open_database()
cursor.execute("SELECT * FROM Womenrank ORDER BY score DESC")
data_list = cursor.fetchall()
database.close_databse(connect, cursor)
return data_list
def main(args):
conn = database.open_database("db/test.lite")
for filename in args[1:]:
filelines = process_file(filename)
counts=character_count(filelines)
print_char_counts(counts)
page = pages.Page(filename)
database.inter_histogram(conn,page,counts)
def get_team_rank(model):
connect, cursor = database.open_database()
if model == 1:
cursor.execute("SELECT * FROM CountryRankMan ORDER BY score DESC")
elif model == 2:
cursor.execute("SELECT * FROM CountryRankWomen ORDER BY score DESC")
data_list = cursor.fetchall()
database.close_databse(connect, cursor)
return data_list
def thread_function():
global tree, running, drive
conn, c = database.open_database()
while running:
if googledrive.check_internet():
if drive == None:
drive = googledrive.auth()
changes = googledrive.get_different(drive)
sorted(changes)
for change in changes:
database.update_database_with_file(c, conn, change)
tree = database.database_to_tree(c)
print("Database edit complete")
if running:
time.sleep(30) # Check once every hminute
database.close_database(c, conn)
def analysis_html(html_data):
soup = BeautifulSoup(html_data, 'html.parser')
player_match, next_link = analysis.get_player_match(soup)
if len(player_match) == 0:
print("table empty, data not found")
return None
try:
connect, cursor = database.open_database()
for element in player_match:
cursor.execute(
"INSERT INTO matchList2018 (matchName, playerA, playerB, playerX, playerY, event, resultA, resultX, winner, winnerDbl) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
(element[0], element[1], element[2], element[3], element[4],
element[5], element[6], element[7], element[8], element[9]))
database.close_databse(connect, cursor)
except OSError as e:
print(e)
return next_link
def analysis_html(html_data):
soup = BeautifulSoup(html_data, 'html.parser')
player_ranking, next_link = analysis.get_player_ranking(soup)
if len(player_ranking) <= 1:
print("table empty, data not found")
return None
try:
connect, cursor = database.open_database()
for element in player_ranking:
cursor.execute(
"INSERT INTO playerRanking (playerName, playerPosition, playerPoints) VALUES (?, ?, ?)",
(element[0], element[1], element[2]))
database.close_databse(connect, cursor)
except OSError as e:
print(e)
return next_link
def analysis_html(html_data):
soup = BeautifulSoup(html_data, 'html.parser')
tournament, next_link = analysis.get_tournament_list(soup)
if len(tournament) <= 1:
print("table empty, data not found")
return None
try:
connect, cursor = database.open_database()
for element in tournament:
if check_exist(element[0], cursor) == 0:
cursor.execute(
"INSERT INTO tournaments (tournamentID, year, tournamentName, type, kind, organizer, matches, fromTime, toTime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
(element[0], element[1], element[2], element[3], element[4], element[5], element[6], element[7], element[8])
)
database.close_databse(connect, cursor)
except OSError as e:
print(e)
return next_link
def gameList( id = None):
try:
db = database.open_database('localhost', 'oh_hell', 'db_config.txt')
players = database.get_table(db, 'player', 'player_id')
games = database.get_table(db, 'game', 'game_id')
text = ''
count = {}
for k in players.keys():
count[k] = 0
tieCount = 0
for id in reversed(games.keys()):
game = games[id]
if game.winner_id > 0:
winner = players[game.winner_id].first_name
count[game.winner_id] = count[game.winner_id] + 1
else:
winner = 'Tie'
tieCount = tieCount + 1
link = '<a href="/game/%d">%s</a>' % ( id, game.time[:10])
text += (str(id) + ": " + link + " : " + winner + "<BR>\n")
text += ('<p>\n')
for k in count.keys():
text += (players[k].first_name + ": " + str(count[k]) + "<BR>\n")
if tieCount > 0:
text += ( 'Ties : ' + str(tieCount) + "<BR>\n")
return "<html><head><title>Oh Hell Games</title></head><body>\n" + text + "\n</body></html>\n"
finally:
if db != None:
db.close()
def api_game_hand_summary( game_id, deal):
try:
db = database.open_database('localhost', 'oh_hell', 'db_config.txt')
deal_infos = database.get_table( db, 'deal', 'deal_id',
'where game_id = %d and deal = %d' % (game_id, deal))
deal_info = deal_infos[deal_infos.keys()[0]]
tricks = get_query_results(db,
'select trick_id from trick where deal_id = '
+ str(deal_info.deal_id) + ' order by trick_id')
trick_list = []
for (trick_id,) in tricks:
cards = get_query_results(db,
'select player_id, card from trick_cards '
+ 'where trick_id = ' + str(trick_id) + ' order by card_num')
trick_list.append( [ { 'player_id' : c[0], 'card' : c[1] } for c in cards ])
player_ids = [ "'" + str(trick['player_id']) + "'" for trick in trick_list[0] ]
players = database.get_table( db, 'player', 'player_id',
'where player_id in ( ' + ','.join(player_ids) + ')' )
player_map = {}
for player_id, row in players.iteritems():
player_map[player_id] = (row.first_name, row.last_name)
jsonRep = { 'deal_id' : deal_info.deal_id,
'players' : player_map,
'dealer_id' : deal_info.dealer_id,
'num_cards' : deal_info.num_cards,
'trump' : deal_info.trump,
'tricks' : trick_list }
return jsonRep
finally:
if db is not None:
db.close()
def api_game_summary( id ):
try:
db = database.open_database('localhost', 'oh_hell', 'db_config.txt')
players = database.get_table(db, 'player', 'player_id')
games = database.get_table(db, 'game', 'game_id', 'WHERE game_id = ' + str(id))
game = games[id]
gsc = ohhellquery.GameStateCreator(db)
gs = gsc.create(id)
if game.winner_id > 0:
winner = players[game.winner_id].first_name
else:
winner = 'Tie'
jsonRep = { 'winner' : winner, 'players' : [], 'hands' : []}
gsPlayers = gs.getPlayers()
scores = gs.currentScores()
for player in gsPlayers:
jsonRep['players'].append( { 'id' : player[0], 'name' : player[1], 'ip' : player[2] })
nPlayers = len(gsPlayers)
scoreSheet = gs.getScoreSheet()
for hand in scoreSheet:
hand_info = { 'num_cards' : hand[0], 'tricks' : [] }
for player in hand[1]:
hand_info['tricks'].append( { 'bid' : player[0], 'tricks' : player[1], 'score': player[2] })
jsonRep['hands'].append( hand_info)
return jsonRep
finally:
if db != None:
db.close()
i = args[1]
manga = database.get_manga(i)
if manga is None:
return jsonify("Invalid id")
if len(args) == 2:
return jsonify(database.to_dict(manga))
elif len(args) == 3 and args[2] == "update_info":
return jsonify(database.update_manga_info(i))
elif len(args) == 4 and args[2] == "get_chapters_list":
return jsonify(manga.get_chapters_list(int(args[3])))
elif len(args) == 5 and args[2] == "download_chapter":
return jsonify(manga.download_chapter(int(args[3]), int(args[4])))
elif len(args) == 4 and args[2] == "set_chapter_read":
return jsonify(manga.set_chapter_read(int(args[3])))
elif len(args) == 3 and args[2] == "delete":
manga.delete()
return jsonify("Deleted manga id: {}".format(i))
else:
return jsonify("Invalid args")
elif args[0] == "search_anilist":
return jsonify(database.search_anilist(request.form["search_title"]))
elif args[0] == "add_manga":
return jsonify(database.add_manga(request.form["add_title"]))
else:
return jsonify("Invalid args")
database.open_database()
app.run(host="0.0.0.0", port=1080, debug=True)
from flask import Flask, render_template, request
from urllib.parse import unquote_plus
from utils import get_best_answers, launch_default_browser
import database
import googledrive
import time
import threading
# Flask App setup
PORT = 5000
app = Flask(__name__)
# Get the KDtree from our database
conn, c = database.open_database()
tree = database.database_to_tree(c)
drive = None
running = True
def thread_function():
global tree, running, drive
conn, c = database.open_database()
while running:
if googledrive.check_internet():
if drive == None:
drive = googledrive.auth()
changes = googledrive.get_different(drive)
sorted(changes)
for change in changes:
database.update_database_with_file(c, conn, change)
tree = database.database_to_tree(c)
print("Database edit complete")
if running:
def gameSummary( id ):
try:
db = database.open_database('localhost', 'oh_hell', 'db_config.txt')
players = database.get_table(db, 'player', 'player_id')
games = database.get_table(db, 'game', 'game_id', 'WHERE game_id = ' + str(id))
game = games[id]
gsc = ohhellquery.GameStateCreator(db)
gs = gsc.create(id)
if game.winner_id > 0:
winner = players[game.winner_id].first_name
else:
winner = 'Tie'
text = """
<html>
<head>
<title>Oh Hell game on """ + game.time[:10] + """</title>
</head>
<body>
<table>
<tr>
<td>Game Id:</td><td> """ + str(id) + """ </td>
</tr>
<tr>
<td>Date :</td><td>""" + game.time[:10] + """</td>
</tr>
<tr>
<td>Winner :</td><td>""" + winner + """ </td>
</tr>
</table>
<p>
<p>
<table border=1 cellspacing=5 cellpadding=5>
"""
gsPlayers = gs.getPlayers()
scores = gs.currentScores()
text += '<tr>\n<td> </td>'
for player in gsPlayers:
text += '<th>' + player[1] + '</th>'
text += ('</tr>\n')
nPlayers = len(gsPlayers)
scoreSheet = gs.getScoreSheet()
oldScores = len(gsPlayers)*[0,]
for hand in scoreSheet:
text += '<tr><td>%d</td>'% ( hand[0])
i = 0
for player in hand[1]:
text += "<td>"
font = False
if oldScores[i] < player[2]:
text += "<font color='green'>"
font = True
elif oldScores[i] > player[2]:
text += "<font color='red'>"
font = True
text += "(%d, %d) %d" % player
if font:
text += "</font>"
text += "</td>"
oldScores[i] = player[2]
i = i + 1
text += '</tr>\n'
return text + "</table>\n</body>\n</html>"
finally:
if db != None:
db.close()
return output
#return 'Task ID: '+str(result[0][0])+'and Task: '+str(result[0][1])
@get('/edit/<no:int>')
def edit_item(no):
cur_data = database.get_task(no)
return template('edit_task', old=cur_data, no=no)
@post('/edit/<no:int>')
def edit_item(no):
edit = request.POST.task.strip()
status = request.POST.status.strip()
if status == 'Open':
status = 1
else:
status = 0
database.update_task(edit, status, no)
return redirect("/")
@get('/delete/<id:int>')
def delete_item(id):
database.delete_task(id)
return redirect("/")
if __name__ == "__main__":
database.open_database("todo.db")
debug(True)
run(host="0.0.0.0", port=8080, reloader=True)
else:
database.open_database("todo.db")
application = default_app()
"""
Author: RedFantom
License: MIT License
Copyright (C) 2018 RedFantom
"""
from database import open_database, execute_query
"""Open the Database"""
connection = open_database("northwind.db")
"""List all tables"""
query1 = """SELECT name FROM sqlite_master WHERE type='table';"""
tables = execute_query(connection, query1)
for table, in tables:
print(table, end=", ")
print()
"""Find the category with the most products"""
query2 = """SELECT ..."""
_, name, description = execute_query(connection, query2)[0]
print("The largest category is {} containing {}.".format(name, description))
"""Find the Category with the most customers"""
query3 = """SELECT ..."""
n, country = execute_query(connection, query3)[0]
print("The country with the most customers is {} with {} customers.".format(
country, n))
"""Find the most popular shipping method"""
query4 = """SELECT ..."""
_, company = execute_query(connection, query4)[0]
print("The most popular shipping company is {}.".format(company))
"""Determine the customer who ordered the biggest order"""
query5 = """SELECT ..."""
result, = execute_query(connection, query5)[0]
print("{} made the order with most items.".format(result))
required=True,
help='set duration of attack between 1 and 3600 seconds')
parser.add_argument('-t',
'--target',
metavar='target',
dest='target',
required=True,
help='specify domain name/IPv4 address to attack')
return parser.parse_args()
if __name__ == '__main__':
args = parse_args()
target_ip = resolve.resolve_host(args.target)
if target_ip is None:
print('[!] Unable to launch attack')
sys.exit(1)
attack_type = ATTACK_TYPES[args.attack_type]
conn = database.open_database(DB_PATH)
cur = conn.cursor()
for bot in cur.execute('SELECT * FROM Bots'):
t = threading.Thread(target=attack.launch_attack,
args=(bot, attack_type, args.duration, target_ip))
t.start()
t.join()
conn.close()
"""
Author: RedFantom
License: MIT License
Copyright (C) 2018 RedFantom
"""
import pandas as pd
from database import open_database, print_dataframe, execute_query
connection = open_database("chinook.db")
"""Database Inspection"""
query1 = """SELECT name "Name" FROM sqlite_master WHERE type = 'table';"""
tables = pd.read_sql_query(query1, connection)
print_dataframe("Tables", tables)
"""List all media types"""
query2 = """SELECT Name "Type" FROM media_types;"""
media_types = pd.read_sql_query(query2, connection)
print_dataframe("Media Types", media_types)
"""Count all tracks with each media type"""
for media_type in media_types["Type"]:
query3 = """
SELECT COUNT(TrackId) FROM tracks WHERE MediaTypeId IN
(SELECT MediaTypeId FROM media_types WHERE media_types.Name = '{}');
""".format(media_type)
number, = execute_query(connection, query3)[0]
print("Tracks, {}: {}".format(media_type, number))
print()
"""Display amount of tracks and name for playlists"""
def get_list3():
connect, cursor = database.open_database()
cursor.execute("SELECT * FROM matchList2018")
rank1 = cursor.fetchall()
database.close_databse(connect, cursor)
return rank1
"""
Author: RedFantom
License: MIT License
Copyright (C) 2018 RedFantom
"""
from datetime import datetime
from database import open_database, execute_query
"""Open database"""
connection = open_database("parking.db")
fmt = "%d-%m-%Y"
"""Determine name of the owner of the car that is parked the longest"""
query1 = """SELECT ..."""
customer, = execute_query(connection, query1)[0]
"""Determine car type and how long for the customer of the last query"""
query2 = """SELECT ...""".format(customer)
start, end, car_type, spot_id = execute_query(connection, query2)[0]
duration = (datetime.strptime(end, fmt) - datetime.strptime(start, fmt)).days
print("{} owns a {} and it is staying for {} days in spot {}.".format(
customer, car_type, duration, spot_id))
import database
if __name__ == '__main__':
conn, cursor = database.open_database()
tournament_id = 3421
cursor.execute("select * from tournaments where tournamentID = ?",
(tournament_id, ))
result = cursor.fetchall()
print('There are', len(result), 'result')
print(result)
#!/usr/bin/python
import sys, GameState, ohhellquery, database
if len(sys.argv) < 2:
print 'Usage: registerGame.py <xml files>'
sys.exit(1)
db = database.open_database('localhost', 'oh_hell', 'db_config.txt')
for f in sys.argv[1:]:
parser = GameState.GameXMLParser()
gameState = parser.parse(f)
ohhellquery.add_game_to_db(db, gameState)