cx_Oracle - Unable to Bind Date Value within SQL-CodePudding

I'm using cx_Oracle and I'm trying to bind a date variable value inside SQL but I'm not able the resolve the errors. Can someone please offer insight on how to fix it?

The code below gives me an error: "DatabaseError: ORA-00936: missing expression"

dat_ptd_sql = """
select 
    univ_prop_id,
    chain_id
from BA4DBOP1.zs_ptd_stack
where chain_ord = 1
    and sale_valtn_dt >= date :cu_perf_beg
"""
cudb_cur.execute(dat_ptd_sql, cu_perf_beg = "'2022-09-01'")

CodePudding user response：

Another option is to use an actual date inside Python:

import datetime

dat_ptd_sql = """
select 
    univ_prop_id,
    chain_id
from BA4DBOP1.zs_ptd_stack
where chain_ord = 1
    and sale_valtn_dt >= date :cu_perf_beg
"""
cudb_cur.execute(dat_ptd_sql, cu_perf_beg = datetime.datetime(2022, 9, 1))

CodePudding user response：

Try something like this. Note the to_date(). This example uses python-oracledb, which is the new name for the latest version of cx_Oracle.

import getpass
import os

import oracledb

un = os.environ.get('PYTHON_USERNAME')
cs = os.environ.get('PYTHON_CONNECTSTRING')
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

connection = oracledb.connect(user=un, password=pw, dsn=cs)

with connection.cursor() as cursor:
    sql = """select
                 ename
             from emp
             where empno = 7654
             and hiredate >= to_date(:bv, 'YYYY-MM-DD')"""

    for r in cursor.execute(sql, bv='1981-04-01'):
        print(r)