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

• Marmite Bomber: Additionaly do not open / close the DB connection in a loop.

Answer 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