使用自然连接的sqlite查询生成额外的记录

Question

我试图用Python查询SQLite数据库，希望将结果作为数据帧返回。但是，查询出错，导致输出错误，记录过多。例如，如果我使用以下查询文本：

query = "SELECT name, season, opponent, ratingA, ratingB  
    FROM players NATURAL JOIN games NATURAL JOIN A_ratings NATURAL JOIN B_ratings  
    WHERE (season="2015-16") AND (home_away="home") AND (tournament="tournX") AND (name="John Doe")"

正确的结果应该是两行，每一个出现的球员与这些条件。如果要从光标打印正确的结果，则如下所示：

(u'John Doe', u'2015-16', u'TeamAlpha', 7.5, 8.0)
(u'John Doe', u'2015-16', u'TeamBeta', 6.0, 6.0)

取而代之的是，我得到了团队中每个符合条件的玩家的评分，查询中的名字替换了name列中的正确名字，如下所示：

(u'John Doe', u'2015-16', u'TeamAlpha', 7.5, 8.0)
(u'John Doe', u'2015-16', u'TeamAlpha', 8.5, 9.0)
(u'John Doe', u'2015-16', u'TeamAlpha', 6.5, 7.0)
(u'John Doe', u'2015-16', u'TeamAlpha', 6.5, 6.0)
(u'John Doe', u'2015-16', u'TeamAlpha', 7.0, 7.0))
(u'John Doe', u'2015-16', u'TeamBeta', 6.0, 6.0)
(u'John Doe', u'2015-16', u'TeamBeta', 8.0, 7.5)
(u'John Doe', u'2015-16', u'TeamBeta', 7.0, 7.0)
(u'John Doe', u'2015-16', u'TeamBeta', 7.5, 8.0)
(u'John Doe', u'2015-16', u'TeamBeta', 6.5, 7.0)

表之间似乎有某种乘法，我认为问题出在自然连接部分，但我不知道如何解决它。你知道吗

很明显，我是一个SQL新手，但是我被难住了，因为如果我将查询输入到我的SQLite数据库浏览器中，它就可以完美地工作。你知道吗

表格的结构如下：

CREATE TABLE "games" (
`gameID`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`season`    TEXT,
`tournament`    TEXT,
`home_away` TEXT,
`opponent`  TEXT,  )

CREATE TABLE "players" (
`playerID`  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`name`  TEXT UNIQUE )

CREATE TABLE "A_ratings" (
`A_ratingID`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`playerID`  INTEGER,
`gameID`    INTEGER,
`ratingA`   REAL,
FOREIGN KEY(`playerID`) REFERENCES `players`(`playerID`),
FOREIGN KEY(`gameID`) REFERENCES games(gameID) )

CREATE TABLE "B_ratings" (
`B_ratingID`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`playerID`  INTEGER,
`gameID`    INTEGER,
`ratingB`   REAL,
FOREIGN KEY(`playerID`) REFERENCES `players`(`playerID`),
FOREIGN KEY(`gameID`) REFERENCES games(gameID) )

这里是相关的pandas部分，但是即使我只是打印光标结果（如上所述），问题也会发生。你知道吗

cnxn = sqlite3.connect(path)
df = pd.read_sql(query, cnxn)

Answer 1

我建议不要使用自然连接，而是显式连接要连接的列。它能让人猜不出事情的真相。尤其是在这种情况下，这不是一个明确的交易。你的表（玩家和游戏）没有直接的关联，所以在没有评级表的情况下，这两个表之间没有自然的连接。根据这些连接的顺序，您可能会在那里得到交叉连接（乘法）。更复杂的是，A和B评分都与游戏和玩家有着各自的联系。你知道吗

在没有看到更多数据的情况下，我只是根据这样一个事实进行猜测，即在一种情况下，通过自然连接可以获得所需的结果，我将尝试以下方法：

query = """SELECT 
              name, season, opponent, ratingA, ratingB  
           FROM players
              inner join A_ratings on
                 players.playerID = A_ratings.playerID
              inner join B_ratings on
                 A_ratings.playerID = B_ratings.playerID and A_ratings.gameID = B_ratings.gameID
              inner join games on
                  B_ratings.gameID = games.gameID
           WHERE (season='2015-16') AND 
                 (home_away='home') AND 
                 (tournament='tournX') AND 
                 (name='John Doe')"""