Read python variable in Oracle-sql query

Question

I want to keep oracle-sql query in python loop. so few python variable need to be placed in sql query. Below is my code:

import pandas as pd
import cx_Oracle
for i in range(df.shape[0]):
        a=df.iloc[i,0]
        b=df.iloc[i,1]
        c=df.iloc[i,2]
        d=df.iloc[i,3]

        con = cx_Oracle.connect('xyz', encoding='utf-8')

        SQL_query= '''SELECT q1.*,
             CASE
                WHEN start_date BETWEEN {a} AND {b} THEN '14A'
                WHEN start_date BETWEEN {c} AND {d} THEN '14B'..........'''
        pdf1 = pd.read_sql(SQL_query , con)

As you can see a,b,c,d are placed in curly bracket. i want to treat it as variable but code is taking it as string

Additionaly do not open / close the DB connection in a loop. — Marmite Bomber
– Marmite Bomber, Commented Apr 15, 2020 at 12:52

Barbaros Özhan · Accepted Answer · 2020-04-15 11:40:15Z

1

Never concatenate or interpolate user data into SQL statements. Prefer using bind variables, which help avoiding SQL Injection security problems because data is never treated as part of an executable statement, by qualifying with a colon ( : ) for each variable such as

SQL_query  = "SELECT q1.*, "
SQL_query += " CASE "
SQL_query += " WHEN start_date BETWEEN to_date(:a,'yyyy-mm-dd') AND to_date(:b,'yyyy-mm-dd') THEN '14A' "
SQL_query += " WHEN start_date BETWEEN to_date(:c,'yyyy-mm-dd') AND to_date(:d,'yyyy-mm-dd') THEN '14B'.........."

pdf1 = pd.read_sql(SQL_query, con, params=['2020-01-01','2020-04-15','2019-01-01','2019-04-15']);

state all four variable values within the params array in the respective order of bind variables a, b, c and d

edited Apr 15, 2020 at 11:40

answered Apr 15, 2020 at 11:35

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Read python variable in Oracle-sql query

I want to keep oracle-sql query in python loop. so few python variable need to be placed in sql query. Below is my code:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

I want to keep oracle-sql query in python loop. so few python variable need to be placed in sql query. Below is my code:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related