Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import sqlite3
- import sqlite3 as mdb
- from sqlite3 import Cursor
- class Database:
- def __int__(self):
- pass
- def connectToDb() -> sqlite3.Connection:
- db = mdb.connect("users.db")
- cursor = db.cursor()
- db.execute("pragma foreign_keys")
- createTableUsersRooms(db)
- createTableTasks(db)
- createTableUsers(db)
- createTablePoll(db)
- createTableUsersRooms(db)
- print("tables created")
- return db
- def chk_conn(self):
- try:
- self.cursor()
- return True
- except Exception as ex:
- return False
- myconn = mdb.connect("users.db")
- if chk_conn(myconn):
- print("Database connected")
- else:
- print("Database connection failed")
- def createTableUsers(db: mdb.Connection):
- cursor = db.cursor()
- cursor.execute('''
- CREATE TABLE IF NOT EXISTS users (
- user_id integer PRIMARY KEY,
- login text NOT NULL UNIQUE,
- password text NOT NULL
- )
- ''')
- def createTableRooms(db: mdb.Connection):
- cursor = db.cursor()
- cursor.execute('''
- CREATE TABLE IF NOT EXISTS rooms (
- room_id integer PRIMARY KEY,
- password text NOT NULL,
- owner_id integer NOT NULL,
- FOREIGN KEY (owner_id) REFERENCES users (id)
- )
- ''')
- def createTableUsersRooms(db: mdb.Connection):
- cursor = db.cursor()
- cursor.execute('''
- CREATE TABLE IF NOT EXISTS users_rooms (
- id integer PRIMARY KEY,
- room_id integer NOT NULL,
- user_id integer NOT NULL,
- FOREIGN KEY (user_id) REFERENCES users (id),
- FOREIGN KEY (room_id) REFERENCES rooms (id),
- UNIQUE(room_id, user_id)
- )
- ''')
- def createTablePoll(db: mdb.Connection):
- cursor = db.cursor()
- cursor.execute('''
- CREATE TABLE IF NOT EXISTS poll (
- id integer PRIMARY KEY,
- subject_id integer NOT NULL,
- task_id integer NOT NULL,
- user_id integer NOT NULL,
- vote float NOT NULL,
- FOREIGN KEY (task_id) REFERENCES tasks (id),
- FOREIGN KEY (user_id) REFERENCES users (id)
- )
- ''')
- def createTableTasks(db: mdb.Connection):
- cursor = db.cursor()
- cursor.execute('''
- CREATE TABLE IF NOT EXISTS tasks (
- id integer PRIMARY KEY,
- room_id integer NOT NULL,
- subject text NOT NULL,
- FOREIGN KEY (room_id) references rooms (id)
- )
- ''')
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement