yQ0ZHDfr

1# Created by Brandon
2import psycopg2 as I_sql
3
4class SQL_Query:
5    con = None
6    noFetch = ['DELETE', 'CREATE', 'UPDATE', 'INSERT']
7    def __init__(self, con):
8        self.con = con
9
10    def execute(self, sql: str):
11        result = False
12        cur = None
13        print(sql)
14        if self.con is not None:
15            try:
16                cur = self.con.cursor()
17                cur.execute(sql)
18
19                hasNoFetch = False
20                for word in self.noFetch:
21                    if word in sql:
22                        hasNoFetch = True
23                        break
24
25                if not hasNoFetch:
26                    result = cur.fetchall()         
27            except (Exception, I_sql.DatabaseError) as error:
28                print("SQL Error Occured >> ")
29                print(error)
30            finally:
31                if cur is not None:
32                    cur.close()
33        return result
34
35
36class SQL:
37    con = None
38    host = ""
39    username = ""
40    password = ""
41    databaseTarget = ""
42    schemaTarget = ""
43    def __init__(self):
44        pass
45
46    def connect(self, host: str, user: str, password: str, dbName = ""):
47        self.host = host
48        self.username = user
49        self.password = password
50        print("Connecting to PostgreSQL")
51        try:
52            self.con = I_sql.connect('user='+user+' password='+password)
53            print("Connected to PostgreSQL")
54            self.con.set_isolation_level(I_sql.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
55            if dbName != "":
56                self.useDatabase(dbName)
57        except (Exception, I_sql.DatabaseError) as error:
58                print("SQL Connection Error Occured >> ")
59                print(error)
60        return self.con
61
62    def close(self):
63        if self.con is not None:
64            self.con.close()
65            print("Disconnecting from PostgreSQL")
66
67    def query(self, sql: str):
68        if self.con is not None:
69            if self.con.closed == 0:
70                query = SQL_Query(self.con)
71                return query.execute(sql)
72            elif self.con.closed == 1:
73                print("Failed to Execute: " + sql + "\nError: No SQL Connection.")
74        return False
75
76    def useDatabase(self, database: str):
77        self.createDatabase(database)
78        print("Connecting to PostgreSQL > Database > " + database)
79        try:
80            self.con = I_sql.connect('dbname='+ database +' user='+ self.username +' password='+ self.password)
81            print("Connected to PostgreSQL > Database > " + database)
82            self.con.set_isolation_level(I_sql.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
83            self.databaseTarget = database
84        except (Exception, I_sql.DatabaseError) as error:
85                print("SQL Connection Error Occured >> ")
86                print(error)
87        return self.con
88
89    def useSchema(self, schema: str):
90        self.schemaTarget = schema
91        return self.con
92
93    def get(self, table: str, where: list):
94        whereClasues = ' '.join(where)
95        return self.query("SELECT * FROM "+ self.__targetTable(table) + ((" WHERE " + whereClasues) if len(where) != 0 else ""))
96
97    def deleteTableRow(self, table: str, where: list):
98        whereClasues = ' AND '.join(where)
99        return self.query("DELETE FROM " + self.__targetTable(table) + " WHERE " + whereClasues)
100
101    def deleteTable(self, table: str):
102        return self.query("DROP TABLE IF EXISTS " + self.__targetTable(table))
103
104    def deleteSchema(self, schema: str):
105        if self.schemaTarget == schema:
106            self.schemaTarget = ""
107        return self.query("DROP SCHEMA IF EXISTS " + schema)
108
109    def deleteDatabase(self, database: str):
110        self.connect(self.host, self.username, self.password)
111        return self.query("DROP DATABASE IF EXISTS " + database)
112
113    def insert(self, table: str, fields: dict):
114        field_keys = ', '.join(fields.keys())
115        _fields = '\',\''.join(fields.values())
116        return self.query("INSERT INTO " + self.__targetTable(table) + "("+ field_keys +") VALUES ('"+ _fields +"')")
117
118    def createTable(self, name: str, fields: list):
119        field_keys = ', '.join(fields)
120        return self.query("CREATE TABLE IF NOT EXISTS " + self.__targetTable(name) + " ("+ field_keys + ")")
121
122    def createSchema(self, name: str):
123        if self.databaseTarget is not "":
124            self.useSchema(name)
125            if not self.checkIfSchemaExists(name):
126                return self.query("CREATE SCHEMA " + name)
127        return False
128
129    def createDatabase(self, name: str):
130        if not self.checkIfDatabaseExists(name):
131            return self.query("CREATE DATABASE " + name)
132            
133        return False
134
135    def update(self, table: str, where: list, fields: dict):
136        whereClasues = ' AND '.join(where)
137        _resolvedFields = []
138        for key in fields.keys():
139            _resolvedFields.append(key + " = '" + fields[key] + "'")
140        
141        _resolvedFieldsToStr = ', '.join(_resolvedFields)
142        
143        return self.query("UPDATE " + self.__targetTable(table) + " SET " + _resolvedFieldsToStr + ((" WHERE " + whereClasues) if len(where) != 0 else ""))
144
145    def checkIfDatabaseExists(self, name: str):
146        result = self.query(
147            """
148            SELECT EXISTS(
149                SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = lower('"""+ name +"""')
150            );
151            """)
152        value = str(result[0]).replace("(", "").replace(")", "").replace(",", "")
153        return True if 'true' in value.lower() else False 
154    
155    def checkIfSchemaExists(self, name: str):
156        result = self.query(
157            """
158            SELECT EXISTS(
159                SELECT schema_name FROM information_schema.schemata WHERE schema_name = '"""+ name +"""'
160            );
161            """)
162        value = str(result[0]).replace("(", "").replace(")", "").replace(",", "")
163        return True if 'true' in value.lower() else False 
164    
165    def __targetTable(self, table: str):
166        return (self.schemaTarget + "." + table) if self.schemaTarget != "" else table
167
168def main():
169    sql = SQL()
170    sql.connect("127.0.0.1", "postgres", "postgres", "google_codein")
171    sql.createSchema("postgresql")
172    sql.createTable("users", 
173        [
174            "id SERIAL",
175            "username TEXT",
176            "password TEXT",
177            "email TEXT",
178            "PRIMARY KEY(id)"     
179        ]
180    )
181    sql.createTable("users_groups", 
182        [
183            "id SERIAL",
184            "user_id INT",
185            "group_id INT",
186            "PRIMARY KEY(id)"     
187        ]
188    )
189    sql.createTable("groups", 
190        [
191            "id SERIAL",
192            "name TEXT",
193            "perms TEXT",
194            "PRIMARY KEY(id)"     
195        ]
196    )
197
198    sql.insert("groups", 
199        {"name": "founder", "perms": "[\"*\"]"})
200    sql.insert("groups", 
201        {"name": "admin", "perms": "[\"admin\", \"moderator\"]"})
202    sql.insert("groups", 
203        {"name": "moderator", "perms": "[\"moderator\"]"})
204
205    sql.insert("users", 
206        {"username": "John Doe", "password": "password123", "email": "JohnDoes@mail.com"})
207    sql.insert("users_groups", 
208        {"user_id": "1", "group_id": "1"})
209    
210    sql.insert("users", 
211        {"username": "Jan Doe", "password": "pass123", "email": "jannydoey2234@mymail.com"})
212    
213    sql.update("users", ["username = 'John Doe'"], {"username" : "Danny Doe"})
214    sql.deleteTableRow("users", ["username = 'Jan Doe'"])
215
216    # SQL Results: 
217    # All John Doe names should be Danny Doe.
218    # And No Jan Doe names.
219    
220if __name__ == '__main__':
221    main()