Create a temporary table in python to join with a sql table

Question

I have the following data in a vertica db, Mytable:

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

I am trying to create a query in python to access a vertica data base. In python I have a list:

ID_list= ['A', 'C']

I would like to create a query that basically inner joins Mytable with the ID_list and then I could make a WHERE query. So it will be basically something like this

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

I don't have writing rights on the data base, so the table needs to be created localy. Or is there an alternative way of doing this?

Answer 1

If you have a small table, then you can do as Tim suggested and create an in-list.

I kind of prefer to do this using python ways, though. I would probably also make ID_list a set as well to keep from having dups, etc.

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

or better use bind variables (depends on the client you are using, and probably not strictly necessary if you are dealing with a set of ints since I can't imagine a way to inject sql with that):

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

and send in your ID_list as a parameter list for your cursor.execute. This method is positional, so you'll need to arrange your bind parameters correctly.

If you have a very, very large list... you could create a local temp and load it before doing your query with join.

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`

Then join to mytable.

I wouldn't bother doing the latter with a very small number of rows, too much overhead.