cD1C9QdE

1# importing SQL library
2import sqlite3
3
4# creating a connection to the database
5connection = sqlite3.connect("UserInfo.db")
6    
7# creating a cursor to access the data from the table
8cur = connection.cursor()
9
10def Connect():
11    
12    # create table if it does not exist in the database already, makes username, IDnumber, and email unique values so that there can be no duplicates
13    cur.execute("""CREATE TABLE IF NOT EXISTS users (
14        username VARCHAR(20) NOT NULL,
15        password VARCHAR(20) NOT NULL,
16        IDnumber BIGINT,
17        email NVARCHAR(255),
18        UNIQUE (username, IDnumber, email)
19        )""")
20    
21    # create table if it does not exist in the database already
22    cur.execute("""CREATE TABLE IF NOT EXISTS books (                                         
23        name VARCHAR(40) NOT NULL,
24        authors VARCHAR(40) NOT NULL,
25        ISBN INTEGER,
26        genre VARCHAR(30)
27        )""")
28    
29    # create table if it does not exist in the database already
30    cur.execute("""CREATE TABLE IF NOT EXISTS CheckOut (
31        IDnumber INTEGER,
32        ISBN INTEGER,
33        CheckOutDate DATETIME,
34        ReturnDate DATETIME
35        )""")
36    
37    # temporary SQL query that inserts admin info for login, once registration is made for insertion we will delete this line
38    cur.execute("""INSERT INTO users (username, password, IDnumber, email)
39    values("admin", "Password1", 99999, "admin@nodomain.com")""")
40
41    # connection commits to this database
42    connection.commit()