cMfcEtRh

1import sqlite3
2conn = sqlite3.connect('Dota2RuForum.sqlite')
3c = conn.cursor()
4conn.execute('''
5CREATE TABLE IF NOT EXISTS users (
6id_user int (10) primary key,
7name varchar (15), 
8email varchar (20),
9password varchar (7))''')
10
11conn.execute ('''INSERT INTO users (id_user, name, email, password)
12VALUES(1,'sergey', 'sergey@mail.ru', '1111')''')
13conn.commit()
14
15users = [(2,'sveta', 'sveta@mail.ru', '2222'),
16(3,'ivan', 'ivan@rambler.ru', '8888'),
17(4, 'katy','katy@gcom','1423')]
18conn.executemany("INSERT INTO users VALUES (?,?,?,?)", users)
19conn.commit()
20
21conn.execute('''CREATE TABLE IF NOT EXISTS topics 
22(id_topic int (10) primary key,
23topic_name varchar(100) not null,
24id_author int (10) not null,
25FOREIGN KEY (id_author) references users (id_user))''')
26
27conn.execute ('''INSERT INTO topics (id_topic, topic_name, id_author)
28VALUES(1,'SuperTopik', 1)''')
29conn.commit()
30
31topics = [(2,'Dota2Topic', 2),
32(3,'LolTopik', 3)]
33conn.executemany("INSERT INTO topics VALUES (?,?,?)", topics)
34conn.commit()
35
36conn.execute('''
37CREATE TABLE IF NOT EXISTS posts (
38id_post int (10) primary key,
39message text NOT NULL,
40id_author int (10) NOT NULL,
41id_topic int (10) NOT NULL)''')
42
43posts = [(1,'PudgeImba', 1, 1),
44(2,'DeleteTechiesPls', 2, 2),
45(3,'BokuNoPikoAkademia', 3, 3)]
46conn.executemany("INSERT INTO posts VALUES (?,?,?,?)", posts)
47conn.commit()
48
49conn.close()
50
51
52
53
54
55import sqlite3
56conn = sqlite3.connect('Dota2RuForum.sqlite')
57c = conn.cursor()
58c.execute('''
59Select name, sql
60From sqlite_master
61Where type = 'table'
62''')
63print(c.fetchone())
64print(c.fetchone())
65print(c.fetchone())
66
67c.execute('''
68Select *
69From users
70''')
71print(c.fetchall())
72
73c.execute('''
74Select *
75From topics
76''')
77print(c.fetchall())
78
79c.execute('''
80Select *
81From posts
82''')
83print(c.fetchall())
84
85c.execute('''
86Select topic_name, name, message
87From users u join topics t on id_user = t.id_author join posts p on t.id_topic = p.id_topic
88''')
89print(c.fetchall())
90
91conn.close()