PdbYCZDi

1
2import sqlite3
3from http.server import HTTPServer
4from http.server import CGIHTTPRequestHandler
5import cgi
6
7try:
8    kit = '''CREATE TABLE IF NOT EXISTS kit  (
9                            id_kit INTEGER PRIMARY KEY,
10                            id_CPU INTEGER,
11                            id_videocard INTEGER,
12                            id_motherboard INTEGER,
13                            id_power_unit INTEGER,
14                            id_RAM INTEGER,
15                            id_storage INTEGER,
16                            OS TEXT,
17                            FOREIGN KEY (id_CPU) REFERENCES CPU(id_CPU),
18                            FOREIGN KEY (id_videocard) REFERENCES videocard(id_videocard),
19                            FOREIGN KEY (id_motherboard) REFERENCES motherboard(id_motherboard),
20                            FOREIGN KEY (id_power_unit) REFERENCES power_unit(id_power_unit),
21                            FOREIGN KEY (id_RAM) REFERENCES RAM(id_RAM),
22                            FOREIGN KEY (id_storage) REFERENCES storage(id_storage)
23                            );'''
24    CPU = '''CREATE TABLE IF NOT EXISTS CPU (
25                                id_CPU INTEGER PRIMARY KEY,
26                                model TEXT NOT NULL UNIQUE,
27                                price INTEGER NOT NULL,
28                                cores INTEGER NOT NULL,
29                                frequency INTEGER NOT NULL
30                                );'''
31    videocard = '''CREATE TABLE IF NOT EXISTS videocard (
32                                id_videocard INTEGER PRIMARY KEY,
33                                model TEXT NOT NULL UNIQUE,
34                                price INTEGER NOT NULL,
35                                storage INTEGER NOT NULL
36                                );'''
37    motherboard = '''CREATE TABLE IF NOT EXISTS motherboard (
38                                id_motherboard INTEGER PRIMARY KEY,
39                                model TEXT NOT NULL UNIQUE,
40                                price INTEGER NOT NULL,
41                                ram_limit INTEGER NOT NULL,
42                                connectors INTEGER NOT NULL
43                                );'''
44    power_unit = '''CREATE TABLE IF NOT EXISTS power_unit (
45                                id_power_unit INTEGER PRIMARY KEY,
46                                model TEXT NOT NULL UNIQUE,
47                                price INTEGER NOT NULL,
48                                power INTEGER NOT NULL
49                                );'''
50    RAM = '''CREATE TABLE IF NOT EXISTS RAM (
51                                id_RAM INTEGER PRIMARY KEY,
52                                model TEXT NOT NULL UNIQUE,
53                                price INTEGER NOT NULL,
54                                frequency INTEGER NOT NULL
55                                );'''
56    storage = '''CREATE TABLE IF NOT EXISTS storage (
57                                id_storage INTEGER PRIMARY KEY,
58                                model TEXT NOT NULL UNIQUE,
59                                price INTEGER NOT NULL,
60                                GB INTEGER NOT NULL
61                                );'''
62    sqlite_connection = sqlite3.connect('sqlite_python.db')
63    cursor = sqlite_connection.cursor()
64    print("База данных подключена к SQLite")
65    cursor.execute(kit)
66    cursor.execute(CPU)
67    cursor.execute(videocard)
68    cursor.execute(motherboard)
69    cursor.execute(power_unit)
70    cursor.execute(RAM)
71    cursor.execute(storage)
72    sqlite_connection.commit()
73    print("Таблица SQLite создана")
74    sqlite_insert_CPU = """INSERT INTO CPU
75                          (model, price, cores, frequency)  VALUES  ('1abc', 1200, 4, 8709)"""
76    count = cursor.execute(sqlite_insert_CPU)
77    sqlite_insert_videocard = """INSERT INTO videocard
78                        (model, price, storage)  VALUES  ('1def', 1500, 45)"""
79    count = cursor.execute(sqlite_insert_videocard)
80    sqlite_insert_motherboard = """INSERT INTO motherboard
81                        (model, price, ram_limit, connectors)  VALUES  ('1ghi', 2500, 8, 2)"""
82    count = cursor.execute(sqlite_insert_motherboard)
83    sqlite_insert_power_unit = """INSERT INTO power_unit
84                        (model, price, power)  VALUES  ('37804f4FN', 4000, 2500)"""
85    count = cursor.execute(sqlite_insert_power_unit)
86    sqlite_insert_RAM = """INSERT INTO RAM
87                        (model, price, frequency)  VALUES  ('g04d437', 2500, 9600)"""
88    count = cursor.execute(sqlite_insert_RAM)
89    sqlite_insert_storage = """INSERT INTO storage
90                        (model, price, GB)  VALUES  ('g4d04052', 1999, 45)"""
91    count = cursor.execute(sqlite_insert_storage)
92
93    count = cursor.execute("""INSERT INTO kit (id_CPU, id_videocard, id_motherboard)
94    SELECT id_CPU, id_videocard, id_motherboard
95    FROM CPU, videocard, motherboard
96    WHERE CPU.model = '1abc' AND videocard.model = '1def' AND motherboard.model = '1ghi'"""
97        )
98    
99    print("Запись успешно вставлена ​​в таблицу sqlitedb", cursor.rowcount)
100
101    sqlite_connection.commit()
102
103    cursor.execute("SELECT * FROM RAM")
104
105    rows = cursor.fetchall()
106
107    for row in rows:
108        print("row")
109        print(row)
110
111    cursor.close()
112
113
114    server_address = ("localhost", 8000)
115    http_server = HTTPServer(server_address, CGIHTTPRequestHandler)
116    http_server.serve_forever()
117
118    handler = HTTPServer.CGIHTTPRequestHandler
119    handler.cgi_directories = [""]
120    handler.cgi_directories = ["/"]
121
122    
123    form = cgi.FieldStorage()
124    text1 = form.getfirst("Author", "не задано")
125    text2 = form.getfirst("Book", "не задано")
126    print("Content - type: text/html\n")
127    print("""<!DOCTYPE HTML>
128        <html>
129        <head>
130        <meta charset = "cp1251">
131        <title>storage</title>
132        </head>
133        <body>
134        <form action = "/cgi/bin/view02.py">
135        Model <input type = "text" name = "model">
136        Price <input type = "text" name = "model">
137        <div>
138            <input type="checkbox"  name="GB" value="4GB">
139            <label for="4GB">4 GB</label>
140        </div>
141        <div>
142            <input type="checkbox" name="GB" value="8GB">
143            <label for="8GB">8 GB</label>
144        </div>
145        <div>
146            <input type="checkbox"  name="GB" value="16GB">
147            <label for="16GB">16 GB</label>
148        </div>
149        <input type = "submit">
150        </form>
151        </body>
152        </html>""")
153
154except sqlite3.Error as error:
155    print("Ошибка при подключении к sqlite", error)
156finally:
157    if (sqlite_connection):
158        sqlite_connection.close()
159        print("Соединение с SQLite закрыто")