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 fetch_single_order(id):
try:
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * from orders WHERE id = %s", id)
row = cursor.fetchone()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
def fetch_all_orders():
try:
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * from orders")
rows = cursor.fetchall()
resp = jsonify(rows)
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 fetch_all_users():
try:
# conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * from users")
rows = cursor.fetchall()
resp = jsonify(rows)
resp.status_code = 200
return resp
except Exception as e:
return e
finally:
cursor.close()
conn.close()
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 getUser(usuario):
cursor = conn.cursor(pymysql.cursors.DictCursor)
try:
cursor.execute("select * from login where usuario =%s", [usuario])
jsonObjects = []
rows = cursor.fetchall()
for object in rows:
login = {"usuario": object[0], "password": object[1]}
jsonObjects.append(login)
resp = json.dumps(jsonObjects)
return resp
except Exception as e:
print(e)
finally:
cursor.close()
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 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 getPeliculaByDirector(director):
cursor = conn.cursor(pymysql.cursors.DictCursor)
try:
cursor.execute("select * from pelicula where director =%s", [director])
rows = cursor.fetchall()
jsonObjects = []
for object in rows:
pelicula = {
"titulo": object[0],
"director": object[1],
"fecha": object[2]
}
jsonObjects.append(pelicula)
resp = json.dumps(jsonObjects)
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 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 __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()
def getUsersList(cls):
cur = conn.cursor()
cur.execute("""select distinct name from messenger.user;""")
res = cur.fetchall()
return res
(без реализации сетевого взаимодействия)
по созданию объектно-ориентированной модели пользователей и сообщений,
добавлению пользователей к списку контактов,
отправке сообщений и отображения их истории
20.04.17 - Добавлено взаимодействие с СУБД PostgreSQL
24.04.17 - Добавлен функционал создания чата, отправки и отображения истории сообщений в Tkinter
27.04.17 - Проект переведен на СУБД MySQL, реализовано многопоточное отображение истории сообщений общего чата в нескольких клиентских теримналах, а также отправка сообщений.
!!! Сообщения на кириллице пока что не поддерживаются !!!
28.04.17 - Добавлена аутентификация и авторизация
'''
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()
