Python cx_Oracle SQL with bind string variable

Question

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.

kfinity · Accepted Answer · 2017-07-31 17:56:29Z

1

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".

answered Jul 31, 2017 at 17:56

kfinity

9,1411 gold badge15 silver badges22 bronze badges

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

2 Comments

bugZ Over a year ago

Okay, I changed it but again the value of cursor.rowcount is still 0, and select :doknr from dual also didn't work...

Christopher Jones Over a year ago

rowcount would be for DML. For queries, use a SELECT COUNT to get the number of rows.

Collectives™ on Stack Overflow

Python cx_Oracle SQL with bind string variable

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related