GxNQk1kV

1import sqlite3
2
3
4class DataBase:
5    def __init__(self, data_base_name: str):
6        self.data_base_name = data_base_name
7        self.connection = sqlite3.connect(self.data_base_name)
8        self.db_cursor = self.connection.cursor()
9        self.tables = []
10
11    def __init__(self, data_base_connection: sqlite3.Connection, db_cursor: sqlite3.Cursor):
12        self.connection = data_base_connection
13        self.db_cursor = db_cursor
14        self.tables = []
15
16    def create_table(self, table_name: str, columns: [], description: [], foreign=None):
17        query = "CREATE TABLE IF NOT EXISTS " + table_name + "("
18        size = len(columns)
19
20        if size != len(description):
21            return
22
23        for i in range(size):
24            query += columns[i] + " " + description[i] + ","
25        query = query[:-1]
26        query += ")"
27
28        if foreign is None:
29            foreign = []
30        else:
31            query = query[:-1]
32            for i in foreign:
33                query += "," + i
34            query += ")"
35
36        try:
37            self.db_cursor.execute(query)
38            self.tables.append(table_name)
39        except:
40            return
41
42    def insert(self, table_name: str, columns: [], values: []):
43        if table_name not in self.tables:
44            return
45        query = "INSERT INTO " + table_name + "("
46        for i in columns:
47            query += i + ","
48
49        query = query[:-1]
50        query += ") VALUES("
51
52        for i in values:
53            query += i + ","
54
55        query = query[:-1]
56        query += ")"
57
58        try:
59            self.db_cursor.execute(query)
60            self.connection.commit()
61        except:
62            return
63
64    def select_all(self, table_name):
65        self.db_cursor.execute("SELECT * FROM " + table_name)
66        return self.db_cursor.fetchall()
67
68    def drop_table(self, table_name: str):
69        try:
70            self.db_cursor.execute("DROP TABLE " + table_name)
71            self.tables.remove(table_name)
72        except:
73            return
74
75    def get_all_tables(self):
76        return self.tables
77
78    def __del__(self):
79        try:
80            self.db_cursor.close()
81        except:
82            return
83
84
85
86import sqlite3
87import DataBase
88
89
90def delete(db_cursor, table_name):
91    db_cursor.execute("DROP TABLE IF EXISTS " + table_name)
92
93
94def insert(db_cursor, table_name, *data) -> None:
95    query = "INSERT INTO " + table_name + " VALUES(NULL, "
96    for i in data:
97        query += i + ", "
98    query = query[:-2]
99    query += ")"
100    db_cursor.execute(query)
101
102
103conn = sqlite3.connect("forum.sqlite")
104
105c = conn.cursor()
106
107db = DataBase.DataBase(conn, c)
108
109db.create_table("users",
110                ["id_user", "name", "email", "password"],
111                ["INTEGER PRIMARY KEY AUTOINCREMENT", "VARCHAR(15)", "VARCHAR(20) UNIQUE", "VARCHAR(7)"]
112                )
113
114db.create_table("topics",
115                ["id_topic", "topic_name", "id_author"],
116                ["INTEGER PRIMARY KEY AUTOINCREMENT", "VARCHAR(100) NOT NULL", "INTEGER(10) NOT NULL"],
117                ["FOREIGN KEY (id_author) REFERENCES users(id_user)"])
118
119db.create_table("posts",
120                ["id_post", "message", "id_author", "id_topic"],
121                ["INTEGER PRIMARY KEY AUTOINCREMENT", "VARCHAR(50) NOT NULL", "INTEGER(10) NOT NULL",
122                 "INTEGER(10) NOT NULL"],
123                ["FOREIGN KEY (id_author) REFERENCES topics(id_author)",
124                 "FOREIGN KEY (id_topic) REFERENCES topics(id_topic)"])
125
126db.insert("users", ["name", "email", "password"], ["'vasya'", "'vasya2020@mail.ru'", "'80040'"])
127
128print(db.select_all("users"))