CODEWITHSUNDEEP
pythonsqloracle-database
bugZ
bugZ

Reputation: 494

Python cx_Oracle SQL with bind string variable

I have a problem with creating SQL query for Oracle database using Python. I want to bind string variable and it does not work, could you tell me what am I doing wrong? This is my code:

import cx_Oracle

dokList = []

def LoadDatabase():
    conn = None
    cursor = None
    try:
        conn = cx_Oracle.connect("login", "password", "localhost")

        cursor = conn.cursor()

        query = "SELECT * FROM DOCUMENT WHERE DOC = :param"

        for doknumber in dokList:

            cursor.execute(query, {'doknr':doknumber})
            print(cursor.rowcount)

    except cx_Oracle.DatabaseError as err:
        print(err)
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

def CheckData():

    with open('changedNamed.txt') as f:
        lines = f.readlines()

        for line in lines:
            dokList.append(line)

CheckData()
LoadDatabase()

The output of cursor.rowcount is 0 but it should be number greater than 0.

Upvotes: 0

Views: 4345

Answers (1)

kfinity
kfinity

Reputation: 9091

You're using a dictionary ({'doknr' : doknumber}) for your parameter, so it's a named parameter - the :param needs to match the key name. Try this:

query = "SELECT * FROM DOCUMENT WHERE DOC = :doknr"
for doknumber in dokList:
        cursor.execute(query, {'doknr':doknumber})
        print(cursor.rowcount)

For future troubleshooting, to check whether your parameter is getting passed properly, you can also try changing your query to "select :param from dual".

Upvotes: 1

Related Questions