# subway_sqlite_python

**Repository Path**: Kozakemi/subway_sqlite_python

## Basic Information

- **Project Name**: subway_sqlite_python
- **Description**: Qt青岛地铁项目前置准备，将txt解析为Sqlite
- **Primary Language**: Python
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2023-09-27
- **Last Updated**: 2023-10-10

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# subway_sqlite_python
### 使用方法
运行 `sqlite_qd.py`,不需要安装而外的包，测试环境Python
``` shell
(venv) ┌─(~/git/subway_sqlite_python)────────────────────────────────────────────────────────────────────────────────────────(kozakemi@kozakemi-arch:pts/1)─┐
└─(23:01:29 on master ✹)──> python3.10                                                                                                                  ──(五,9月29)─┘
Python 3.10.13 (main, Sep 26 2023, 11:54:59) [GCC 13.2.1 20230801] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> 

```
使用以下命令对文件转换
``` shell
python sqlite_qd.py
```
虽然只会读取`lineQD.txt`和执行写入读取`subway_qd.sqlite3`但是依然建议创建备份
### 元数据要求
站点信息符合以下要求
```
id: 1
name: 1号线
colour: #E70012
fromTo: 王家港 东郭庄
totalStations: 41
1 王家港	120.162217 35.942393
........
11 青岛北站 120.377483 36.169569
end
```
注意，必须有end,，虽然不匹配，但是用作结束用
### 表命令
```sqlite
-- 站点表 (Stations)
CREATE TABLE Stations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    station_name TEXT UNIQUE,
    latitude REAL,
    longitude REAL
);

-- 线路表 (Lines)
CREATE TABLE Lines (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    line_name TEXT UNIQUE,
    line_color TEXT
);

-- 线路-站点关联表 (LineStations)
CREATE TABLE LineStations (
    line_id INTEGER,
    station_id INTEGER,
    previous_station_id INTEGER,
    next_station_id INTEGER,
    FOREIGN KEY (line_id) REFERENCES Lines(id),
    FOREIGN KEY (station_id) REFERENCES Stations(id),
    FOREIGN KEY (previous_station_id) REFERENCES Stations(id),
    FOREIGN KEY (next_station_id) REFERENCES Stations(id),
    UNIQUE (line_id, station_id)
);
```

```
站点表 (Stations):

    id (INTEGER): 站点ID，主键, 自增
    station_name (TEXT): 站点名称
    latitude (REAL): 站点纬度
    longitude (REAL): 站点经度

线路表 (Lines):

    id (INTEGER): 线路ID，主键
    line_name (TEXT): 线路名称
    line_color (TEXT): 线路颜色

-- 线路-站点关联表 (LineStations):

    line_id (INTEGER): 线路ID，外键参考线路表
    station_id (INTEGER): 站点ID，外键参考站点表
    previous_station_id (INTEGER): 上一个站点ID，外键参考站点表
    next_station_id (INTEGER): 下一个站点ID，外键参考站点表

```
设计思路：
站点表存储站点基本信息，线路表存储线路基本信息，而线路-站点关联表最为关键，previous_station_id为上一个站点，当为最开始的站点，station_id=previous_station_id，当为结束站点时station_id=next_station_id，当然也许使用NULL可能更加简单容易，但是我并未使用
当然，这样设计时，在插入站点时较为麻烦，插入一个站点，首先需要判断是否是首尾站点，之后修改插入的上一个站点的前驱与后继
### 重点sql语句
#### 查询1号线所有的站点，按照顺序输出`station_id`
下面是实现功能的递归查询语句：
``` sqlite
WITH RECURSIVE station_sequence AS (
  SELECT
    line_id,
    station_id,
    previous_station_id,
    next_station_id
  FROM LineStations
  WHERE line_id = 1 AND station_id = previous_station_id
  
  UNION 
  
  SELECT e.line_id, e.station_id, e.previous_station_id, e.next_station_id
  FROM LineStations e  
  JOIN station_sequence s ON e.station_id = s.next_station_id AND e.line_id = 1 AND e.station_id <> e.previous_station_id
)
SELECT
 station_id
FROM station_sequence;
```
以下为参考结果
``` shell
0
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

```

### 疑问？
请提交Issue
### 感谢
感谢组员的数据统计与表设计思路支持