Using Pandas Dataframe within a SQL Join

Question

I'm trying to perform a SQL join on the the contents of a dataframe with an external table I have in a Postgres Database.

This is what the Dataframe looks like:

>>> df
   name  author  count
0  a     b       10
1  c     d       5
2  e     f       2

I need to join it with a Postgres table that looks like this:

TABLE: blog
title   author    url    
a       b         w.com
b       b         x.com
e       g         y.com

This is what I'm attempting to do, but this doesn't appear to be the right syntax for the query:

>>> sql_join = r"""select b.*, frame.*  from ({0}) frame
        join blog b
        on frame.name = b.title
        where frame.owner = b.owner 
        order by frame.count desc
        limit 30;""".format(df)

>>> res = pd.read_sql(sql_join, connection)

I'm not sure how I can use the values in the dataframes within the sql query. Can someone point me in the right direction? Thanks!

Edit: As per my use case, I'm not able to convert the blog table into a dataframe given memory and performance constraints.

Answer 1

I managed to do this without having to convert the dataframe to a temp table or without reading SQL into a dataframe from the blog table.

For anyone else facing the same issue, this is achieved using a virtual table of sorts.

This is what my final sql query looks like this:

>>> inner_string = "VALUES ('a','b',10), ('c','d',5), ('e','f',2)"

>>> sql_join = r"""SELECT * FROM blog
        JOIN ({0}) AS frame(title, owner, count)
        ON blog.title = frame.title
        WHERE blog.owner = frame.owner 
        ORDER BY frame.count DESC
        LIMIT 30;""".format(inner_string)

>>> res = pd.read_sql(sql_join, connection)

You can use string manipulation to convert all rows in the dataframe into one large string similar to inner_string.