utils.db

View Source

 1import os
 2import time
 3import mysql.connector
 4
 5
 6class DbConnector:
 7    """
 8    An Object used to connect to a Database and perform necessary functions like creating tables, reading and writing
 9    to the database.
10    """
11    def __init__(self):
12        self.connector = None
13
14    def connect(self):
15        """
16        Function to Open a database connection
17        :return:
18        """
19        retry = 10
20        while retry > 0 and self.connector is None:
21            try:
22                self.connector = mysql.connector.connect(
23                    host=os.getenv("DB_HOST"), port=int(os.getenv("DB_PORT")),
24                    user=os.getenv("DB_USER"), password=os.getenv("DB_PASSWORD"))
25
26            except Exception as error:
27                print("Failed to connect to the database: {} retry count:{}".format(error,retry))
28            finally:
29                time.sleep(5)
30                retry -= 1
31
32    def create_tables(self):
33        """
34        Function to create and insert tables in the database
35        :return:
36        """
37        try:
38            cursor = self.connector.cursor()
39            cursor.execute("CREATE DATABASE discorddb")
40            print("Created database discorddb")
41            # Create tables and insert data(if any)
42            cursor.execute("CREATE TABLE discorddb.pwords (server_name NVARCHAR(255), word NVARCHAR(255))")
43            cursor.execute(
44                "CREATE TABLE discorddb.user_activity (user_id NVARCHAR(255), server_name NVARCHAR(255), offense_count INT DEFAULT 0, "
45                "apology_count INT DEFAULT 0, is_banned TINYINT DEFAULT 0)")
46            print("Created Tables for the database")
47            self.connector.commit()
48            print("Commited the changes")
49
50
51            print("Inserted records in the database")
52            self.connector.commit()
53
54        except Exception as error:
55            print("Failed to get record from MySQL table: {}".format(error))
56        finally:
57            cursor.close()
58            return True
59
60    def executequery(self, query, cond):
61        """
62        Function to get data from query in the Database
63        :return:
64        """
65        try:
66            data = None
67            cursor = self.connector.cursor()
68            cursor.execute(query, cond)
69            data = cursor.fetchall()
70        except Exception as error:
71            print("Failed to get record from MySQL table: {}".format(error))
72        finally:
73            cursor.close()
74            return data
75
76    def close(self):
77        """
78            disconnect from server
79        """
80        try:
81            if self.connector.is_connected():
82                self.connector.close()
83        except Exception as error:
84            print("Failed to connect to the database: {}".format(error))
85        return True

class DbConnector: View Source

 7class DbConnector:
 8    """
 9    An Object used to connect to a Database and perform necessary functions like creating tables, reading and writing
10    to the database.
11    """
12    def __init__(self):
13        self.connector = None
14
15    def connect(self):
16        """
17        Function to Open a database connection
18        :return:
19        """
20        retry = 10
21        while retry > 0 and self.connector is None:
22            try:
23                self.connector = mysql.connector.connect(
24                    host=os.getenv("DB_HOST"), port=int(os.getenv("DB_PORT")),
25                    user=os.getenv("DB_USER"), password=os.getenv("DB_PASSWORD"))
26
27            except Exception as error:
28                print("Failed to connect to the database: {} retry count:{}".format(error,retry))
29            finally:
30                time.sleep(5)
31                retry -= 1
32
33    def create_tables(self):
34        """
35        Function to create and insert tables in the database
36        :return:
37        """
38        try:
39            cursor = self.connector.cursor()
40            cursor.execute("CREATE DATABASE discorddb")
41            print("Created database discorddb")
42            # Create tables and insert data(if any)
43            cursor.execute("CREATE TABLE discorddb.pwords (server_name NVARCHAR(255), word NVARCHAR(255))")
44            cursor.execute(
45                "CREATE TABLE discorddb.user_activity (user_id NVARCHAR(255), server_name NVARCHAR(255), offense_count INT DEFAULT 0, "
46                "apology_count INT DEFAULT 0, is_banned TINYINT DEFAULT 0)")
47            print("Created Tables for the database")
48            self.connector.commit()
49            print("Commited the changes")
50
51
52            print("Inserted records in the database")
53            self.connector.commit()
54
55        except Exception as error:
56            print("Failed to get record from MySQL table: {}".format(error))
57        finally:
58            cursor.close()
59            return True
60
61    def executequery(self, query, cond):
62        """
63        Function to get data from query in the Database
64        :return:
65        """
66        try:
67            data = None
68            cursor = self.connector.cursor()
69            cursor.execute(query, cond)
70            data = cursor.fetchall()
71        except Exception as error:
72            print("Failed to get record from MySQL table: {}".format(error))
73        finally:
74            cursor.close()
75            return data
76
77    def close(self):
78        """
79            disconnect from server
80        """
81        try:
82            if self.connector.is_connected():
83                self.connector.close()
84        except Exception as error:
85            print("Failed to connect to the database: {}".format(error))
86        return True

An Object used to connect to a Database and perform necessary functions like creating tables, reading and writing to the database.

DbConnector() View Source

12    def __init__(self):
13        self.connector = None

def connect(self): View Source

15    def connect(self):
16        """
17        Function to Open a database connection
18        :return:
19        """
20        retry = 10
21        while retry > 0 and self.connector is None:
22            try:
23                self.connector = mysql.connector.connect(
24                    host=os.getenv("DB_HOST"), port=int(os.getenv("DB_PORT")),
25                    user=os.getenv("DB_USER"), password=os.getenv("DB_PASSWORD"))
26
27            except Exception as error:
28                print("Failed to connect to the database: {} retry count:{}".format(error,retry))
29            finally:
30                time.sleep(5)
31                retry -= 1

Function to Open a database connection

Returns

def create_tables(self): View Source

33    def create_tables(self):
34        """
35        Function to create and insert tables in the database
36        :return:
37        """
38        try:
39            cursor = self.connector.cursor()
40            cursor.execute("CREATE DATABASE discorddb")
41            print("Created database discorddb")
42            # Create tables and insert data(if any)
43            cursor.execute("CREATE TABLE discorddb.pwords (server_name NVARCHAR(255), word NVARCHAR(255))")
44            cursor.execute(
45                "CREATE TABLE discorddb.user_activity (user_id NVARCHAR(255), server_name NVARCHAR(255), offense_count INT DEFAULT 0, "
46                "apology_count INT DEFAULT 0, is_banned TINYINT DEFAULT 0)")
47            print("Created Tables for the database")
48            self.connector.commit()
49            print("Commited the changes")
50
51
52            print("Inserted records in the database")
53            self.connector.commit()
54
55        except Exception as error:
56            print("Failed to get record from MySQL table: {}".format(error))
57        finally:
58            cursor.close()
59            return True

Function to create and insert tables in the database

Returns

def executequery(self, query, cond): View Source

61    def executequery(self, query, cond):
62        """
63        Function to get data from query in the Database
64        :return:
65        """
66        try:
67            data = None
68            cursor = self.connector.cursor()
69            cursor.execute(query, cond)
70            data = cursor.fetchall()
71        except Exception as error:
72            print("Failed to get record from MySQL table: {}".format(error))
73        finally:
74            cursor.close()
75            return data

Function to get data from query in the Database

Returns

def close(self): View Source

77    def close(self):
78        """
79            disconnect from server
80        """
81        try:
82            if self.connector.is_connected():
83                self.connector.close()
84        except Exception as error:
85            print("Failed to connect to the database: {}".format(error))
86        return True

disconnect from server