LibraryManagementSystem/initDB.py.py at main · ThrivikramGR/LibraryManagementSystem · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
import mysql.connector as mysc

input("Press Enter to Initialize Database for the first time!")

'''Using connection object 'cdb' to create a new database'''

cdb=mysc.connect(host='localhost', user='root', passwd='4321')

csrdb=cdb.cursor()

csrdb.execute('DROP DATABASE IF EXISTS LibProject')

csrdb.execute('create database LibProject')

print()
print("Initializing...")
print()

'''Using connection object 'csrdb' to create tables in the database'''

cobj=mysc.connect(host='localhost', user='root', passwd='4321', database='LibProject')

csr=cobj.cursor()


csr.execute('''create table MainShelf
            ( Serial INT(6) AUTO_INCREMENT,
            ID INT(5) NOT NULL PRIMARY KEY,
            Name VARCHAR(50) NOT NULL,
            Language CHAR(3) NOT NULL,
            Genre VARCHAR(10),
            Shelf INT(2),
            Status CHAR(9),
            Reader CHAR(30),
            INDEX(Serial))
            ''')


csr.execute('''create table Novels
            ( Serial INT(6) AUTO_INCREMENT,
            ID INT(5) NOT NULL UNIQUE,
            Name VARCHAR(50) NOT NULL,
            Language CHAR(3) NOT NULL,
            Genre VARCHAR(10),
            Shelf INT(2),
            INDEX(Serial),
            FOREIGN KEY (ID) REFERENCES MainShelf (ID)
            ON DELETE CASCADE ON UPDATE CASCADE)
            ''')


csr.execute('''create table Reference
            ( Serial INT(6) AUTO_INCREMENT,
            ID INT(5) NOT NULL UNIQUE,
            Name VARCHAR(50) NOT NULL,
            Language CHAR(3) NOT NULL,
            Genre VARCHAR(10),
            Shelf INT(2),
            INDEX(Serial),
            FOREIGN KEY (ID) REFERENCES MainShelf (ID)
            ON DELETE CASCADE ON UPDATE CASCADE)
            ''')


csr.execute('''create table Poems
            ( Serial INT(6) AUTO_INCREMENT,
            ID INT(5) NOT NULL UNIQUE,
            Name VARCHAR(50) NOT NULL,
            Language CHAR(3) NOT NULL,
            Genre VARCHAR(10),
            Shelf INT(2),
            INDEX(Serial),
            FOREIGN KEY (ID) REFERENCES MainShelf (ID)
            ON DELETE CASCADE ON UPDATE CASCADE)
            ''')


csr.execute('''create table English
            ( Serial INT(6) AUTO_INCREMENT,
            ID INT(5) NOT NULL UNIQUE,
            Name VARCHAR(50) NOT NULL,
            Language CHAR(3) NOT NULL,
            Genre VARCHAR(10),
            Shelf INT(2),
            INDEX(Serial),
            FOREIGN KEY (ID) REFERENCES MainShelf (ID)
            ON DELETE CASCADE ON UPDATE CASCADE)
            ''')


csr.execute('''create table Tamil
            ( Serial INT(6) AUTO_INCREMENT,
            ID INT(5) NOT NULL UNIQUE,
            Name VARCHAR(50) NOT NULL,
            Language CHAR(3) NOT NULL,
            Genre VARCHAR(10),
            Shelf INT(2),
            INDEX(Serial),
            FOREIGN KEY (ID) REFERENCES MainShelf (ID)
            ON DELETE CASCADE ON UPDATE CASCADE)
            ''')


csr.execute('''create table Hindi
            ( Serial INT(6) AUTO_INCREMENT,
            ID INT(5) NOT NULL UNIQUE,
            Name VARCHAR(50) NOT NULL,
            Language CHAR(3) NOT NULL,
            Genre VARCHAR(10),
            Shelf INT(2),
            INDEX(Serial),
            FOREIGN KEY (ID) REFERENCES MainShelf (ID)
            ON DELETE CASCADE ON UPDATE CASCADE)
            ''')


print('Database and Tables Initialised succcessfully!')