Getting proper output of the SELECT clause. SQLite

pythonselectsqlite

I am coding a bus connection search engine. My goal is to see the time when the bus is departing, while the stop_from and stop_to names are provided.

I have created the STOP table

CREATE TABLE STOP (
ID INTEGER PRIMARY KEY,
STOP_NAME TEXT NOT NULL,
ROUTE_ID INTEGER NOT NULL
);

and the TIMETABLE table

CREATE TABLE TIMETABLE (
ID INTEGER PRIMARY KEY, 
TIME TEXT NOT NULL,
STOP_ID INTEGER NOT NULL,
FOREIGN KEY (STOP_ID) REFERENCES STOP (ID)
);

The route starts in Lidzbark Warmiński and leads via Smolajny and Dobre Miasto to Olsztyn. There are four connections per day. And I have also inserted all the possible routes from Lidzbark Warmiński to Olsztyn

INSERT INTO STOP VALUES
(1,'Lidzbark Warmiński', 1),
(2, 'Smolajny', 1),
(3, 'Dobre Miasto', 1),
(4, 'Olsztyn', 1),
(5, 'Lidzbark Warmiński', 2),
(6, 'Smolajny', 2),
(7, 'Dobre Miasto', 2),
(8, 'Lidzbark Warmiński', 3),
(9, 'Smolajny', 3),
(10, 'Smolajny', 4),
(11, 'Dobre Miasto', 4),
(12, 'Smolajny', 5),
(13, 'Olsztyn', 5),
(14, 'Dobre Miasto', 6),
(15, 'Olsztyn', 6),
(16, 'Smolajny', 7),
(17, 'Dobre Miasto', 7),
(18, 'Olsztyn', 7);

and the timetables regarding them

INSERT INTO TIMETABLE VALUES
--towards Olsztyn
(1, '08:00', 1),
(2, '12:00', 1),
(3, '15:00', 1),
(4, '19:00', 1),
(5, '08:00', 2),
(6, '12:00', 2),
(7, '15:00', 2),
(8, '19:00', 2),
(9, '08:00', 3),
(10, '12:00', 3),
(11, '15:00', 3),
(12, '19:00', 3),
(13, '08:20', 4),
(14, '12:20', 4),
(15, '15:20', 4),
(16, '19:20', 4),
(17, '08:20', 5),
(18, '12:20', 5),
(19, '15:20', 5),
(20, '19:20', 5),
(21, '08:30', 6),
(22, '12:30', 6),
(23, '15:30', 6),
(24, '19:30', 6),
(25, '08:20', 7),
(26, '12:20', 7),
(27, '15:20', 7),
(28, '19:20', 7);

I am developing under python 3.8 and this is a function with my SELECT clause

def fetch_hours(cursor, stop_from_name, stop_to_name):
    cursor.execute(
        '''SELECT T.TIME FROM TIMETABLE T
             JOIN STOP S1 ON T.STOP_ID = S1.ID 
             JOIN STOP S2 ON S2.ROUTE_ID = S1.ROUTE_ID 
             WHERE S1.STOP_NAME = ? 
             AND S2.STOP_NAME = ? ORDER BY T.TIME ASC''', 
        (stop_from_name, stop_to_name)) 
    return cursor.fetchall()

However, I have encountered some problems. For a wide range of bus stops this code doesn't work well. Providing stop_from Smolajny and stop_to Dobre Miasto, the result is

Departures: 08:00, 08:30, 12:00, 12:30, 15:00, 15:30, 19:00, 19:30

While I would like to see only four hours. For some connections it doesn't work at all and for some it is OK.

The desired effect is to see four departure hours for any stop_from and stop_to on this route (from Lidzbark Warmiński to Olsztyn). The reverse route is now commented, and if it would be helpful, the rest of my code can be achieved here: https://github.com/wwmarkositrawe/autobusy-backend

While looking for help,
Wishing you best regards,
marasecki

Best Answer

SELECT * FROM ... gives this output:

ID  TIME   STOP_ID  ID  STOP_NAME  ROUTE_ID  ID  STOP_NAME     ROUTE_ID
--  -----  -------  --  ---------  --------  --  ------------  --------
5   08:00  2        2   Smolajny   1         3   Dobre Miasto  1
21  08:30  6        6   Smolajny   2         7   Dobre Miasto  2
6   12:00  2        2   Smolajny   1         3   Dobre Miasto  1
22  12:30  6        6   Smolajny   2         7   Dobre Miasto  2
7   15:00  2        2   Smolajny   1         3   Dobre Miasto  1
23  15:30  6        6   Smolajny   2         7   Dobre Miasto  2
8   19:00  2        2   Smolajny   1         3   Dobre Miasto  1
24  19:30  6        6   Smolajny   2         7   Dobre Miasto  2

Both route 1 and route 2 have stops at Smolajny and Dobre Miasto, and the first stops (2 and 6) have four times each. This output is correct.