def create_relation(user_1_id, user_2_id, relation_type):
remove_relation(user_1_id, user_2_id)
remove_relation(user_2_id, user_1_id)
timestamp = int(time.time() * 1000)
cursor.execute("insert into social values (?, ?, ?, ?)",
[user_1_id, user_2_id, relation_type, timestamp])
connection.commit()
def modify_tokens(user_id, formatted_token, remove):
temp_tokens = valid_tokens(user_id)
temp_tokens.remove(formatted_token) if remove else temp_tokens.append(
formatted_token)
cursor.execute("update users set valid_tokens = ? where user_id = ?",
[json.dumps(temp_tokens), user_id])
connection.commit()
def getTracks():
cursor.execute(
"""SELECT Faixas.id,Faixas.id_faixa_album,Faixas.nome,Faixas.descricao,Faixas.tipo_gravacao,Faixas.album,Faixas.tipo_composicao,Faixas.duracao,Compositor.nome,Interprete.nome,Composicao.nome
From Faixas, FaixasCompositorAux,Compositor, FaixasInterpreteAux,
Interprete,Composicao
WHERE Faixas.id = FaixasCompositorAux.id_faixa
AND FaixasCompositorAux.id_compositor = Compositor.id
AND FaixasInterpreteAux.id_faixa = Faixas.id
AND FaixasInterpreteAux.id_interprete = Interprete.id
AND Faixas.tipo_composicao = Composicao.id
GROUP BY Faixas.id,Faixas.id_faixa_album,Faixas.nome,Faixas.descricao,Faixas.tipo_gravacao,Faixas.album,Faixas.tipo_composicao,Faixas.duracao,Compositor.nome,Interprete.nome,Composicao.nome
ORDER BY Faixas.id""")
faixas = []
for row in cursor.fetchall():
faixas.append({
'id': row[0],
'id_faixa_album': row[1],
'nome': row[2],
'descricao': row[3],
'tipo_gravacao': row[4],
'album': row[5],
'tipo_composicao': row[6],
'duracao': (str(row[7])),
'nome_compositor': row[8],
'nome_interprete': row[9],
'nome_composicao': row[10]
})
return jsonify(faixas)
def store_commit(repos_id, commit, filename, author_name, author_email,
author_date, committer_name, committer_email, committer_date,
added, removed, whitespace):
# Fix some common issues in git commit logs and store data
# Sometimes git is misconfigured and name/email get swapped
author_name, author_email = check_swapped_emails(author_name, author_email)
committer_name, committer_email = check_swapped_emails(
committer_name, committer_email)
# Some systems append extra info after a second @
author_email = strip_extra_amp(author_email)
committer_email = strip_extra_amp(committer_email)
store = (
"INSERT INTO analysis_data (repos_id,commit,filename,"
"author_name,author_raw_email,author_email,author_date,"
"committer_name,committer_raw_email,committer_email,committer_date,"
"added,removed,whitespace) "
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)")
cursor.execute(store,
(repos_id, commit, filename, author_name, author_email,
discover_alias(author_email), author_date, committer_name,
committer_email, discover_alias(committer_email),
committer_date, added, removed, whitespace))
db.commit()
log_activity('Debug', 'Stored commit: %s' % commit)
def update_status(status):
# Update the status displayed in the UI
query = ("UPDATE settings SET value=%s WHERE setting='utility_status'")
cursor.execute(query, (status, ))
db.commit()
def shop_menu(callback):
message = callback.message
if not USER_BASKET_DATA.get(callback.from_user.id):
USER_BASKET_DATA[callback.from_user.id] = {}
if [int(callback.data)
] in cursor.execute('select shop_id from menu').fetchall():
shop_name_description = \
cursor.execute(f"select name, description from shops where id={callback.data}").fetchall()[0]
bot.send_message(
message.chat.id,
f'Ресторан "{shop_name_description[0]}"\n{shop_name_description[1]}'
)
dishes = cursor.execute(f"select description, name, photo, cost from menu where shop_id = '{callback.data}'")\
.fetchall()
names = [i[1] for i in dishes]
SHOP_MENU_DATA[callback.from_user.id] = {
dish[1]: dish
for dish in dishes
}
bot.send_message(message.chat.id,
'Доступные блюда:',
reply_markup=create_menu_keyboard(
names, callback.data))
else:
bot.send_message(message.chat.id,
'Кажется этот ресторан еще не настроил свое меню')
def get_chats_list():
if not request.json or 'user' not in request.json or not request.json[
'user'].isdigit():
abort(400)
chats_id = cursor.execute(
f"select chat_id from user_chat where user_id={request.json['user']}"
).fetchall()
last_messages = []
for chat_id in chats_id:
if cursor.execute(
f"select created_at from message where chat={chat_id[0]}"
).fetchall():
last_messages.append([
chat_id[0],
sorted([
i[0] for i in cursor.execute(
f"select created_at from message where chat={chat_id[0]}"
).fetchall()
])[-1]
])
result = sorted([
cursor.execute(f"select * from chat where id={i[0]}").fetchall()[0]
for i in last_messages
],
key=lambda x: x[1],
reverse=True)
return jsonify({'chats': result})
def getFaixaCompositores():
cursor.execute("SELECT * from FaixasCompositorAux")
faixacompositores = []
for row in cursor.fetchall():
faixacompositores.append({'id_compositor':row[0],
'id_faixa': row[1]})
return jsonify(faixacompositores)
def post(self):
data = UserRegistration.parser.parse_args()
data['pwd'] = User.generate_hash(data['pwd'])
create_users_table()
insert_query = """INSERT INTO users (username, pwd, firstname,
lastname, othernames, email, registered, isAdmin)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s);"""
record_to_insert = list(data.values())
try:
cursor.execute(insert_query, record_to_insert)
connection.commit()
access_token = create_access_token(identity=data['username'])
return {
'status': 201,
'data': [{
'token': access_token,
'user': data['username']
}]
}, 201
# TODO: HANDLE ERROR BETTER -- 409 IF CONFLICT, 500 IF OTHER
except (Exception, psycopg2.Error) as error:
connection.rollback()
return {
'status': 500,
'message': json.dumps(error, default=str)
}, 500
def git_repo_updates():
# Update existing repos
update_status('Updating repos')
log_activity('Info', 'Updating existing repos')
repo_base_directory = get_setting('repo_directory')
query = ("SELECT id,projects_id,git,name,path FROM repos WHERE "
"status='Active'")
cursor.execute(query)
existing_repos = list(cursor)
for row in existing_repos:
log_activity('Verbose', 'Attempting to update %s' % row['git'])
update_repo_log(row['id'], 'Updating')
cmd = ("git -C %s%s/%s%s pull" %
(repo_base_directory, row['projects_id'], row['path'],
row['name']))
return_code = subprocess.Popen([cmd], shell=True).wait()
if return_code == 0:
update_repo_log(row['id'], 'Up-to-date')
log_activity('Verbose', 'Updated %s' % row["git"])
else:
update_repo_log(row['id'], 'Failed (%s)' % return_code)
log_activity('Error', 'Could not update %s' % row["git"])
log_activity('Info', 'Updating existing repos (complete)')
def move(self, user_id, column):
if len(self.board.win_positions) > 0: return
if self.board.board_full: return
move = self.player_1_id if self.board.player_1 else self.player_2_id
if user_id != move: return
self.board.drop_fisje(column)
io.emit("fieldUpdate", {"field": self.board.board}, room=self.room)
now = int(time.time() * 1000)
cursor.execute(
"update games set last_activity = ?, moves = moves || ? || ',' where game_id = ?",
[now, column, self.game_id])
connection.commit()
if len(self.board.win_positions) > 0 or self.board.board_full:
outcome = "d"
if not self.board.board_full:
winner = self.board.board[int(self.board.win_positions[0][0])]
outcome = "w" if winner == "2" else "l"
io.emit("finish", {
"winPositions": self.board.win_positions,
"boardFull": self.board.board_full
},
room=self.room)
self.close("finished", outcome)
return
io.emit("turnUpdate", {"player1": self.board.player_1}, room=self.room)
def get_recommendation():
if request.method == "POST":
RATINGS_PATH = '/srv/movielens/ratings_matrix.npz'
RATINGS_MATRIX = ratings_matrix.ratings_matrix(RATINGS_PATH)
user_id = request.json["user_id"]
sql_query = "SELECT DISTINCT movie_id FROM user_favorite WHERE user_id = %s"
cursor.execute(sql_query, tuple([user_id]))
movie_ids = cursor.fetchall()
movie_list = []
if len(movie_ids) == 0:
result = {"recommended_movies": movie_list}
return jsonify(result)
liked = {}
for movie_id in movie_ids:
movie_id = movie_id[0]
liked[movie_id] = 50
print(liked)
recommendations = get_matches(RATINGS_MATRIX, liked,
NUM_RECOMMENDATIONS)
print(recommendations)
sql_query = "SELECT * FROM movie WHERE id = %s"
for index in range(NUM_RECOMMENDATIONS):
movie_id = recommendations[index][1]
movie_id = RATINGS_MATRIX.imdb_id(movie_id)
cursor.execute(sql_query, tuple([int(movie_id)]))
movie_row = cursor.fetchone()
movie = get_movie_details(movie_row)
movie_list.append(movie)
result = {"recommended_movies": movie_list}
print(movie_list)
return jsonify(result)
def index():
data = request.get_json()
# get form data
email = data.get("email") or ""
password = data.get("password") or ""
# return malformed request if email or password is missing
if not email or not password:
return "", 400
# resolve user_id from username or email
user_id = None
user_id = user_id or cursor.execute(
"select user_id from users where email = ?", [email]).fetchone()
user_id = user_id or cursor.execute(
"select user_id from users where lower(username) = lower(?)",
[email]).fetchone()
if user_id == None: return "", 401
# check the password
valid_password = login_password(user_id[0], password)
if not valid_password: return "", 401
# generate a new authentication token and add it to the users valid token list
new_token = token.generate_token()
token.add_token(user_id[0], token.hash_token(new_token))
# make response with the set_cookie header
res = make_response("", 200)
res.set_cookie("token",
new_token["token"],
expires=int(new_token["expirationDate"] / 1000))
return res
def get_movie_details(row):
id = row[0]
sql_query = "SELECT person.name FROM person, person_junction " +\
"WHERE person.id = person_junction.person_id" +\
" AND person_junction.role = 'cast'" +\
" AND person_junction.movie_id = %s"
cursor.execute(sql_query, tuple([id]))
casts = cursor.fetchmany(size=4)
actors = []
for cast in casts:
actors.append(cast[0])
sql_query = "SELECT rating FROM ratings WHERE movie_id = %s"
cursor.execute(sql_query, tuple([id]))
rating = cursor.fetchone()[0]
movie = {
"id": row[0],
"title": row[1],
"year": row[2],
"url": row[3],
"plot": row[5][:200] + " ...",
"genre": row[6],
"language": row[7],
"casts": actors,
"rating": rating
}
return movie
def index():
cursor.execute('select name from Sequences;')
response = Response()
response.set_data(f'Welcome to the Integer Sequence Database.\r\n</br>Sequences: {str([*cursor])[1:-1]}')
return response
def collection():
sql = "select * from tbl_sp where id_sp = 33"
cursor.execute(sql)
record1 = cursor.fetchall()
sql = "select * from tbl_sp where id_sp = 34"
cursor.execute(sql)
record2 = cursor.fetchall()
return render_template("collection.html", r1=record1, r2=record2)
def get_setting(setting):
# Get a setting from the database
query = ("SELECT value FROM settings WHERE setting=%s ORDER BY "
"last_modified DESC LIMIT 1")
cursor.execute(query, (setting, ))
return cursor.fetchone()["value"]
def get_setting(setting):
# Get a setting from the database
query = ("SELECT value FROM settings WHERE setting='%s' ORDER BY "
"last_modified DESC LIMIT 1" % setting)
cursor.execute(query)
return cursor.fetchone()["value"]
def cleanup():
now = int(time.time() * 1000)
old_games = cursor.execute(
"select game_id from games where (status = \"wait_for_opponent\" or status = \"in_progress\") and last_activity < ?",
[now - 5 * 60 * 1e3]).fetchall()
for game_id in old_games:
cursor.execute("delete from games where game_id = ?", [game_id[0]])
connection.commit()
def getInterpretes():
cursor.execute("SELECT * from Interprete")
interpretes = []
for row in cursor.fetchall():
interpretes.append({'id':row[0],
'nome': row[1],
'tipo':row[2]})
return jsonify(interpretes)
def viewSeq():
name = request.args.get("name") # get query string
response = Response()
cursor.execute(f'select seq from Sequences where name=\'{name}\';')
response.set_data(str([str(i) for i in cursor])) # write to response
return response
def add_favorite():
if request.method == "POST":
user_id = request.json["user_id"]
movie_id = request.json["movie_id"]
sql_query = "INSERT INTO user_favorite(user_id, movie_id) VALUES(%s, %s)"
cursor.execute(sql_query, tuple([user_id, movie_id]))
database.commit()
return "SUCCESS"
return "FAILED"
def added_user():
us = request.form.get("us")
pa = request.form.get("pa")
pq = request.form.get("pq")
sql = f"insert into tbl_user(user_name, pass_word, phan_quyen) values(N'{us}', N'{pa}', {pq})"
cursor.execute(sql)
connection.commit()
return redirect("/user")
def update_repo_log(repos_id, status):
# Log a repo's fetch status
log_message = ("INSERT INTO repos_fetch_log (repos_id,status) "
"VALUES (%s,%s)")
cursor.execute(log_message, (repos_id, status))
db.commit()
def update_analysis_log(repos_id, status):
# Log a repo's analysis status
log_message = ("INSERT INTO analysis_log (repos_id,status) "
"VALUES (%s,%s)")
cursor.execute(log_message, (repos_id, status))
db.commit()
def rename_shop_final(message):
new_name = message.text
old_name = cursor.execute(
f"select name from shops where user_id = '{message.from_user.id}'"
).fetchall()[0][0]
cursor.execute(
f"update shops set name = '{new_name}' where name = '{old_name}'")
update_state(RENAME_STATE, message, RENAME_CLOSE)
connection.commit()
bot.send_message(message.chat.id, 'Имя успешно изменено')
def start_command(message):
clear_all_state(message)
bot.send_message(
message.chat.id,
'Привет, ты на универсальной платформе по доставке еды.\n'
'Достпуный список команд - /help')
user_id = str(message.from_user.id)
if [user_id] not in cursor.execute('select user_id from users').fetchall():
cursor.execute(f"insert into users (user_id) values ('{user_id}')")
connection.commit()
def product():
if "username" in session:
id_sp = request.args.get("id_sp", type=int)
sql = "select * from tbl_sp"
cursor.execute(sql)
record = cursor.fetchall()
print(record)
return render_template("ad_product.html", r=record, id_sp=id_sp)
else:
return redirect("/login")
def createPlaylist():
data = request.form.to_dict()
if not (data and data['nome'] and data['descricao']):
return "Json vazio", 400
playlist = {'nome': data['nome'], 'descricao': data['descricao']}
query = "INSERT INTO Playlist (nome, descricao, data_criacao) VALUES (?, ?, SYSDATETIME())"
cursor.execute(query, playlist['nome'], playlist['descricao'])
cnxn.commit()
return playlist, 201
def route(user_1_id, user_2_id):
cursor.execute(
"update social set type = \"friendship\" where user_1_id = ? and user_2_id = ?",
[user_2_id, user_1_id])
connection.commit()
io.emit("changedRelation", {"id": user_2_id}, room="user-" + user_1_id)
io.emit("changedRelation", {"id": user_1_id}, room="user-" + user_2_id)
return "", 200
def store_working_commit(repo_id, commit):
# Store the working commit.
store_commit = ("UPDATE repos " "SET working_commit = %s " "WHERE id = %s")
cursor.execute(store_commit, (commit, repo_id))
db.commit()
log_activity('Debug', 'Stored working commit: %s' % commit)
import config
from db import cursor
from markovify import markovify
userlist = {}
for row in cursor.execute('SELECT usercode, username FROM user'):
userlist[row[0]] = row[1]
markov_chains = {}
print(userlist)
class Markov(object):
def __init__(self, usercode):
messages = [row[0] for row in cursor.execute('SELECT body FROM message WHERE usercode=?', (usercode,))]
self.text_model = markovify.Text('. '.join(messages))
def generate_markov_text(self):
output = ''
for r in range(config.markov_sentences):
attempts = 0
sentence = ' '.join(self.text_model.chain.walk(None))
while attempts < 100 and len(sentence.split()) < config.markov_sentence_length:
sentence = ' '.join(self.text_model.chain.walk(None))
attempts += 1
sentence = sentence.replace('.', '').strip()
output += sentence + '. '
return output[:-1]
filename = 'facade_summary-projects_by_LoC_and_number_contributors_by_year.xlsx'
#--> Set the description of the data
detail = 'LoC added (Unique emails)'
#--> Change this to modify the names of each worksheet
sheets = reversed(list(range(int(get_setting('start_date')[:4]),
datetime.datetime.now().year + 1)))
#--> Change this to modify the x axis
get_x_axis = "SELECT name,id FROM projects"
cursor.execute(get_x_axis)
x_axis = list(cursor)
facade_dir = dirname(dirname(dirname(filepath)))
outfile = os.path.join(facade_dir,'files',filename)
workbook = xlsxwriter.Workbook(outfile)
bold = workbook.add_format({'bold': True})
italic = workbook.add_format({'italic': True})
bold_italic = workbook.add_format({'bold': True, 'italic': True})
numformat = workbook.add_format({'num_format': '#,##0'})
for sheet in sheets:
worksheet = workbook.add_worksheet(str(sheet))
def __init__(self, usercode):
messages = [row[0] for row in cursor.execute('SELECT body FROM message WHERE usercode=?', (usercode,))]
self.text_model = markovify.Text('. '.join(messages))
checker = enchant.Dict('en_US')
DEBUG = False
base_directory = raw_input('Dir name: ')
# Import users first
for root, subdirs, filenames in os.walk(base_directory):
for filename in filenames:
if filename in ('channels.json', 'integration_logs.json', '.DS_Store'):
continue
with open(os.path.join(root, filename)) as f:
data = json.loads(f.read())
if filename == 'users.json':
for user in data:
try:
print('Loading ' + user['name'])
if not DEBUG: cursor.execute('INSERT INTO user VALUES(?, ?, ?, ?)', (user['id'], user['name'], permissions.USER, config.starting_beers))
conn.commit()
except Exception as e:
print(e)
else:
for message in data:
try:
if not message.get('subtype'): # assume anything without a subtype is a regular message
text = message['text']
# Ignore messages that are ! commands
if text.startswith('!'):
continue
for emoji in re.findall(r'(:[a-z0-9_]+:)', text):
if not DEBUG: cursor.execute('INSERT INTO emoji VALUES (?)', (emoji,))
