Debugging: SQL inside Python Psycopg2

Question

sql = "WITH users AS(SELECT * FROM stats.core_users cu LEFT JOIN XXXX.sent_hidden_users h USING(user_id)\
    WHERE cu.status = 'hidden' AND h.user_id is null AND cu.country_code = 86 LIMIT 100)\
SELECT\
    cu.user_id,\
    CASE WHEN cu.gender = 'male' THEN 0 ELSE 1 END AS gender,\
    CASE WHEN cu.looking_for_gender = cu.gender THEN 2 WHEN cu.looking_for_gender = 'both' THEN 1 ELSE 0 END AS sexual_orientation,\
    CASE WHEN e2.os_name = 'iOS' THEN 0 ELSE 1 END AS device,\
    ROUND((DATE(NOW()) - cu.birthdate)/365.25) AS user_age,\
    SUM(dsb.likes) AS likes,\
    SUM(dsb.dislikes) AS dislikes,\
    SUM(dsb.blocks) AS blocks,\
    SUM(dsb.matches) AS matches,\ 
    SUM(dsb.received_likes) AS received_likes,\
    SUM(dsb.received_dislikes) AS received_dislikes,\
    SUM(dsb.received_blocks) AS received_blocks,\
    cu.search_radius,\
    cu.search_min_age,\
    cu.search_max_age,\
    '' AS recall_case,\
    '' AS recall_retention\
FROM \
    users cu\
LEFT JOIN \
    yay.daily_swipes_by_users dsb ON (dsb.user_id = cu.user_id) \
LEFT JOIN LATERAL (\
    SELECT \
            cd.os_name \
    FROM \
            stats.core_devices cd \
    WHERE \
            cu.user_id = cd.user_id \
    ORDER BY cd.updated_time DESC LIMIT 1) e2 ON TRUE \
GROUP BY 1,2,3,4,5,13,14,15,16,17\
;"

Error Information:

File "", line 5 sql = "WITH users AS(SELECT * FROM stats.core_users cu LEFT JOIN zhangqiao.sent_hidden_users h USING(user_id) WHERE cu.status = 'hidden' AND h.user_id is null AND cu.country_code = 86 LIMIT 100)SELECT cu.user_id, CASE WHEN cu.gender = 'male' THEN 0 ELSE 1 END AS gender, CASE WHEN cu.looking_for_gender = cu.gender THEN 2 WHEN cu.looking_for_gender = 'both' THEN 1 ELSE 0 END AS sexual_orientation, CASE WHEN e2.os_name = 'iOS' THEN 0 ELSE 1 END AS device, ROUND((DATE(NOW()) - cu.birthdate)/365.25) AS user_age, SUM(dsb.likes) AS likes, SUM(dsb.dislikes) AS dislikes, SUM(dsb.blocks) AS blocks, SUM(dsb.matches) AS matches,\ ^

SyntaxError: EOL while scanning string literal

Answer 1

There seems to be a space after the \ in SUM(dsb.matches) AS matches,\. Get rid of that. As currently written, you are escaping the space with \ rather than the newline.

Your second error is because you need a space before the \ in this line:

'' AS recall_retention\

Because when you write:

    '' AS recall_retention\
FROM \
    users cu\

You get as a result:

'' AS recall_retentionFROM users cu

Hopefully the error there is obvious. Rather than mucking around with all these escapes, maybe you should just simplify your code by using multiline quotations (either ''' or """), like this:

sql = """WITH users AS(SELECT * FROM stats.core_users cu LEFT JOIN XXXX.sent_hidden_users h USING(user_id)
    WHERE cu.status = 'hidden' AND h.user_id is null AND cu.country_code = 86 LIMIT 100)
SELECT
    cu.user_id,
    CASE WHEN cu.gender = 'male' THEN 0 ELSE 1 END AS gender,
    CASE WHEN cu.looking_for_gender = cu.gender THEN 2 WHEN cu.looking_for_gender = 'both' THEN 1 ELSE 0 END AS sexual_orientation,
    CASE WHEN e2.os_name = 'iOS' THEN 0 ELSE 1 END AS device,
    ROUND((DATE(NOW()) - cu.birthdate)/365.25) AS user_age,
    SUM(dsb.likes) AS likes,
    SUM(dsb.dislikes) AS dislikes,
    SUM(dsb.blocks) AS blocks,
    SUM(dsb.matches) AS matches,
    SUM(dsb.received_likes) AS received_likes,
    SUM(dsb.received_dislikes) AS received_dislikes,
    SUM(dsb.received_blocks) AS received_blocks,
    cu.search_radius,
    cu.search_min_age,
    cu.search_max_age,
    '' AS recall_case,
    '' AS recall_retention
FROM
    users cu
LEFT JOIN
    yay.daily_swipes_by_users dsb ON (dsb.user_id = cu.user_id)
LEFT JOIN LATERAL (
    SELECT
            cd.os_name
    FROM
            stats.core_devices cd
    WHERE
            cu.user_id = cd.user_id
    ORDER BY cd.updated_time DESC LIMIT 1) e2 ON TRUE
GROUP BY 1,2,3,4,5,13,14,15,16,17
;"""