zspGp387

1import sqlite3
2
3
4try:
5    sqliteConnection = sqlite3.connect('nowa.sql')
6    cursor = sqliteConnection.cursor()
7    
8    sqlite_select_Query = """CREATE TABLE IF NOT EXISTS users(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT);"""
9    cursor.execute(sqlite_select_Query)
10    sqlite_select_Query = """CREATE TABLE IF NOT EXISTS groups (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT);"""
11    cursor.execute(sqlite_select_Query)
12    sqlite_select_Query = """CREATE TABLE IF NOT EXISTS user_groups (id_user INTEGER NOT NULL, id_group INT NOT NULL, FOREIGN KEY (id_user) REFERENCES users(id), FOREIGN KEY (id_group) REFERENCES groups(id));"""
13    cursor.execute(sqlite_select_Query)
14    
15    sqlite_select_Query = "INSERT INTO users(name)VALUES('Maciej');"
16    cursor.execute(sqlite_select_Query)
17    sqlite_select_Query = "INSERT INTO users(name)VALUES('Damian');"
18    cursor.execute(sqlite_select_Query)
19    sqlite_select_Query = "INSERT INTO users(name)VALUES('Daniel');"
20    cursor.execute(sqlite_select_Query)
21    
22    sqlite_select_Query = "INSERT INTO groups(name)VALUES('Grupa1');"
23    cursor.execute(sqlite_select_Query)
24    sqlite_select_Query = "INSERT INTO groups(name)VALUES('Grupa2');"
25    cursor.execute(sqlite_select_Query)
26    sqlite_select_Query = "INSERT INTO groups(name)VALUES('Grupa3');"
27    cursor.execute(sqlite_select_Query)
28    
29    sqlite_select_Query = "INSERT INTO user_groups(id_user,id_group)VALUES(1,2);"
30    cursor.execute(sqlite_select_Query)
31    sqlite_select_Query = "INSERT INTO user_groups(id_user,id_group)VALUES(1,1);"
32    cursor.execute(sqlite_select_Query)
33    sqlite_select_Query = "INSERT INTO user_groups(id_user,id_group)VALUES(1,3);"
34    cursor.execute(sqlite_select_Query)
35    sqlite_select_Query = "INSERT INTO user_groups(id_user,id_group)VALUES(2,1);"
36    cursor.execute(sqlite_select_Query)
37    sqlite_select_Query = "INSERT INTO user_groups(id_user,id_group)VALUES(2,2);"
38    cursor.execute(sqlite_select_Query)
39    sqlite_select_Query = "INSERT INTO user_groups(id_user,id_group)VALUES(2,3);"
40    cursor.execute(sqlite_select_Query)
41    sqlite_select_Query = "INSERT INTO user_groups(id_user,id_group)VALUES(3,1);"
42    cursor.execute(sqlite_select_Query)
43    sqlite_select_Query = "INSERT INTO user_groups(id_user,id_group)VALUES(3,2);"
44    cursor.execute(sqlite_select_Query)
45    sqlite_select_Query = "INSERT INTO user_groups(id_user,id_group)VALUES(3,3);"
46    cursor.execute(sqlite_select_Query)
47    
48    
49    sqlite_select_Query = "SELECT groups.name, users.name FROM users,groups,user_groups WHERE user_groups.id_user = users.id AND user_groups.id_group = groups.id;"
50    cursor.execute(sqlite_select_Query)
51    record = cursor.fetchall()
52    
53    print(record)
54    cursor.close()
55    
56except sqlite3.Error as error:
57    print("Error while connecting to dbase", error)
58finally:
59    if (sqliteConnection):
60        sqliteConnection.close()
61        print("The SQlite connection is closed")