Create a temporary table in python to join with a sql table
join, python, sql, vertica
Solution
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.
Problem
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?