TrZJ9mS5

1import mysql.connector
2
3
4class ShowQuery:
5    def __init__(self, *lines):
6        self.lines = lines
7        print(self.__str__())
8
9    def __str__(self):
10        query = "\nMySQL Query Generated:\n"
11        for line in self.lines:
12            query += line + '\n'
13        return query + "--------------------\n"
14
15
16class Database:
17    def __init__(self, use_database='', password='', host='localhost', user='root', overwrite=False):
18        self.host = host
19        self.user = user
20        self.use_database = use_database
21
22        connection_dict = {
23            'host': self.host,
24            'user': self.user,
25            'passwd': password,
26        }
27        self.mydb = mysql.connector.connect(**connection_dict)
28        self.cursor = self.mydb.cursor(dictionary=True)
29
30        if overwrite:
31            self.__remove_database(self.use_database)
32        self.__create_database(self.use_database)
33        use_command = "USE {};".format(self.use_database)
34        self.cursor.execute(use_command)
35        print("Connection achieved. Using database {}.".format(self.use_database))
36
37    def __str__(self):
38        return 'User \'{}\' connected at \'{}\', using database \'{}\'.'.format(
39            self.user, self.host, self.use_database
40        )
41
42    def __create_database(self, name, show_query=False, sq=False):
43        create_command = "CREATE DATABASE IF NOT EXISTS {};".format(name)
44        self.use_database = name
45        self.cursor.execute(create_command)
46        if show_query or sq:
47            ShowQuery(create_command, self.use_command)
48
49    def __remove_database(self, name, show_query=False):
50        remove_command = "DROP DATABASE {};".format(name)
51        self.cursor.execute(remove_command)
52        if show_query:
53            ShowQuery(remove_command)
54
55    def create_table(self, table_name, column_dict={}, show_query=False, sq=False, **kwargs):
56        self.cursor.execute("USE {};".format(self.use_database))
57        column_dict.update(kwargs)
58        create_command = "CREATE TABLE IF NOT EXISTS {} (\n".format(table_name)
59        for column_name, column_type in column_dict.items():
60            create_command += "{} {},\n".format(column_name, column_type)
61        create_command = create_command.strip(',\n')
62        create_command += "\n);"
63        if show_query or sq:
64            ShowQuery(create_command)
65        self.cursor.execute(create_command)
66
67
68    def show_tables(self, table_name='', all=False, show_query=False, sq=False):
69        # TODO single table viewing
70        show_command = 'SHOW TABLES;'
71        self.cursor.execute(show_command)
72        if show_query or sq:
73            ShowQuery(show_command)
74        for table in self.cursor:
75            print(table)
76
77    def execute(self, command):
78        cursor = self.mydb.cursor()
79        return cursor.execute(command)