Python - use string literals in Oracle SQL Query

Question

I am attempting to run a SQL query on an oracle database like so:

import cx_Oracle as cx
import pandas as pd

un = "my username"
pw = "my password"
db = "database name"
lookup = "1232DX%"

myconn = cx.connect(un, pw, db)
cursor = myconn.cursor()

qry = """SELECT *
    FROM tableX
    WHERE tableX.code LIKE '1232DX%'"""

qry.df = pd.read_sql(qry, con = myconn)
myconn.close()

My issue is that it is redundant to define lookup before the query and use the value in the query itself. I would like to just be able to type

WHERE tableX.code LIKE lookup

and have the value 1232DX% substituted into my query.

I imagine there is a straightforward way to do this in Python, but I am hardly an expert so I thought I would ask someone here. All suggestions are welcome. If there is a better way to do this than what I have shown please include it. Thank you in advance.

Barmar · Accepted Answer · 2021-07-29 20:40:04Z

3

You use the same syntax as when passing parameters to cursor.execute().

qry = """SELECT *
    FROM tableX
    WHERE tableX.code LIKE :pattern"""
qry.df = pd.read_sql(qry, con = myconn, params={":pattern": lookup})

answered Jul 29, 2021 at 20:40

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

Python - use string literals in Oracle SQL Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related