create oracle connection using function in python

Question

I'm trying to create a function for oracle connection which will take the parameters as mentioned in below code, but seems some issue...

could anyone please help in understanding how it can be corrected and what i'm missing here?

import cx_Oracle

def sqlconnect(user,passwd,SID, query):
    connStr = cx_Oracle.connect('user/passwd@SID')
    cursor = connStr.cursor()
    cursor.execute(query)
    return cursor.fetchall()

if __name__ == '__main__':
    sqlconnect('user','password','XEE','select * from dual')

Thanks in advance!

Answer 1

The string user/passwd@SID is not a valid connect string. Unless XEE is a tnsnames.ora entry you need to reference the host and service name in your connect string. You probably want something like this, instead:

def sqlconnect(user, passwd, dsn, query):
    conn = cx_Oracle.connect(user=user, password=passwd, dsn=dsn)
    cursor = conn.cursor()
    cursor.execute(query)
    return cursor.fetchall()

if __name__ == '__main__':
    host = "my_host_name"
    service_name = "XEE"
    conn_string = f"{host}/{service_name}"
    sqlconnect("user", "password", conn_string, "select * from dual")