File size: 5,546 Bytes
0abcb4b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1ef5f8b
 
0abcb4b
1ef5f8b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
243d5f5
1ef5f8b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
243d5f5
1ef5f8b
 
 
 
 
 
 
243d5f5
 
1ef5f8b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
243d5f5
 
1ef5f8b
 
 
 
 
 
 
 
243d5f5
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1ef5f8b
 
243d5f5
 
1ef5f8b
 
243d5f5
 
1ef5f8b
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
import paramiko
import pymysql
from sshtunnel import SSHTunnelForwarder

# SSH settings
ssh_host = '129.159.146.88'
ssh_user = 'ubuntu'
ssh_key_path = 'C:/Users/kerts/OneDrive/Documents/Keys/Ubuntu_Oracle/ssh-key-2023-02-12.key'

# MySQL settings
mysql_host = 'localhost'  # because we will connect through the SSH tunnel
mysql_port = 3306  # the default MySQL port
mysql_user = 'root'
mysql_password = 'naP2tion'
mysql_db = 'warbot'
ssh_port = 22
dbTableName = 'conversations' # messages data table

class DataBase():
    # manages mySQL connection and send-receive
    def __init__(db, ssh_key_path = ssh_key_path, ssh_host=ssh_host, ssh_port=ssh_port, ssh_user=ssh_user,
                 mysql_host=mysql_host,mysql_port=mysql_port,mysql_user=mysql_user,mysql_password=mysql_password,mysql_db=mysql_db):
        # create an SSH client and load the private key
        db.ssh_client = paramiko.SSHClient()
        db.ssh_client.load_system_host_keys()
        db.ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        db.private_key = paramiko.RSAKey.from_private_key_file(ssh_key_path)
        db.ssh_key_path = ssh_key_path
        db.ssh_host=ssh_host
        db.ssh_port=ssh_port
        db.ssh_user=ssh_user
        db.mysql_host=mysql_host
        db.mysql_port=mysql_port
        db.mysql_user=mysql_user
        db.mysql_password=mysql_password
        db.mysql_db=mysql_db
    def getmessages(db,username = ""):
        # get the entire conversation with the specific user
        with SSHTunnelForwarder(
                (db.ssh_host, db.ssh_port),
                ssh_username=db.ssh_user,
                ssh_pkey=db.private_key,
                remote_bind_address=(db.mysql_host, db.mysql_port)) as tunnel:
            # connect to the MySQL server through the SSH tunnel
            mysql_conn = pymysql.connect(
                host='localhost', # will be due to the SSH tunneling issue
                port=tunnel.local_bind_port,
                user=db.mysql_user,
                password=db.mysql_password,
                db=db.mysql_db
            )

            # send a query to the MySQL database and print the response table
            with mysql_conn.cursor() as cursor:
                query = f'SELECT * FROM {dbTableName} WHERE username = "{username}";'
                cursor.execute(query)
                rows = cursor.fetchall()

                messages = [(message[2],message[3]) for message in rows]

            # close the MySQL connection
            mysql_conn.close()

        # close the SSH client
        db.ssh_client.close()
        return messages
    def setmessages(db,username, message_text="", bot_reply=""):
        # Adding the record into the database
        with SSHTunnelForwarder(
                (db.ssh_host, db.ssh_port),
                ssh_username=db.ssh_user,
                ssh_pkey=db.private_key,
                remote_bind_address=(db.mysql_host, db.mysql_port)) as tunnel:
            # connect to the MySQL server through the SSH tunnel
            mysql_conn = pymysql.connect(
                host='localhost', # will be due to the SSH tunneling issue
                port=tunnel.local_bind_port,
                user=db.mysql_user,
                password=db.mysql_password,
                db=db.mysql_db
            )

            # send a query to the MySQL database and print the response table
            with mysql_conn.cursor() as cursor:
                query = f"INSERT INTO {dbTableName} (username,message_text,bot_reply) " \
                        f"VALUES ('{username}','{message_text}','{bot_reply}');"
                cursor.execute(query)
                mysql_conn.commit()

            # close the MySQL connection
            mysql_conn.close()

        # close the SSH client
        db.ssh_client.close()
    def cleanup(db, username = "", remaining_messages = 3):
        # Cleanup the records, except the last N rows
        with SSHTunnelForwarder(
                (db.ssh_host, db.ssh_port),
                ssh_username=db.ssh_user,
                ssh_pkey=db.private_key,
                remote_bind_address=(db.mysql_host, db.mysql_port)) as tunnel:
            # connect to the MySQL server through the SSH tunnel
            mysql_conn = pymysql.connect(
                host='localhost',  # will be due to the SSH tunneling issue
                port=tunnel.local_bind_port,
                user=db.mysql_user,
                password=db.mysql_password,
                db=db.mysql_db
            )

            # send a query to the MySQL database to delete the records except the last ones
            with mysql_conn.cursor() as cursor:

                query = f"DELETE FROM {dbTableName} WHERE username = '{username}' AND id NOT IN " \
                        f"(SELECT id FROM (SELECT id FROM (SELECT id FROM {dbTableName} " \
                        f"WHERE username = '{username}' ORDER BY id DESC LIMIT {remaining_messages}) " \
                        f"subquery) subsubquery)"
                cursor.execute(query)
                mysql_conn.commit()

            # close the MySQL connection
            mysql_conn.close()

        # close the SSH client
        db.ssh_client.close()


if __name__ == '__main__':
    # This is for testing purpose only:
    username = 'user1'

    db = DataBase()
    db.setmessages(username='user2',message_text='some message',bot_reply='some reply')
    #db.cleanup(username='user2',remaining_messages=1)
    messages = db.getmessages(username)
    print(messages)