在Python中创建临时表以与SQL表连接

Question

我在 Vertica 数据库中有以下数据，表名为 Mytable：

+----+-------+
| ID | Value |
+----+-------+
| A  |     5 |
| B  |     9 |
| C  |    10 |
| D  |     7 |
+----+-------+

我将尝试创建一个Python查询来访问Vertica数据库。在Python中，我有一个列表：

ID_list= ['A', 'C']

我希望创建一个查询，基本上是将表Mytable与ID_list内连接，然后可以进行WHERE查询。 因此，它基本上是类似于这样的：

SELECT *
FROM Mytable
INNER JOIN ID_list
    ON Mytable.ID = ID_list as temp_table
WHERE Value = 5

我没有数据库的写入权限，所以需要在本地创建表。或者有其他方法可以做到这一点吗？

- valenzio

---

我认为你不能在本地创建临时表，但为什么不直接在“WHERE”子句中使用这些值呢？ - Tim Biegeleisen

ID_list相当长，这样可以吗？我的意思是最坏的情况下我可以创建一个循环。 - valenzio

我不熟悉Python，所以无法给出完整的答案，但如果Vertica支持的话，你可以使用WHERE IN（5,10，...）。为避免注入，请使用参数查询。 - Tim Biegeleisen

基本上就像这样： SELECT* (SELECT * FROM Mytable WHERE ID IN (ID_list)) WHERE Value = 5 - valenzio

是的，这看起来就像我想要的。 - Tim Biegeleisen

Answer 1

如果你有一个小表格，那么你可以像Tim建议的那样创建一个in-list。

虽然我更喜欢用Python的方式来做这件事。我也可能会将ID_list设置为set，以避免出现重复等问题。

in_list = '(%s)' % ','.join(str(id) for id in ID_list)

或者更好的使用绑定变量（取决于您使用的客户端，如果您处理一组整数，则可能不是严格必要的，因为我无法想象一种注入sql的方法）：

in_list = '(%s)' % ','.join(['%d'] * len(ID_list)

将您的ID列表作为参数列表发送给cursor.execute。这种方法是位置相关的，因此您需要正确安排绑定参数。

如果您有一个非常非常大的列表...您可以创建一个本地临时列表，并在使用连接查询之前加载它。

CREATE LOCAL TEMP TABLE mytable ( id INTEGER );

COPY mytable FROM STDIN;
-- Or however you need to load the data. Using python, you'll probably need to stream in a list using `cursor.copy`

然后加入到mytable表中。

如果行数很少，我不建议这样做，因为开销太大。

- woot

Answer 2

所以我使用了Tim的方法：

# create a String of all the ID_list so they can be inserted into a SQL query
Sql_string='(';
for ss in ID_list:
    Sql_string= Sql_string + " " + str(ss) + ","
Sql_string=Sql_string[:-1] + ")"

"SELECT * FROM
(SELECT * FROM Mytable WHERE ID IN " + Sql_string) as temp 
Where Value = 5"

运行速度惊人地快

- valenzio