Python使用sqlite3执行SQL查询获取唯一Song_ID时遇语法错误

数据库结构 #

我有一个包含两张表的数据库：

1. music表 #

字段包括name、Date、Edition、Song_ID、Singer_ID，数据如下：

2. singer表 #

字段包括Singer、nationality、Singer_ID，数据如下：

报错情况 #

我想统计歌曲数量，执行SQL语句：

SELECT DISTINCT Song_ID FROM music

时提示“DISTINCT附近存在语法错误”。

数据库创建及插入数据的Python代码 #

代码执行正常，但查询语句报错：

import sqlite3

conn = sqlite3.connect('musicten.db')  
c = conn.cursor()

c.execute('''
          CREATE TABLE IF NOT EXISTS singer
          ([Singer_ID] INTEGER PRIMARY KEY, [Singer] TEXT, [nationality] TEXT)
          ''')
          
c.execute('''
          CREATE TABLE IF NOT EXISTS music
          ([SONG_ID] INTEGER PRIMARY KEY, [SINGER_ID] INTEGER SECONDARY KEY, [name] TEXT, [Date] DATE, [EDITION] INTEGER)
          ''')
                     
conn.commit()



import sqlite3

conn = sqlite3.connect('musicten.db') 
c = conn.cursor()
                   
c.execute('''
          INSERT INTO singer (Singer_ID, Singer,nationality)

                VALUES
                (1,'JT Watson',' USA'),
                (2,'Rafinha','Brazil'),
                (3,'Juan Casa','Spain'),
                (4,'Kidi','USA'),
                (5,'Dede','USA')
          ''')

c.execute('''
          INSERT INTO music (Song_ID,Singer_ID, name, Date,Edition)

                VALUES
                (1,1,'LA',01/05/2009,1),
                (2,2,'Second',13/07/2009,1),
                (3,1,'Mexico',13/07/2009,1),
                (4,3,'Let"s go',13/09/2009,1),
                (5,(4,5),'Hello',18/09/2009,1)
          ''')

问题分析与解决 #

1. DISTINCT语法错误的直接原因 #

SQLite中SELECT DISTINCT语法本身合法，但创建music表时使用的SECONDARY KEY是SQLite不支持的语法，这会导致表结构创建异常，干扰后续查询语句的解析，最终抛出语法错误。

2. 数据库设计的其他问题 #

• 多歌手关联逻辑错误：music表中存在Singer_ID为(4,5)、(5,6)的情况，说明单首歌曲对应多个歌手，但用单个字段存储多个ID违反数据库范式，应创建中间表（如music_singer）实现多对多关联。
• 日期插入错误：插入时用01/05/2009会被SQLite当作除法运算，导致日期字段存储数值而非日期字符串，需用单引号包裹日期字符串（如'01.05.2009'）。
• 字符串转义错误：插入Let"s go时双引号未转义，SQLite中需用两个单引号转义单引号，应写成'Let''s go'。

3. 修正后的解决方案 #

步骤1：修正表结构创建代码

import sqlite3

conn = sqlite3.connect('musicten.db')  
c = conn.cursor()

# 创建singer表
c.execute('''
          CREATE TABLE IF NOT EXISTS singer
          (Singer_ID INTEGER PRIMARY KEY, Singer TEXT, nationality TEXT)
          ''')
          
# 创建music表，移除不支持的SECONDARY KEY
c.execute('''
          CREATE TABLE IF NOT EXISTS music
          (Song_ID INTEGER PRIMARY KEY, name TEXT, Date TEXT, Edition INTEGER)
          ''')

# 创建多对多中间表
c.execute('''
          CREATE TABLE IF NOT EXISTS music_singer
          (Song_ID INTEGER, Singer_ID INTEGER,
           PRIMARY KEY (Song_ID, Singer_ID),
           FOREIGN KEY (Song_ID) REFERENCES music(Song_ID),
           FOREIGN KEY (Singer_ID) REFERENCES singer(Singer_ID))
          ''')
                     
conn.commit()

步骤2：修正插入数据代码

import sqlite3

conn = sqlite3.connect('musicten.db') 
c = conn.cursor()
                   
# 插入完整singer数据
c.execute('''
          INSERT INTO singer (Singer_ID, Singer, nationality)
          VALUES
                (1,'JT Watson','USA'),
                (2,'Rafinha','Brazil'),
                (3,'Juan Casa','Spain'),
                (4,'Kidi','USA'),
                (5,'Dede','USA'),
                (6,'Briana','USA'),
                (7,'Jay Ado','UK'),
                (8,'Dani','Australia'),
                (9,'Mike Rich','USA')
          ''')

# 插入music数据
c.execute('''
          INSERT INTO music (Song_ID, name, Date, Edition)
          VALUES
                (1,'LA','01.05.2009',1),
                (2,'Second','13.07.2009',1),
                (3,'Mexico','13.07.2009',1),
                (4,'Let''s go','13.09.2009',1),
                (5,'Hello','18.09.2009',1),
                (6,'Don''t give up','12.02.2010',2),
                (7,'ZIC ZAC','18.03.2010',2),
                (8,'Blablabla','14.04.2010',2),
                (9,'Oh la la','14.05.2011',3),
                (10,'Food First','14.05.2011',3),
                (11,'La Vie est..','17.06.2011',3),
                (12,'Jajajajajaja','13.07.2011',3)
          ''')

# 插入多对多关联数据
c.execute('''
          INSERT INTO music_singer (Song_ID, Singer_ID)
          VALUES
                (1,1),
                (2,2),
                (3,1),
                (4,3),
                (5,4),
                (5,5),
                (6,5),
                (6,6),
                (7,7),
                (8,2),
                (9,4),
                (10,5),
                (11,8),
                (12,9)
          ''')

conn.commit()
conn.close()

步骤3：正确统计歌曲数量

因为Song_ID是主键，直接用COUNT即可统计总数：

SELECT COUNT(Song_ID) AS total_songs FROM music;

若需去重（主键本身无重复，此为演示）：

SELECT COUNT(DISTINCT Song_ID) AS total_songs FROM music;

内容的提问来源于stack exchange，提问作者user19965401