def add_participants(self, *participants):
for participant in [x for x in participants[0]]:
if participant in [x.id for x in self.owner.contacts
] and participant not in self.participants:
self.participants.append(participant)
else:
print(
"Пользователь {} не найден в списке контактов пользователя {}"
.format(participant, self.owner.name))
print("Чат номер", self.id, "создан юзером", self.owner.name,
"участники: ", self.participants)
for part in self.participants:
conn = pymysql.connect(host='localhost',
user='******',
password='******',
db='test',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cur = conn.cursor()
cur.execute(
"""
insert into messenger.chat_participants
(chatid, participant)
values (%s, %s);""",
(self.id, part))
conn.commit()
conn.close()
def delete_all_orders():
try:
cursor = conn.cursor()
cursor.execute("DELETE FROM orders")
conn.commit()
resp = jsonify("Orders deleted successfully")
resp.status_code = 200
return resp
except Exception as e:
print(e)
def delete_single_order(id):
try:
cursor = conn.cursor()
cursor.execute("DELETE from orders WHERE id = %s", id)
conn.commit()
resp = jsonify('Order deleted successfully')
resp.status_code = 200
return resp
except Exception as e:
print(e)
def deletePelicula(titulo):
cursor = conn.cursor()
try:
cursor.execute("delete from pelicula where titulo =%s", [titulo])
conn.commit()
resp = jsonify("Pelicula borrada correctamente")
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
def __init__(self, sender, text, chat):
self.sender = sender
self.text = str(text)
self.time = datetime.now()
cur = conn.cursor()
cur.execute(
"""
insert into messenger.message
(sender, time, text, chatid)
values (%s, %s, %s, %s);""",
(self.sender.id, self.time, self.text, chat))
conn.commit()
conn.close()
def profile(username):
token = request.cookies['JWT']
token_decoded = jwt.decode(token, app.config['SECRET_KEY'])
current_user = token_decoded['user']
try:
cursor.execute("""SELECT follows FROM followers WHERE username=%s AND follows=%s;""", (current_user, username))
following = cursor.fetchall()
except Exception as identifier:
following = cursor.fetchall()
if following:
try:
cursor.execute("""SELECT * FROM posts WHERE user_username = %s ORDER BY id_post DESC;""", (username,))
data = cursor.fetchall()
except Exception as identifier:
pass
if data == []:
data = [(username, 'No posts to show!'), False]
if request.method == "POST":
cursor.execute("""DELETE FROM followers WHERE username = %s AND follows = %s;""", (current_user, username))
conn.commit()
try:
cursor.execute("""SELECT * FROM posts WHERE user_username = %s AND post_privacy = %s ORDER BY id_post DESC;""", (username, 'public'))
data = cursor.fetchall()
except Exception as identifier:
pass
if data == []:
data = [(username, 'No posts to show!'), False]
return render_template('nfprofile.html', data=data)
return render_template("fprofile.html", data=data)
else:
try:
cursor.execute("""SELECT * FROM posts WHERE user_username = %s AND post_privacy = %s ORDER BY id_post DESC;""", (username, 'public'))
data = cursor.fetchall()
except Exception as identifier:
pass
if data == []:
data = [(username, 'No posts to show!'), False]
if request.method == "POST":
cursor.execute("""INSERT INTO followers (username, follows) VALUES (%s,%s);""", (current_user, username))
conn.commit()
try:
cursor.execute("""SELECT * FROM posts WHERE user_username = %s ORDER BY id_post DESC;""", (username,))
data = cursor.fetchall()
except Exception as identifier:
pass
if data == []:
data = [(username, 'No posts to show!'), False]
return render_template('fprofile.html', data=data)
return render_template("nfprofile.html", data=data)
def getContacts(self):
if self.contacts:
cur = conn.cursor()
cur.execute(
"""
select c.contact, u.name from messenger.contacts c join messenger.user u on c.contact=u.userid
where c.userid=%s;""", (self.id, ))
res = cur.fetchall()
contacts = [(x['contact'], x['name']) for x in res]
conn.commit()
conn.close()
return contacts
else:
return "Список контактов пользователя {} пуст".format(self.name)
def addContact(self, contact):
if contact in User.getUsersList:
self.contacts.append(contact)
print(
"Пользователь {} добавлен в список контактов пользователя {}".
format(contact.name, self.name))
cur = conn.cursor()
v = (self.id, contact.id)
cur.execute(
"insert into messenger.contacts (userid, contact) values (%s,%s)",
v)
conn.commit()
conn.close()
else:
print("Пользователь не зарегистрирован")
def post(id):
if request.method == "POST":
comment_text = request.form["comment_text"]
#print(comment_text)
token = request.cookies['JWT']
token_decoded = jwt.decode(token, app.config['SECRET_KEY'])
current_user = token_decoded['user']
time = datetime.datetime.utcnow()
#print(current_user)
cursor.execute("""INSERT INTO comments (username, post_id, comment, datetime) VALUES (%s,%s,%s,%s);""", (current_user, id, comment_text, time))
conn.commit()
cursor.execute("""SELECT * FROM posts WHERE id_post = %s;""", (id,))
data_1 = cursor.fetchall()
cursor.execute("""SELECT * FROM comments WHERE post_id = %s;""", (id,))
data_2 = cursor.fetchall()
data=[data_1,data_2]
return render_template("post.html", data=data)
def updatePelicula():
cursor = conn.cursor()
try:
_json = request.json
_titulo = _json['titulo']
_director = _json['director']
_fecha = _json['fecha']
if _titulo and _director and _fecha and request.method == 'POST':
sql = "update pelicula set director = %s, fecha = %s where titulo =%s"
data = (_director, _fecha, _titulo)
cursor.execute(sql, data)
conn.commit()
return resp
except Exception as e:
print(e)
finally:
cursor.close()
def add_user():
first_name = request.json.get('first_name')
last_name = request.json.get('last_name')
email = request.json.get('email')
password = request.json.get('password') or 'password'
address = request.json.get('address')
if request.method == 'POST':
if not 'first_name' in request.json or not request.json.get(
'first_name'):
return jsonify(message="First name and email is mandatory")
if not 'email' in request.json or not request.json.get('email'):
return jsonify(message="First name and email is mandatory")
sql_formula = 'INSERT INTO users (first_name, last_name, email_address, password, address) VALUES (%s, %s, %s, %s, %s)'
data = (first_name, last_name, email, password, address)
cursor = conn.cursor()
cursor.execute(sql_formula, data)
conn.commit()
resp = jsonify('User added successfully')
resp.status_code = 200
return resp
def __init__(self, owner):
self.owner = owner
self.participants = [self.owner.id]
self.messages = []
Chat.chats.append(self)
self.created_at = datetime.now()
cur = conn.cursor()
cur.execute(
"""
insert into messenger.chat
(owner, createdat)
values (%s, %s);""",
(self.owner.id, self.created_at))
cur.execute("""
select chatid, MAX(createdat) from messenger.chat
GROUP BY chatid;""")
self.id = cur.fetchone()['chatid']
conn.commit()
conn.close()
def createPeliculas():
cursor = conn.cursor()
try:
_json = request.json
_titulo = _json['titulo']
_director = _json['director']
_fecha = _json['fecha']
if _titulo and _director and _fecha and request.method == 'POST':
sql = "insert into pelicula(titulo, director, fecha) values(%s, %s, %s)"
data = (_titulo, _director, _fecha)
cursor.execute(sql, data)
conn.commit()
resp = jsonify("Pelicula añadida correctamente")
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
def add_order():
try:
json = request.json
name = json['name']
model = json['model']
quantity = json['quantity']
# validate the received values
if name and model and quantity and request.method == 'POST':
sql = "INSERT INTO orders(name, model, quantity) VALUES(%s, %s, %s)"
data = (
name,
model,
quantity,
)
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('Product added successfully!')
resp.status_code = 200
return resp
except Exception as e:
print(e)
def addUser():
cursor = conn.cursor()
try:
_json = request.json
_username = _json['usuario']
_password = _json['password']
#validamos si se han recivido
if _username and _password and request.method == 'POST':
sql = "insert into login(usuario, password) values(%s, %s)"
data = (_username, _password)
cursor.execute(sql, data)
conn.commit()
resp = jsonify("Usuario añadido correctamente")
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
def signup():
if request.method == "POST":
name = request.form["Name"]
surname = request.form["Surname"]
username = request.form["Username"]
email = request.form["email"]
password = request.form["Password"]
confirm_password = request.form["ConfirmPassword"]
if password != confirm_password:
flash("Passwords doesn't match. Please check.")
return redirect(request.url)
else:
try:
cursor.execute("SELECT * from users WHERE username = %s", (username,))
username_check = cursor.fetchall()
except Exception as identifier:
username_check = cursor.fetchall()
try:
cursor.execute("SELECT * from users WHERE email = %s", (email,))
email_check = cursor.fetchall()
except Exception as identifier:
email_check = cursor.fetchall()
if not username_check:
if not email_check:
hash_password = argon2.hash(password)
cursor.execute("""INSERT INTO users (name, surname, username, email, password) VALUES (%s, %s, %s, %s, %s)""", (name, surname, username, email, hash_password))
conn.commit()
#flash("Account has been created. Proceed with signing in.")
return render_template("successful_reg.html")
else:
flash("Email already exists. Please use another one.")
return redirect(request.url)
else:
flash("Username already exists. Please use another one.")
return redirect(request.url)
return render_template("registration.html")
def update_order():
try:
json = request.json
id = json['id']
name = json['name']
model = json['model']
quantity = json['quantity']
# validate the received values
if name and model and quantity and id and request.method == 'PUT':
sql = "UPDATE orders SET name=%s, model=%s, quantity=%s WHERE id=%s"
data = (
name,
model,
quantity,
id,
)
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('Product updated successfully!')
resp.status_code = 200
return resp
except Exception as e:
print(e)
def home():
token = request.cookies['JWT']
data = jwt.decode(token, app.config['SECRET_KEY'])
current_user = data['user']
if request.method == "POST":
post_text = request.form["post_text"]
post_privacy = request.form["post_privacy"]
time = datetime.datetime.utcnow()
print(time)
#print (post_privacy, current_user, post_text)
cursor.execute("""INSERT INTO posts (user_username, post, post_privacy, datetime) VALUES (%s, %s, %s, %s);""", (current_user, post_text, post_privacy, time))
conn.commit()
try:
cursor.execute("""SELECT * FROM posts
WHERE user_username IN (SELECT follows FROM followers WHERE username = %s)
union
SELECT * FROM posts WHERE post_privacy = 'public'
ORDER BY id_post DESC
""", (current_user,))
data = cursor.fetchall()
return render_template("home.html", data=data)
except Exception as identifier:
print ('No data!')
return render_template("home.html")
def __init__(self, name, password):
#User.users_list.append(self)
self.name = name
self.password = hashlib.md5(password.encode()).hexdigest()
self.status = 'offline'
self.contacts = []
self.created_at = datetime.now()
cur = conn.cursor()
cur.execute(
"""
insert into messenger.user
(name, password, status, createdat)
values (%s, %s, %s, %s);""",
(self.name, self.password, self.status, self.created_at))
cur.execute(
"""
select userid from messenger.user
where name=%s and password=%s;""",
(self.name, self.password))
self.id = cur.fetchone()['userid']
conn.commit()
conn.close()
cur = conn.cursor()
cur.execute("""
DROP TABLE IF EXISTS messenger.contacts;
DROP TABLE IF EXISTS messenger.message;
DROP TABLE IF EXISTS messenger.chat_participants;
DROP TABLE IF EXISTS messenger.chat;
DROP TABLE IF EXISTS messenger.user;
CREATE SCHEMA IF NOT EXISTS messenger;
CREATE TABLE IF NOT EXISTS messenger.user (userid serial PRIMARY KEY, name varchar(45), password varchar(256), status varchar(45), createdat timestamp);
CREATE TABLE IF NOT EXISTS messenger.chat (chatid serial PRIMARY KEY, owner integer REFERENCES messenger.user (userid), createdat timestamp);
CREATE TABLE IF NOT EXISTS messenger.message (messageid serial PRIMARY KEY, sender integer REFERENCES messenger.user (userid), time timestamp, text text, chatid integer REFERENCES messenger.chat (chatid));
CREATE TABLE IF NOT EXISTS messenger.chat_participants (chatid integer REFERENCES messenger.chat (chatid), participant integer REFERENCES messenger.user (userid));
CREATE TABLE IF NOT EXISTS messenger.contacts (userid integer REFERENCES messenger.user (userid), contact integer REFERENCES messenger.user (userid));
""")
print("connection sucsessful")
conn.commit()
conn.close()
def authenticate():
output.delete('0.0', END)
login = log_field.get()
passw = hashlib.md5(pass_field.get().encode()).hexdigest()
print(User.getUsersList())
'''
for user in User.getUsersList():
if (login, passw) == (user.name, user.password):
new_client = Client(user)
elif login == user.name and passw != user.password:
output.insert('1.0', 'Ошибка! Неверный пароль для пользователя с именем {} \n'.format(login))
else:
