I'm trying to run a SQL query through python file, and I'm getting the error message My query code is as below: I have also tried different versions like below: UPDATE)) I think the syntax error is in the comparison operator. How can I send comparison operator from python to SQL? Answer Triple quotes allow you to write the query

SQL Comparison Operator Issue) …right syntax to use near ‘>’2021-06-01’ AND 100*(d1_close-close)/close>’29” at line 1″)

I’m trying to run a SQL query through python file, and I’m getting the error message

... right syntax to use near '>'2021-06-01' AND 100*(d1_close-close)/close>'29'' at line 1")

​x
 
... right syntax to use near '>'2021-06-01' AND 100*(d1_close-close)/close>'29'' at line 1")​

My query code is as below:

sql = "WITH comparing_price AS (SELECT CODE, DATE, OPEN, high, low, close, volume,"
                "LEAD(OPEN, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_open',"
                "LEAD(OPEN, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_open',"
                "LEAD(OPEN, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_open',"
                "LEAD(high, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_high',"
                "LEAD(high, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_high',"
                "LEAD(high, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_high',"
                "LEAD(high, 4) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd4_high',"
                "LEAD(low, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_low',"
                "LEAD(low, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_low',"
                "LEAD(low, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_low',"
                "LEAD(close, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_close',"
                "LEAD(close, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_close',"
                "LEAD(close, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_close',"
                "LEAD(volume, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_volume',"
                "LEAD(volume, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_volume',"
                "AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 2 preceding AND 2 following) AS 'd2_MA5',"
                "AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 7 preceding AND 2 following) AS 'd2_MA10',"
                "AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 17 preceding AND 2 following) AS 'd2_MA20',"
                "AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 57 preceding AND 2 following) AS 'd2_MA60',"
                "AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 117 preceding AND 2 following) AS 'd2_MA120',"
                "STD(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 17 preceding AND 2 following) AS 'd2_std'"
            "FROM daily_price)"
        "SELECT * "
        "FROM comparing_price"
        "WHERE DATE>'2021-06-01' AND 100*(d1_close-close)/close>'29'"
        curs.execute(sql)

 
sql = "WITH comparing_price AS (SELECT CODE, DATE, OPEN, high, low, close, volume,"                "LEAD(OPEN, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_open',"                "LEAD(OPEN, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_open',"                "LEAD(OPEN, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_open',"                "LEAD(high, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_high',"                "LEAD(high, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_high',"                "LEAD(high, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_high',"                "LEAD(high, 4) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd4_high',"                "LEAD(low, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_low',"                "LEAD(low, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_low',"                "LEAD(low, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_low',"                "LEAD(close, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_close',"                "LEAD(close, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_close',"                "LEAD(close, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_close',"                "LEAD(volume, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_volume',"                "LEAD(volume, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_volume',"                "AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 2 preceding AND 2 following) AS 'd2_MA5',"                "AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 7 preceding AND 2 following) AS 'd2_MA10',"                "AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 17 preceding AND 2 following) AS 'd2_MA20',"                "AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 57 preceding AND 2 following) AS 'd2_MA60',"                "AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 117 preceding AND 2 following) AS 'd2_MA120',"                "STD(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 17 preceding AND 2 following) AS 'd2_std'"            "FROM daily_price)"        "SELECT * "        "FROM comparing_price"        "WHERE DATE>'2021-06-01' AND 100*(d1_close-close)/close>'29'"        curs.execute(sql)​

I have also tried different versions like below:

conditions=('2021-06-01','29')
...
....
"WHERE DATE>%s AND 100*(d1_close-close)/close>%s"
curs.execute(sql, conditions)

 
conditions=('2021-06-01','29')......."WHERE DATE>%s AND 100*(d1_close-close)/close>%s"curs.execute(sql, conditions)​

UPDATE)) I think the syntax error is in the comparison operator. How can I send comparison operator from python to SQL?

Answer

Triple quotes allow you to write the query without string concatenation because the strings can span multiple lines:

sql = """WITH comparing_price AS (SELECT CODE, DATE, OPEN, high, low, close, volume,
                LEAD(OPEN, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_open',
                LEAD(OPEN, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_open',
                LEAD(OPEN, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_open',
                "LEAD(high, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_high',
                LEAD(high, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_high',
                LEAD(high, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_high',
                LEAD(high, 4) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd4_high',
                LEAD(low, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_low',
                LEAD(low, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_low',
                LEAD(low, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_low',
                LEAD(close, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_close',
                LEAD(close, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_close',"
                LEAD(close, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_close',
                LEAD(volume, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_volume',
                LEAD(volume, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_volume',
                AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 2 preceding AND 2 following) AS 'd2_MA5',
                AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 7 preceding AND 2 following) AS 'd2_MA10',
                AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 17 preceding AND 2 following) AS 'd2_MA20',
                AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 57 preceding AND 2 following) AS 'd2_MA60',
                AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 117 preceding AND 2 following) AS 'd2_MA120',
                STD(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 17 preceding AND 2 following) AS 'd2_std'
            FROM daily_price)
        SELECT * 
        FROM comparing_price
        WHERE DATE > '2021-06-01' AND 100*(d1_close-close)/close"""

 
sql = """WITH comparing_price AS (SELECT CODE, DATE, OPEN, high, low, close, volume,                LEAD(OPEN, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_open',                LEAD(OPEN, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_open',                LEAD(OPEN, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_open',                "LEAD(high, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_high',                LEAD(high, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_high',                LEAD(high, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_high',                LEAD(high, 4) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd4_high',                LEAD(low, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_low',                LEAD(low, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_low',                LEAD(low, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_low',                LEAD(close, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_close',                LEAD(close, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_close',"                LEAD(close, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd3_close',                LEAD(volume, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd1_volume',                LEAD(volume, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS 'd2_volume',                AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 2 preceding AND 2 following) AS 'd2_MA5',                AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 7 preceding AND 2 following) AS 'd2_MA10',                AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 17 preceding AND 2 following) AS 'd2_MA20',                AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 57 preceding AND 2 following) AS 'd2_MA60',                AVG(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 117 preceding AND 2 following) AS 'd2_MA120',                STD(close) OVER (PARTITION BY CODE ORDER BY DATE, DATE ROWS BETWEEN 17 preceding AND 2 following) AS 'd2_std'            FROM daily_price)        SELECT *         FROM comparing_price        WHERE DATE > '2021-06-01' AND 100*(d1_close-close)/close"""​

This also makes it easier to test because you can just copy the query to another tool to run it.

Advertisement

Answer