python - SQLSoup 多重连接

Question

我想使用SQLSoup用于查询 MySQL 数据库。

我想要发出的查询包含多个联接。不幸的是tutorial示例涉及具有完全不同列名的表(表之间没有重复的列名)。

就我而言，我的所有表都有一个名为 id 的 pkey 列。

我执行以下操作:

s = SQLSoup(DATABASE_URI)
s.join(s.logical_interface, s.with_labels(s.physical_interface), s.physical_interface.id == s.logical_interface.physical_interface_id).first()

结果是:

InvalidRequestError: Implicitly combining column logical_interface.physical_interface_id with column foo.physical_interface_id under attribute 'physical_interface_id'.  Please configure one or more attributes for these same-named columns explicitly

因此我执行以下操作: s.join(s.with_labels(s.logic_interface), s.with_labels(s.physical_interface), s.physical_interface.id == s.logic_interface.physical_interface_id).first()

结果(请参阅下面的完整查询):

OperationalError: (OperationalError) (1066, "Not unique table/alias: 'foo'") 'SELECT foo.logical_interface_id AS ... 

似乎每个表都使用相同的表别名foo，这显然会带来麻烦。

有正确的方法吗？

<小时/>

完整查询:

'SELECT foo.logical_interface_id AS foo_logical_interface_id, foo.logical_interface_physical_interface_id AS foo_logical_interface_physical_interface_id, foo.logical_interface_name AS foo_logical_interface_name, foo.logical_interface_description AS foo_logical_interface_description, foo.logical_interface_active AS foo_logical_interface_active, foo.logical_interface_ifindex AS foo_logical_interface_ifindex, foo.logical_interface_ifinoctets AS foo_logical_interface_ifinoctets, foo.logical_interface_ifoutoctets AS foo_logical_interface_ifoutoctets, foo.logical_interface_lastpolloctets AS foo_logical_interface_lastpolloctets, foo.logical_interface_ifsigqunerroreds AS foo_logical_interface_ifsigqunerroreds, foo.logical_interface_ifsigqcorrecteds AS foo_logical_interface_ifsigqcorrecteds, foo.logical_interface_ifsigquncorrectables AS foo_logical_interface_ifsigquncorrectables, foo.logical_interface_lastpollfec AS foo_logical_interface_lastpollfec, foo.physical_interface_id AS foo_physical_interface_id, foo.physical_interface_docsis_mac_id AS foo_physical_interface_docsis_mac_id, foo.physical_interface_name AS foo_physical_interface_name, foo.physical_interface_type AS foo_physical_interface_type, foo.physical_interface_module AS foo_physical_interface_module \nFROM (SELECT logical_interface.id AS logical_interface_id, logical_interface.physical_interface_id AS logical_interface_physical_interface_id, logical_interface.name AS logical_interface_name, logical_interface.description AS logical_interface_description, logical_interface.active AS logical_interface_active, logical_interface.ifindex AS logical_interface_ifindex, logical_interface.ifinoctets AS logical_interface_ifinoctets, logical_interface.ifoutoctets AS logical_interface_ifoutoctets, logical_interface.lastpolloctets AS logical_interface_lastpolloctets, logical_interface.ifsigqunerroreds AS logical_interface_ifsigqunerroreds, logical_interface.ifsigqcorrecteds AS logical_interface_ifsigqcorrecteds, logical_interface.ifsigquncorrectables AS logical_interface_ifsigquncorrectables, logical_interface.lastpollfec AS logical_interface_lastpollfec \nFROM logical_interface) AS foo INNER JOIN (SELECT physical_interface.id AS physical_interface_id, physical_interface.docsis_mac_id AS physical_interface_docsis_mac_id, physical_interface.name AS physical_interface_name, physical_interface.type AS physical_interface_type, physical_interface.module AS physical_interface_module \nFROM physical_interface) AS foo ON physical_interface.id = logical_interface.physical_interface_id \n LIMIT %s' (1,)

Answer 1

答案是“不”。

原因是 SQLSoup 没有维护并且已经过时(我可以这样说，因为我是 SQLSoup 唯一的维护者)。使用automap ，我编写它作为 SQLSoup 的现代替代品。