예제 #1
0
파일: User.py 프로젝트: Njsao/FlaskServer
 def get_user_by_id(self, id):
     connection = MySQLConn()
     query = """select tu.* , tbl_user_dettail.bday, tbl_user_dettail.role, tbl_user_dettail.city,
               tbl_user_dettail.rate, tbl_user_dettail.active, tbl_user_dettail.description,
               tbl_user_dettail.avatar
               from(select * from tbl_user where id_user = '******') as tu
               left join tbl_user_dettail on tu.id_user = tbl_user_dettail.id_user """ % (
         id
     )
     connection.open()
     connection.get_session().execute(query)
     user_data = connection.get_session().fetchone()
     desc = connection.get_session().description
     connection.get_connection().commit()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     user = User()
     for data, col in zip(range(0, len(desc)), desc):
         print("> > >  %s = %s" % (col[0], user_data[data]))
         if isinstance(user_data[data], datetime.date):
             user.set(col[0], str(user_data[data]))
         else:
             user.set(col[0], user_data[data])
     connection.close()
     return user
예제 #2
0
파일: Notice.py 프로젝트: Njsao/FlaskServer
 def get_notice_by_user(self, notice):
     connection = MySQLConn()
     query = """SELECT * FROM tbl_notice WHERE user_id = '%s' ORDER BY written_at DESC; """ % (notice.get("user_id"))
     connection.open()
     connection.get_session().execute(query)
     notices_data = connection.get_session().fetchall()
     desc = connection.get_session().description
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     notices = []
     for notice_data in notices_data:
         notice = Notice()
         for data, col in zip(range(0, len(desc)), desc):
             print("> > >  %s = %s" % (col[0], notice_data[data]))
             if isinstance(notice_data[data], datetime.datetime):
                 notice.set(col[0], str(notice_data[data]))
             elif col[0] == "sender_id" or col[0] == "receiver_id":
                 user = self.user_m.get_user_by_id(int(notice_data[data]))
                 notice.set(col[0], user.__dict__)
             else:
                 notice.set(col[0], notice_data[data])
         notices.append(notice.__dict__)
     connection.close()
     return notices
예제 #3
0
파일: User.py 프로젝트: Njsao/FlaskServer
 def get_filtered_user(self, user):
     connection = MySQLConn()
     query = """select tu.* ,tud.bday, tud.role, tud.city,tud.rate, tud.active, tud.description, tud.avatar
                 from (select * from tbl_user_dettail where role like '%s%%' and city like '%s%%' and rate > '%s') as tud
                 join (select * from tbl_user where name like '%s%%') as tu
                 on tu.id_user = tud.id_user""" % (
         user.get("role"),
         user.get("city"),
         user.get("rate"),
         user.get("name"),
     )
     connection.open()
     connection.get_session().execute(query)
     users_data = connection.get_session().fetchall()
     desc = connection.get_session().description
     connection.get_connection().commit()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     users = []
     for user_data in users_data:
         user = User()
         for data, col in zip(range(0, len(desc)), desc):
             print("> > >  %s = %s" % (col[0], user_data[data]))
             if isinstance(user_data[data], datetime.date):
                 user.set(col[0], str(user_data[data]))
             else:
                 user.set(col[0], user_data[data])
         users.append(user.__dict__)
     connection.close()
     return users
예제 #4
0
파일: Follow.py 프로젝트: Njsao/FlaskServer
 def get_follow(self, follow):
     user_m = UserManager()
     connection = MySQLConn()
     query = """SELECT * FROM tbl_follow WHERE user_id = '%s' and target_id = '%s'; """ % (follow.get("user_id"),
                                                                                           follow.get("target_id"))
     connection.open()
     connection.get_session().execute(query)
     follow_data = connection.get_session().fetchone()
     desc = connection.get_session().description
     print(desc)
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     follow = Follow()
     for data, col in zip(range(0, len(desc)), desc):
         print("> > >  %s = %s" % (col[0], follow_data[data]))
         if isinstance(follow_data[data], datetime.date):
             follow.set(col[0], str(follow_data[data]))
         elif col[0] == "user_id" or col[0] == "target_id":
             user = user_m.get_user_by_id(int(follow_data[data]))
             follow.set(col[0], user.__dict__)
         else:
             follow.set(col[0], follow_data[data])
     connection.get_connection().commit()
     connection.close()
     return follow
예제 #5
0
파일: User.py 프로젝트: Njsao/FlaskServer
 def _create_table_user(self):
     connection = MySQLConn()
     query = """CREATE TABLE IF NOT EXISTS %s (
               %s INT NOT NULL AUTO_INCREMENT,
               %s VARCHAR(55) NOT NULL,
               %s VARCHAR(55) NOT NULL,
               %s VARCHAR(55) NOT NULL,
               %s VARCHAR(55) NOT NULL,
               PRIMARY KEY (%s),
               UNIQUE KEY %s_UNIQUE (%s));""" % (
         self.__tbl_user,
         self.__id_user,
         self.__name,
         self.__surname,
         self.__email,
         self.__password,
         self.__id_user,
         self.__email,
         self.__email,
     )
     connection.open()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_session().execute(query)
     connection.get_connection().commit()
     connection.close()
예제 #6
0
 def get_users_intouch(self, message):
     connection = MySQLConn()
     query = """select tu.* ,tud.bday, tud.role, tud.city,tud.rate, tud.active, tud.description, tud.avatar
                 from (select * from tbl_user_dettail where role is not null) as tud
                 join (select * from tbl_user where email != '%s' order by rand() limit 7) as tu
                 on tu.id_user = tud.id_user""" % ()
     connection.open()
     connection.get_session().execute(query)
     users_data = connection.get_session().fetchall()
     desc = connection.get_session().description
     connection.get_connection().commit()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     users = []
     for user_data in users_data:
         user = User()
         for data, col in zip(range(0, len(desc)), desc):
             print("> > >  %s = %s" % (col[0], user_data[data]))
             if isinstance(user_data[data], datetime.date):
                 user.set(col[0], str(user_data[data]))
             else:
                 user.set(col[0], user_data[data])
         users.append(user.__dict__)
     connection.close()
     return users
예제 #7
0
 def get_message_by_user(self, message):
     connection = MySQLConn()
     query = """select * from tbl_message where sender_id = '%s' or receiver_id = '%s' ; """ % (
     message.get('sender_id'),
     message.get('sender_id'))
     connection.open()
     connection.get_session().execute(query)
     messages_data = connection.get_session().fetchall()
     desc = connection.get_session().description
     connection.get_connection().commit()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     messages = []
     for message_data in messages_data:
         message = Message()
         for data, col in zip(range(0, len(desc)), desc):
             print("> > >  %s = %s" % (col[0], message_data[data]))
             if isinstance(message_data[data], datetime.datetime):
                 message.set(col[0], str(message_data[data]))
             elif col[0] == "sender_id" or col[0] == "receiver_id":
                 user = self.user_m.get_user_by_id(int(message_data[data]))
                 message.set(col[0], user.__dict__)
             else:
                 message.set(col[0], message_data[data])
         messages.append(message.__dict__)
     connection.close()
     return messages
예제 #8
0
파일: User.py 프로젝트: Njsao/FlaskServer
 def insert(self, user):
     connection = MySQLConn()
     query = """INSERT INTO %s ( %s , %s, %s, %s ) VALUES ( '%s' , '%s', '%s', '%s' );""" % (
         self.__tbl_user,
         self.__name,
         self.__surname,
         self.__email,
         self.__password,
         user.get("name"),
         user.get("surname"),
         user.get("email"),
         user.get("password"),
     )
     connection.open()
     try:
         connection.get_session().execute(query)
         print("#DB#    -execute-\n")
         print("#DB#    %s \n" % (query))
         current_id = connection.get_session().lastrowid
         connection.get_connection().commit()
         connection.close()
         if user.get("role") != None:
             user.set("id_user", current_id)
             self._insert_dettails(user)
     # catch duplicated Entries
     except IntegrityError:
         print("#DB-ERR#    %s already exists\n" % (user.get("email")))
         connection.close()
예제 #9
0
 def set_read(self, message):
     connection = MySQLConn()
     query = """ update tbl_message set isread = 1 where sender_id = %s and receiver_id = '%s';""" % (
         message.get("sender_id"), message.get("receiver_id"))
     connection.open()
     connection.get_session().execute(query)
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     connection.close()
예제 #10
0
파일: Follow.py 프로젝트: Njsao/FlaskServer
 def insert(self, follow):
     connection = MySQLConn()
     query = """INSERT INTO tbl_follow (user_id, target_id, date_follow)
               VALUES ( '%s' , '%s', '%s');""" % (follow.get("user_id"), follow.get("target_id"),
                                                  follow.get("date_follow"))
     connection.open()
     connection.get_session().execute(query)
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     connection.close()
예제 #11
0
파일: Follow.py 프로젝트: Njsao/FlaskServer
    def remove_follower(self, follow):
        connection = MySQLConn()
        query = """DELETE FROM tbl_follow WHERE user_id = '%s' and target_id = '%s';""" % (follow.get("user_id"),
                                                                                          follow.get("target_id"))
        connection.open()
        print("#DB#    %s \n" % (query))

        connection.get_session().execute(query)
        print("#DB#    -execute-\n")
        connection.get_connection().commit()
        connection.close()
예제 #12
0
파일: Notice.py 프로젝트: Njsao/FlaskServer
 def insert(self, notice):
     connection = MySQLConn()
     query = """INSERT INTO tbl_notice (user_id, text, written_at)
               VALUES ( '%s' , '%s', '%s');""" % (
     notice.get("user_id"), notice.get("text"), notice.get("written_at"))
     connection.open()
     connection.get_session().execute(query)
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     connection.close()
예제 #13
0
파일: User.py 프로젝트: Njsao/FlaskServer
 def login(self, email, password):
     connection = MySQLConn()
     query = """ SELECT count(*) FROM tbl_user where email='%s' and password='******'; """ % (email, password)
     connection.open()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_session().execute(query)
     result = connection.get_session().fetchone()[0] == 1
     connection.get_connection().commit()
     connection.close()
     print("> > > login result: %s \n" % result)
     return result
예제 #14
0
파일: Token.py 프로젝트: Njsao/FlaskServer
 def login(self, token):
     connection = MySQLConn()
     query = """ SELECT count(*) FROM tbl_refresh_token where token='%s' and expair_at > now();""" % (token)
     connection.open()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_session().execute(query)
     result = connection.get_session().fetchone()[0] == 1
     connection.get_connection().commit()
     connection.close()
     print("> > > login result: %s \n" % result)
     return result
예제 #15
0
파일: User.py 프로젝트: Njsao/FlaskServer
 def update_status(self, user):
     connection = MySQLConn()
     query = """ update tbl_user_dettail set active = '%s' where id_user = %s;""" % (
         user.get("active"),
         user.get("id_user"),
     )
     connection.open()
     connection.get_session().execute(query)
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     connection.close()
예제 #16
0
 def insert(self, message):
     connection = MySQLConn()
     query = """INSERT INTO tbl_message (sender_id, receiver_id, text, sendet_at, isread)
               VALUES ( '%s' , '%s', '%s', '%s', '%s');""" % (message.get("sender_id"), message.get("receiver_id"),
                                                              message.get("text"), message.get("sendet_at"),
                                                              message.get("isread"))
     connection.open()
     connection.get_session().execute(query)
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     connection.close()
예제 #17
0
 def get_number_messages_not_read(self, message):
     connection = MySQLConn()
     query = """SELECT count(DISTINCT sender_id) FROM tbl_message WHERE receiver_id = '%s' and isread = 0; """ % \
             (message.get("receiver_id"))
     connection.open()
     connection.get_session().execute(query)
     message_data = connection.get_session().fetchone()[0]
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     connection.close()
     return message_data
예제 #18
0
파일: Client.py 프로젝트: Njsao/FlaskServer
 def login(self, random_id, secret_id, grant_types):
     connection = MySQLConn()
     query = """ SELECT count(*) FROM tbl_client where random_id='%s' and secret_id='%s' and grant_types='%s'; """ % \
             (random_id, secret_id, grant_types)
     connection.open()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_session().execute(query)
     result = connection.get_session().fetchone()[0] == 1
     connection.get_connection().commit()
     connection.close()
     print("> > > login result: %s \n" % result)
     return result
예제 #19
0
 def ceck_feedback(self, feedback):
     connection = MySQLConn()
     query = """ SELECT count(*) FROM tbl_feedback where user_id='%s' and target_id='%s' and
                 Datediff(now(), date_feedback) = 0; """ % (feedback.get("user_id"), feedback.get("target_id"))
     connection.open()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_session().execute(query)
     result = connection.get_session().fetchone()[0] == 0
     connection.get_connection().commit()
     connection.close()
     print("> > > login result: %s \n" % result)
     return result
예제 #20
0
파일: Follow.py 프로젝트: Njsao/FlaskServer
 def is_followed(self, follow):
     connection = MySQLConn()
     query = """SELECT count(user_id) FROM tbl_follow WHERE user_id = '%s' and target_id = '%s';""" % (
         follow.get("user_id"),
         follow.get("target_id"))
     connection.open()
     connection.get_session().execute(query)
     result = connection.get_session().fetchone()[0] == 1
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     connection.close()
     return result
예제 #21
0
파일: Token.py 프로젝트: Njsao/FlaskServer
 def _update_access(self, user):
     connection = MySQLConn()
     new_date = datetime.datetime.now() + datetime.timedelta(days=60)
     query = """ update tbl_access_token set expair_at = '%s' where user_id = %s;""" % (
         new_date,
         user.get("id_user"),
     )
     connection.open()
     connection.get_session().execute(query)
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     connection.close()
예제 #22
0
파일: User.py 프로젝트: Njsao/FlaskServer
 def _update_user(self, user):
     connection = MySQLConn()
     query = """ update tbl_user set name = '%s', surname = '%s', password = '******' where id_user = %s;""" % (
         user.get("name"),
         user.get("surname"),
         user.get("password"),
         user.get("id_user"),
     )
     connection.open()
     connection.get_session().execute(query)
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     connection.close()
예제 #23
0
파일: Token.py 프로젝트: Njsao/FlaskServer
 def insert_access_token(self, token):
     connection = MySQLConn()
     query = """INSERT INTO tbl_access_token ( expair_at , token, client_id, user_id)
               VALUES ( '%s' , '%s', '%s', '%s' );""" % (
         token.get("expair_at"),
         token.get("token"),
         token.get("client_id"),
         token.get("user_id"),
     )
     connection.open()
     connection.get_session().execute(query)
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     connection.close()
예제 #24
0
파일: Client.py 프로젝트: Njsao/FlaskServer
 def _create_table_client(self):
     connection = MySQLConn()
     query = """CREATE TABLE IF NOT EXISTS %s (
               %s INT NOT NULL AUTO_INCREMENT,
               %s VARCHAR(55) NOT NULL,
               %s VARCHAR(55) NOT NULL,
               %s VARCHAR(55) NOT NULL,
               PRIMARY KEY (%s));""" % (self.__tbl_name, self.__id_client, self.__random_id, self.__secret_id,
                                        self.__grant_types, self.__id_client)
     connection.open()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_session().execute(query)
     connection.get_connection().commit()
     connection.close()
예제 #25
0
파일: Token.py 프로젝트: Njsao/FlaskServer
 def get_tokens_by_user(self, user):
     connection = MySQLConn()
     query = """ SELECT tbl_refresh_token.token, tbl_access_token.token FROM tbl_refresh_token, tbl_access_token
                 WHERE tbl_refresh_token.user_id = '%s' and tbl_access_token.user_id ='%s' ;""" % (
         user.get("id_user"),
         user.get("id_user"),
     )
     connection.open()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_session().execute(query)
     result = connection.get_session().fetchone()
     connection.get_connection().commit()
     connection.close()
     print("> > > refresh token result: %s \n> > > access token result: %s \n" % (result[0], result[1]))
     return result[0], result[1]
예제 #26
0
파일: Notice.py 프로젝트: Njsao/FlaskServer
 def _create_table(self):
     connection = MySQLConn()
     query = """CREATE TABLE IF NOT EXISTS tbl_notice(
               id_notice INT NOT NULL AUTO_INCREMENT,
               user_id INT NOT NULL,
               text VARCHAR(250) NOT NULL,
               written_at DATETIME NOT NULL,
               PRIMARY KEY (id_notice),
               INDEX user_id_ind (user_id),
               FOREIGN  KEY (user_id)
               REFERENCES tbl_user(id_user));"""
     connection.open();
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_session().execute(query)
     connection.get_connection().commit()
     connection.close()
예제 #27
0
파일: User.py 프로젝트: Njsao/FlaskServer
 def _update_dettail(self, user):
     connection = MySQLConn()
     query = """ update tbl_user_dettail set bday = '%s', role = '%s', city = '%s', rate = '%s', active = '%s',
     description = '%s', avatar = '%s' where id_user = %s;""" % (
         user.get("bday"),
         user.get("role"),
         user.get("city"),
         user.get("rate"),
         user.get("active"),
         user.get("description"),
         user.get("avatar"),
         user.get("id_user"),
     )
     connection.open()
     connection.get_session().execute(query)
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_connection().commit()
     connection.close()
예제 #28
0
파일: User.py 프로젝트: Njsao/FlaskServer
 def _insert_dettails(self, user):
     connection = MySQLConn()
     query = """INSERT INTO %s VALUES ( '%d', '%s', '%s', '%s', '%f', '%d', '%s', '%d' );""" % (
         self.__tbl_user_dettail,
         user.get("id_user"),
         user.get("bday"),
         user.get("role"),
         user.get("city"),
         user.get("rate"),
         user.get("active"),
         user.get("description"),
         user.get("avatar"),
     )
     connection.open()
     connection.get_session().execute(query)
     connection.get_connection().commit()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.close()
예제 #29
0
파일: Follow.py 프로젝트: Njsao/FlaskServer
 def _create_table(self):
     connection = MySQLConn()
     query = """CREATE TABLE IF NOT EXISTS tbl_follow(
               id_follow INT NOT NULL AUTO_INCREMENT,
               user_id INT NOT NULL,
               target_id INT NOT NULL,
               date_follow DATE NOT NULL,
               PRIMARY KEY (id_follow),
               INDEX user_id_ind (user_id),
               INDEX target_id_ind (target_id),
               FOREIGN  KEY (user_id)
               REFERENCES tbl_user(id_user),
               FOREIGN KEY (target_id)
               REFERENCES tbl_user(id_user));"""
     connection.open();
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_session().execute(query)
     connection.get_connection().commit()
     connection.close()
예제 #30
0
파일: Token.py 프로젝트: Njsao/FlaskServer
 def _create_table_refresh(self):
     connection = MySQLConn()
     query = """CREATE TABLE IF NOT EXISTS tbl_refresh_token(
               id_token INT NOT NULL AUTO_INCREMENT,
               expair_at DATETIME NOT NULL,
               token VARCHAR(55) NOT NULL,
               client_id INT NOT NULL,
               user_id INT NOT NULL,
               PRIMARY KEY (id_token),
               INDEX client_id_ind (client_id),
               INDEX user_id_ind (user_id),
               FOREIGN  KEY (client_id)
               REFERENCES tbl_client(id_client),
               FOREIGN KEY (user_id)
               REFERENCES tbl_user(id_user));"""
     connection.open()
     print("#DB#    -execute-\n")
     print("#DB#    %s \n" % (query))
     connection.get_session().execute(query)
     connection.get_connection().commit()
     connection.close()