-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathdatabase.py
43 lines (39 loc) · 3.32 KB
/
database.py
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
import sqlite3
conn=sqlite3.connect('bus_booking.db')
cursor=conn.cursor()
cursor.execute('create table if not exists bus(bus_id varchar(5) not null primary key,bus_type varchar(10),capacity int,fair int,op_id varchar(5) not null,route_id varchar(5) not null,foreign key(op_id) references operator(opr_id),foreign key(route_id) references route(r_id))')
cursor.execute('create table if not exists operator(opr_id varchar(5) primary key,name varchar(20),address varchar(50),phone char(10),email varchar(30))')
cursor.execute('create table if not exists running(b_id varchar(5) ,run_date date,seat_avail int,foreign key(b_id) references bus(bus_id))')
cursor.execute('create table if not exists route(r_id varchar(5) not null primary key,s_name varchar(20),s_id varchar(5),e_name varchar(20),e_id varchar(5) )')
cursor.execute('create table if not exists booking_history(name varchar(20),gender char(1),no_of_seat int,phone char(10),age int,booking_ref varchar(10) not null primary key,booking_date date,travel_date date,bid varchar(5),foreign key(bid) references bus(bus_id))')
cursor.execute('''INSERT INTO bus VALUES('1','AC 2x2',35,1250,'2','1')''')
cursor.execute('''INSERT INTO bus VALUES('2','AC 2x2',45,2500,'2','2')''')
cursor.execute('''INSERT INTO bus VALUES('5','2x2',30,1800,'3','4')''')
cursor.execute('''INSERT INTO bus VALUES('6','2x2',35,1500,'1','3')''')
cursor.execute('''INSERT INTO bus VALUES('3','3x2',40,1200,'1','1')''')
cursor.execute('''INSERT INTO bus VALUES('4','AC 3x2',30,1400,'3','2')''')
cursor.execute('''INSERT INTO operator VALUES('1','Kamla Travels','Guna','9876543210','[email protected]')''')
cursor.execute('''INSERT INTO operator VALUES('2','Udaan Travels','Patna','6549873210','[email protected]')''')
cursor.execute('''INSERT INTO operator VALUES('3','Ratri Travels','Sitamarhi','7783868382','[email protected]')''')
cursor.execute('''INSERT INTO running VALUES('1','2023-11-20',35)''')
cursor.execute('''INSERT INTO running VALUES('1','2023-11-21',40)''')
cursor.execute('''INSERT INTO running VALUES('1','2023-11-22',32)''')
cursor.execute('''INSERT INTO running VALUES('1','2023-11-23',50)''')
cursor.execute('''INSERT INTO running VALUES('2','2023-11-24',41)''')
cursor.execute('''INSERT INTO running VALUES('2','2023-11-25',47)''')
cursor.execute('''INSERT INTO running VALUES('2','2023-11-26',35)''')
cursor.execute('''INSERT INTO running VALUES('2','2023-11-27',35)''')
cursor.execute('''INSERT INTO running VALUES('3','2023-11-28',30)''')
cursor.execute('''INSERT INTO running VALUES('3','2023-11-29',50)''')
cursor.execute('''INSERT INTO running VALUES('4','2023-11-30',39)''')
cursor.execute('''INSERT INTO running VALUES('4','2023-12-01',40)''')
cursor.execute('''INSERT INTO running VALUES('5','2023-12-02',35)''')
cursor.execute('''INSERT INTO running VALUES('6','2023-12-03',30)''')
cursor.execute(''' INSERT INTO route VALUES('1','guna','080','patna','066')''')
cursor.execute(''' INSERT INTO route VALUES('2','patna','066','guna','080')''')
cursor.execute(''' INSERT INTO route VALUES('3','guna','080','bhopal','003')''')
cursor.execute(''' INSERT INTO route VALUES('4','gorakhpur','003','bhopal','080')''')
cursor.execute(''' INSERT INTO route VALUES('5','guna','066','lucknow','003')''')
cursor.execute(''' INSERT INTO route VALUES('6','lucknow','003','guna','066')''')
conn.commit()
conn.close()