将复杂的SQL查询转换为SQLite3的python 2

Question

下面我有一些连接到数据库并运行查询的代码。如果在DB浏览器中输入SQLite，此查询将按预期运行，但当我在python中运行它时，会收到以下错误：

sqlite3.OperationalError:没有这样的列: CHARACTER_INNATES.PC_ID

我阅读了一些关于参数的文档和多个StackOverflow注释，以及它们的目的，以防止像注入攻击这样的问题。我尝试过那种方法，它散落在“？”在任何地方，它都是不可读的和不可维护的，而且不起作用。它还了一个空的元组。这使我相信，应该有一种更简单的方法来执行这类SQL查询。所以有吗？还是我漏掉了什么明显的东西？

db = sqlite3.connect('TheGame.db')
db.text_factory = str
conn = db.cursor()
query = 'SELECT CHARACTERS.Full_Name, CHARACTER_INNATES.PC_ID, SKILLS_INNATES.Skill, Sum([CHARACTER_INNATES].[Current]*[Weight]) AS [Current Innate], Sum([CHARACTER_INNATES].[Maximum]*[Weight]) AS [Max Innate]\
      FROM CHARACTERS INNER JOIN (SKILLS_INNATES INNER JOIN CHARACTER_INNATES ON SKILLS_INNATES.Innate = CHARACTER_INNATES.Innate) ON CHARACTERS.ID = CHARACTER_INNATES.PC_ID\
      GROUP BY CHARACTERS.Full_Name, CHARACTER_INNATES.PC_ID, SKILLS_INNATES.Skill\
      ORDER BY CHARACTER_INNATES.PC_ID, Sum([CHARACTER_INNATES].[Current]*[Weight]) DESC;'
conn.execute(query)
print conn.fetchall()

我的修复尝试看起来有点像

params = ('CHARACTERS.Full_Name', 'CHARACTER_INNATES.PC_ID', ...) #this continued for awhile
query = 'SELECT ?, ?, ?, ...'
conn.execute(query, params)
print conn.fetchall() # prints empty tuple

Answer 1

此查询已不可读。

无论如何，一些SQLite版本存在隐藏在括号内的表名问题。当您使用Access查询生成器时，通常会发生这种情况；只需正确地编写联接：

SELECT CHARACTERS.Full_Name,
       CHARACTER_INNATES.PC_ID,
       SKILLS_INNATES.Skill,
       Sum(CHARACTER_INNATES.Current * Weight) AS "Current Innate",
       Sum(CHARACTER_INNATES.Maximum * Weight) AS "Max Innate"
FROM CHARACTERS
INNER JOIN CHARACTER_INNATES ON CHARACTERS.ID = CHARACTER_INNATES.PC_ID
INNER JOIN SKILLS_INNATES USING (Innate)
GROUP BY CHARACTERS.Full_Name,
         CHARACTER_INNATES.PC_ID,
         SKILLS_INNATES.Skill
ORDER BY CHARACTER_INNATES.PC_ID,
         Sum(CHARACTER_INNATES.Current * Weight) DESC;