Create code to generate SQL table from a pandas dataframe

Question

I'm wondering if it's possible to generate SQL code given a pandas dataframe.

Given :

pd.DataFrame({'a' : [1, 2], 'b' : [3, 4]})

I'd like to be able to generate something along the lines of:

WITH example AS (
  SELECT 1 as a, 3 as b UNION ALL
  SELECT 2 as a, 4 as b
  )
SELECT * FROM example

If only a subset of this can be generated, perhaps the

WITH example AS (
  SELECT 1 as a, 3 as b UNION ALL
  SELECT 2 as a, 4 as b
  )

That would be fine.

Answer 1

Absolutely possible, just construct what's repeatable as a series, convert to a list, and then add on the extra pieces at the end.

df = df.astype(str)
selects = "SELECT " + df.a + " as a, " + df.b + " as b"
selects = selects.to_list()
output = "WITH example AS (" + " UNION ALL ".join(selects) + ") SELECT * FROM example"
print(output)

Output:

WITH example AS (SELECT 1 as a, 3 as b UNION ALL SELECT 2 as a, 4 as b) SELECT * FROM example