CODEWITHSUNDEEP
pythonpython-3.xoracle-databaseoracle11gpyodbc
moosearch
moosearch

Reputation: 316

pyodbc - cursor does not accept integer parameters when using python 3

Good day,

I am having issues with my select queries when I use non-string parameters for my SQL queries with pyodbc.

My current environment:

Here are some code snippets below.

Code for creating a database connection object and declaring the cursor object as a class variable:

class DB_Oracle(object):
    """Database connections for Oracle Databases.
    """

    def __init__(self, Target="test"):
        """Connect to the target database and create a cursor for doing queries.
        """
        if Target == "test":
            if platform.architecture()[0] == "64bit":
                self.DatabaseDriver = "{Oracle in OraClient12Home1}"
            else:
                self.DatabaseDriver = "{Oracle in OraClient11g_home1}"

            self.DatabaseUser = #omitted
            self.DatabasePassword = #omitted

            self.HostName = #omitted
            self.Port = "1521"
            self.SID = #omitted
            self.DBQ = self.HostName + ":" + self.Port + "/" + self.SID

            print("            - Assyst: Connecting to "+self.HostName+" with driver "+self.DatabaseDriver)
            try:
                self.dbconnection = pyodbc.connect(
                        "DRIVER=" + self.DatabaseDriver +
                        ";DBQ=" + self.DBQ + 
                        ";UID=" + self.DatabaseUser + 
                        ";PWD=" + self.DatabasePassword)
                self.cursor = self.dbconnection.cursor()
                print("            - Connection completed: " + str(Target))
            except:
                print("** ERROR: Can not create connection to: " + str(Target))

        else:
            print("** ERROR: The requested target Database is unknown: " + str(Target))

        return(None)

Example code for querying said database:

if __name__=='__main__':
    db_conn = DB_Oracle(Target="test")
    sql_query = "SELECT * from TEST_TABLE WHERE TEST_ID = ?"
    param = 1
    db_conn.cursor.execute(sql_query, param)
    print(db_conn.cursor.fetchall())
    db_conn.cursor.close()

The above code will work if I am using Python 2, but will fail if I use python 3. It will spit out the following error:

Error: ('HY000', 'The driver did not supply an error!')

If I declare my param variable as a string:

    param = '1'

The code will execute as normal.

Table data for reference:

TEST_TABLE

TEST_ID | TEST_NAME
-------------------
1       | 'test1' 
2       | 'test2' 
3       | 'test3' 
4       | 'test4'

What I have narrowed down through my troubleshooting is that this problem only affects python 3 code querying Oracle databases. The ODBC drivers don't seem to be the issue nor python's bit-ness.

I could cast all my parameters as a string for my existing queries, but I would like to refrain from doing that if possible. PDB hasn't helped me much either in debugging my code.

Where could I go from here?

Thank you

Upvotes: 1

Views: 460

Answers (1)

moosearch
moosearch

Reputation: 316

So...

Turns out that an old version of pyodbc was the issue. Doing an update from 4.0.25 to 4.0.30 on the pyodbc package within Anaconda fixed the problem.

I rather not modify the base installation of the anaconda package so it looks like I'll use a virtual environment of some sort.

Thank you all that viewed my question

Upvotes: 2

Related Questions