python sqlite3不执行连接，但sqlite3可以执行

Question

在使用Python 2.6.4的sqlite3标准库时，以下查询在sqlite3命令行中运行得很好：

select segmentid, node_t, start, number,title  from 
    ((segments inner join position using (segmentid)) 
    left outer join titles using (legid, segmentid)) 
    left outer join numbers using (start, legid, version);

但是如果我通过Python中的sqlite3库来执行它，就会出现错误：

>>> conn=sqlite3.connect('data/test.db')
>>> conn.execute('''select segmentid, node_t, start, number,title  from 
((segments inner join position using (segmentid)) left outer join titles using 
(legid, segmentid)) left outer join numbers using (start, legid, version)''')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.OperationalError: cannot join using column start - column not present 
in both tables

在连接的左边，计算出的表似乎有相关的列，因为如果我单独检查它，我得到：

>>> conn.execute('''select *  from ((segments inner join position using 
(segmentid)) left outer join titles using 
(legid, segmentid)) limit 20''').description
(('segmentid', None, None, None, None, None, None), ('html', None, None, None, 
None, None, None), ('node_t', None, None, None, None, None, None), ('legid', 
None, None, None, None, None, None), ('version', None, None, None, None, None, 
None), ('start', None, None, None, None, None, None), ('title', None, None, 
None, None, None, None))

我的数据库结构是：

CREATE TABLE leg (legid integer primary key,  t char(16), year char(16), 
    no char(16));
CREATE TABLE numbers (
    number char(16), legid integer, version integer, start integer, 
    end integer, prev integer, prev_number char(16), next integer, 
    next_number char(16), primary key (number, legid, version));
CREATE TABLE position (
    segmentid integer, legid integer, version integer, start integer, 
    primary key (segmentid, legid, version));
CREATE TABLE 'segments' 
    (segmentid integer primary key,  html text, node_t integer);
CREATE TABLE titles (legid integer, segmentid integer, title text, 
    primary key (legid, segmentid));
CREATE TABLE versions 
    (legid integer, version integer, primary key (legid, version));
CREATE INDEX idx_numbers_start on numbers (legid, version, start);

我对自己做错了什么感到困惑。我已经尝试退出并重新启动Python和sqlite命令行，但还是看不出我哪里出错了。这可能是显而易见的。

Answer 1

SQLite版本3.6.22 -- 看起来你需要对“模糊的列名”进行更明确的说明...

sqlite> select segmentid, node_t, start, number,title  from
   ...> ((segments inner join position using (segmentid))
   ...> left outer join titles using (legid, segmentid))
   ...> left outer join numbers using (start, legid, version);
Error: ambiguous column name: segmentid

sqlite> select segments.segmentid, node_t, start, number,title  from
   ...> ((segments inner join position using (segmentid))
   ...> left outer join titles using (legid, segmentid))
   ...> left outer join numbers using (start, legid, version);
Error: ambiguous column name: start

sqlite> select segments.segmentid, node_t, numbers.start, number,title  from
   ...> ((segments inner join position using (segmentid))
   ...> left outer join titles using (legid, segmentid))
   ...> left outer join numbers using (start, legid, version);
sqlite> 

在SQLite版本3.6.23.1中，行为也是一样的。

Answer 2

你有一个名字很搞笑的表格：

CREATE TABLE 'segments' 

不过我觉得这不是问题所在。根据要求，这里是我执行你查询的结果，'segments' 表格被重新创建为 segments：

$ sqlite3 junk.sqlite
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
CREATE TABLE leg (legid integer primary key,  t char(16), year char(16), 
    no char(16));
CREATE TABLE numbers (
    number char(16), legid integer, version integer, start integer, 
    end integer, prev integer, prev_number char(16), next integer, 
    next_number char(16), primary key (number, legid, version));
CREATE TABLE position (
    segmentid integer, legid integer, version integer, start integer, 
    primary key (segmentid, legid, version));
CREATE TABLE segments (segmentid integer primary key,  html text, node_t integer);
CREATE TABLE titles (legid integer, segmentid integer, title text, 
    primary key (legid, segmentid));
CREATE TABLE versions 
    (legid integer, version integer, primary key (legid, version));
CREATE INDEX idx_numbers_start on numbers (legid, version, start);
sqlite> select segmentid, node_t, start, number,title  from 
   ...>     ((segments inner join position using (segmentid)) 
   ...>     left outer join titles using (legid, segmentid)) 
   ...>     left outer join numbers using (start, legid, version);
Error: ambiguous column name: segmentid

Answer 3

我遇到的问题的解决办法（使用python库）似乎是引入一个完全无关的表名：

SELECT legid, version, segmentid, html, node_t, start, number, title 
    from ((segments inner join position using (segmentid))  
    left outer join titles using (legid, segmentid)) as LT 
    left outer join numbers using (start, legid, version);

我认为这样做的目的是强制sqlite把最外层左连接的左侧名称收集在一起，其中一个名称是“start”，这样就给了最外层左连接一个可以操作的对象。这对我有效——可能升级后会引入更多问题，而不是解决它们，但我会等到那时再处理。