GS8TsPxS

1â„–1
2import sqlite3
3import pprint
4conn = sqlite3.connect('TypeBase.sqlite')
5c = conn.cursor()
6pp = pprint.PrettyPrinter(indent=1, width=60, compact=False)
7
8conn.execute('''
9CREATE TABLE IF NOT EXISTS t1 (
10t text,
11nu numeric,
12i integer,
13r real,
14no blob)''')
15
16#1
17c.execute('''
18DELETE FROM t1;
19''')
20conn.commit()
21
22c.execute('''
23INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
24''')
25conn.commit()
26
27c.execute('''
28SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no)
29FROM t1;
30''')
31pp.pprint(c.fetchall())
32
33#2
34c.execute('''
35DELETE FROM t1;
36''')
37conn.commit()
38
39c.execute('''
40INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
41''')
42conn.commit()
43
44c.execute('''
45SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no)
46FROM t1;
47''')
48pp.pprint(c.fetchall())
49
50#3
51c.execute('''
52DELETE FROM t1;
53''')
54conn.commit()
55
56c.execute('''
57INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
58''')
59conn.commit()
60
61c.execute('''
62SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no)
63FROM t1;
64''')
65pp.pprint(c.fetchall())
66
67#4
68c.execute('''
69DELETE FROM t1;
70''')
71conn.commit()
72
73c.execute('''
74INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
75''')
76conn.commit()
77
78c.execute('''
79SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no)
80FROM t1;
81''')
82pp.pprint(c.fetchall())
83
84#5
85c.execute('''
86DELETE FROM t1;
87''')
88conn.commit()
89
90c.execute('''
91INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
92''')
93conn.commit()
94
95c.execute('''
96SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no)
97FROM t1;
98''')
99pp.pprint(c.fetchall())
100
101conn.close()
102
103
104â„–2
105import sqlite3
106import pprint
107conn = sqlite3.connect('TypeBase.sqlite')
108c = conn.cursor()
109pp = pprint.PrettyPrinter(indent=1, width=60, compact=False)
110
111conn.execute('''
112CREATE TABLE IF NOT EXISTS t1 (
113t text unique,
114nu numeric check(nu<100),
115i integer default 5000,
116r real not null check (r>=200),
117no blob
118check(r+i>=400))''')
119
120c.execute('''
121DELETE FROM t1;
122''')
123conn.commit()
124
125c.execute('''
126INSERT INTO t1 VALUES('abc', 150, 500000, 210, 'первая строка');
127''')
128conn.commit()
129
130c.execute('''
131SELECT *
132FROM t1;
133''')
134pp.pprint(c.fetchall())
135
136
137conn.close()
138
139
140â„–3
141import sqlite3
142import pprint
143conn = sqlite3.connect('Shop3.sqlite')
144c = conn.cursor()
145pp = pprint.PrettyPrinter(indent=1, width=60, compact=False)
146
147conn.execute('''
148create table IF NOT EXISTS customers (
149  id_customer integer,
150  name char(50) NOT NULL,
151  email char(50) NOT NULL UNIQUE,
152  PRIMARY KEY (id_customer))''')
153
154conn.execute('''
155create table IF NOT EXISTS vendors (
156  id_vendor integer,
157  name char(50) NOT NULL,
158  city char(30) NOT NULL,
159  address char(100) NOT NULL,
160  PRIMARY KEY (id_vendor))''')
161
162conn.execute('''
163create table IF NOT EXISTS sale (
164  id_sale integer,
165  id_customer int NOT NULL,
166  date_sale date NOT NULL DEFAULT GETDATE(),
167  PRIMARY KEY (id_sale),
168  FOREIGN KEY (id_customer) REFERENCES customers (id_customer))''')
169
170conn.execute('''
171create table IF NOT EXISTS incoming (
172  id_incoming integer,
173  id_vendor int NOT NULL,
174  date_incoming date NOT NULL DEFAULT GETDATE(),
175  PRIMARY KEY (id_incoming),
176  FOREIGN KEY (id_vendor) REFERENCES vendors (id_vendor))''')
177
178conn.execute('''
179create table IF NOT EXISTS products (
180  id_product integer,
181  name char(100) NOT NULL,
182  author  char(50) NOT NULL,
183  PRIMARY KEY (id_product))''')
184
185conn.execute('''
186create table IF NOT EXISTS prices (
187  id_product integer,
188  date_price_changes date NOT NULL,
189  price double NOT NULL CHECK (price>0),
190  PRIMARY KEY (id_product, date_price_changes),
191  FOREIGN KEY (id_product) REFERENCES products (id_product))''')
192
193conn.execute('''
194create table IF NOT EXISTS magazine_sales (
195  id_sale int NOT NULL,
196  id_product int NOT NULL,
197  quantity int NOT NULL,
198  PRIMARY KEY (id_sale, id_product),
199  FOREIGN KEY (id_sale) REFERENCES sale (id_sale),
200  FOREIGN KEY (id_product) REFERENCES products (id_product))''')
201
202conn.execute('''
203create table IF NOT EXISTS magazine_incoming (
204  id_incoming int NOT NULL,
205  id_product int NOT NULL,
206  quantity int NOT NULL,
207  PRIMARY KEY (id_incoming, id_product),
208  FOREIGN KEY (id_incoming) REFERENCES incoming (id_incoming),
209  FOREIGN KEY (id_product) REFERENCES products (id_product))''')
210
211c.execute('''
212INSERT INTO vendors  (name, city, address) VALUES
213 ('Вильямс', 'Москва', 'ул.Лесная, д.43'),
214 ('Дом печати', 'Минск', 'пр.Ф.Скорины, д.18'),
215 ('БХВ-Петербург', 'Санкт-Петербург', 'ул.Есенина, д.5');
216 ''')
217conn.commit()
218
219c.execute('''
220INSERT INTO customers  (name, email) VALUES
221 ('Иванов Сергей', 'sergo@mail.ru'),
222 ('Ленская Катя', 'lenskay@yandex.ru'),
223 ('Демидов Олег', 'demidov@gmail.ru'),
224 ('Афанасьев Виктор', 'victor@mail.ru'),
225 ('Пажская Вера', 'verap@rambler.ru');
226 ''')
227conn.commit()
228
229c.execute('''
230INSERT INTO products  (name, author) VALUES
231 ('Стихи о любви', 'Андрей Вознесенский'),
232 ('Собрание сочинений, том 2', 'Андрей Вознесенский'),
233 ('Собрание сочинений, том 3', 'Андрей Вознесенский'),
234 ('Русская поэзия', 'Николай Заболоцкий'),
235 ('Машенька', 'Владимир Набоков'),
236 ('Доктор Живаго', 'Борис Пастернак'),
237 ('Наши', 'Сергей Довлатов'),
238 ('Приглашение на казнь', 'Владимир Набоков'),
239 ('Лолита', 'Владимир Набоков'),
240 ('Темные аллеи', 'Иван Бунин'),
241 ('Дар', 'Владимир Набоков'),
242 ('Сын вождя', 'Юлия Вознесенская'),
243 ('Эмигранты', 'Алексей Толстой'),
244 ('Горе от ума', 'Александр Грибоедов'),
245 ('Анна Каренина', 'Лев Толстой'),
246 ('Повести и рассказы', 'Николай Лесков'),
247 ('Антоновские яблоки', 'Иван Бунин'),
248 ('Мертвые души', 'Николай Гоголь'),
249 ('Три сестры', 'Антон Чехов'),
250 ('Беглянка', 'Владимир Даль'),
251 ('Идиот', 'Федор Достоевский'),
252 ('Братья Карамазовы', 'Федор Достоевский'),
253 ('Ревизор', 'Николай Гоголь'),
254 ('Гранатовый браслет', 'Александр Куприн');
255 ''')
256conn.commit()
257
258c.execute('''
259INSERT INTO incoming  (date_incoming) VALUES
260 ('2011-04-10'),
261 ('2011-04-11'),
262 ('2011-04-12');
263 ''')
264conn.commit()
265
266c.execute('''
267INSERT INTO magazine_incoming  (id_incoming, id_product, quantity) VALUES
268 ('1', '1', '10'),
269 ('1', '2', '5'),
270 ('1', '3', '7'),
271 ('1', '4', '10'),
272 ('1', '5', '10'),
273 ('1', '6', '8'),
274 ('1', '18', '8'),
275 ('1', '19', '8'),
276 ('1', '20', '8'),
277 ('2', '7', '10'),
278 ('2', '8', '10'),
279 ('2', '9', '6'),
280 ('2', '10', '10'),
281 ('2', '11', '10'),
282 ('2', '21', '10'),
283 ('2', '22', '10'),
284 ('2', '23', '10'),
285 ('2', '24', '10'),
286 ('3', '12', '10'),
287 ('3', '13', '10'),
288 ('3', '14', '10'),
289 ('3', '15', '10'),
290 ('3', '16', '10'),
291 ('3', '17', '10');
292 ''')
293conn.commit()
294
295c.execute('''
296INSERT INTO prices  (date_price_changes, price) VALUES
297 ('2011-04-10', '100'),
298 ('2011-04-10', '130'),
299 ('2011-04-10', '90'),
300 ('2011-04-10', '100'),
301 ('2011-04-10', '110'),
302 ('2011-04-10', '85'),
303 ('2011-04-11', '95'),
304 ('2011-04-11', '100'),
305 ('2011-04-11', '79'),
306 ('2011-04-11', '49'),
307 ('2011-04-11', '105'),
308 ('2011-04-12', '85'),
309 ('2011-04-12', '135'),
310 ('2011-04-12', '100'),
311 ('2011-04-12', '90'),
312 ('2011-04-12', '75'),
313 ('2011-04-12', '90'),
314 ('2011-04-10', '150'),
315 ('2011-04-10', '140'),
316 ('2011-04-10', '85'),
317 ('2011-04-11', '105'),
318 ('2011-04-11', '70'),
319 ('2011-04-11', '65'),
320 ('2011-04-11', '130');
321 ''')
322conn.commit()
323
324c.execute('''
325INSERT INTO sale  (id_customer, date_sale) VALUES
326 ('2', '2011-04-11'),
327 ('3', '2011-04-11'),
328 ('5', '2011-04-11');
329 ''')
330conn.commit()
331
332c.execute('''
333INSERT INTO magazine_sales  (id_sale, id_product, quantity) VALUES
334 ('1', '1', '1'),
335 ('1', '5', '1'),
336 ('1', '7', '1'),
337 ('2', '2', '1'),
338 ('3', '1', '1'),
339 ('3', '7', '1');
340''')
341conn.commit()
342
343conn.close()