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()