Mathieu L'Allier Mathieu L'Allier - 5 months ago 23
SQL Question

Psycopg2 Python Inserting List

I'm trying to insert a list into the SQL query via psycopg2 but every time it see's a ' it doubles it...

heres the code:

for Line in ListFile:
if CountLigne == 1:
IDList = "'" + (Line[1].replace('"', '')) + "', "
CountLigne += 1
elif CountLigne < NbrLigne:
IDList += "'" + (Line[1].replace('"', '')) + "', "
CountLigne += 1
else:
IDList += (Line[1].replace('"', '') + "'")

break
print(IDList)
print(type(IDList))
FTDSQL = (
'''WITH ftd AS (
SELECT m.event_user, m.event_ts, m.revenue, rank() OVER (PARTITION BY m.event_user ORDER BY m.event_ts) as order_purchase
FROM agg_monetization m
WHERE revenue is not null
)
SELECT distinct ftd.event_user, SUM(ftd.revenue)
FROM ftd
WHERE order_purchase = 1
AND ftd.event_user IN (%s)
GROUP BY ftd.event_user, ftd.event_ts
'''
)
Cursor.execute(FTDSQL, [IDList])
print(Cursor.query)


the list: '849cf768-41ea-4ed0-9861-779369d3eede', '10ad8dca-b4e6-4be5-93d3-b7fb88b1668a'

the result : AND ftd.event_user IN ('''849cf768-41ea-4ed0-9861-779369d3eede'', ''10ad8dca-b4e6-4be5-93d3-b7fb88b1668a'', ''863c3eaf-d98d-4f6a-bb97-8756750e7a09''

Thanks !!

Answer

Pyscopg2 automatically converts tuples to sql list. Take a look on Adaptation of Python values to SQL types. So you need just to change list to tuple and then pass it.

FTDSQL = '''
  WITH ftd AS (
    SELECT 
      m.event_user, m.event_ts, m.revenue, 
      rank() OVER (PARTITION BY m.event_user ORDER BY m.event_ts) as order_purchase
    FROM agg_monetization m
    WHERE revenue is not null
  )
  SELECT
    distinct ftd.event_user, SUM(ftd.revenue)
  FROM ftd
  WHERE
    order_purchase = 1
  AND
    ftd.event_user IN %s
    -- parenthesis should be added automatically 
  GROUP BY ftd.event_user, ftd.event_ts
'''
)
Cursor.execute(FTDSQL, (tuple(IDList),))

btw on the other hand Python lists by default are converted to postgres ARRAY