Python Oracle query - Printable Version

Python Oracle query - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: Python Oracle query (/thread-13285.html)

Python Oracle query - onenessboy - Oct-08-2018

Hi Friends,

Trying to connect to oracle database and query a table based on value passed to it via runtime parameter and place result set in pandas dataframe.

import pandas as pd
from pandas import DataFrame
from sqlalchemy import create_engine
import os
import subprocess
import sys
name = sys.argv[1]
os.environ['ORACLE_HOME'] = "/u01/app/oracle/12.1.0.2/db1-base/"
os.environ['ORACLE_SID'] = "orcl"
os.environ['PATH'] = "/u01/app/oracle/12.1.0.2/db1-base/bin/"
oracle_connection_string = 'oracle+cx_oracle://{username}:{password}@{hostname}:{port}/{database}'
engine = create_engine(
    oracle_connection_string.format(
        username='system',
        password='Oracle_456',
        hostname='localhost',
        port='1521',
        database='orcl',
    )
)
sql = 'SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = :{name}'
data = pd.read_sql_query(sql,engine)
print(data)

However I am getting error at below line:

sql = 'SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = :{name}'

:name is the one which i want to pass as parameter while running this program

error:

[oracle@abora ~]$ python orcltest.py ABTESTLOCK
Traceback (most recent call last):
  File "orcltest.py", line 22, in <module>
    data = pd.read_sql_query(sql,engine)
  File "/usr/lib64/python2.7/site-packages/pandas/io/sql.py", line 314, in read_sql_query
    parse_dates=parse_dates, chunksize=chunksize)
  File "/usr/lib64/python2.7/site-packages/pandas/io/sql.py", line 1063, in read_query
    result = self.execute(*args)
  File "/usr/lib64/python2.7/site-packages/pandas/io/sql.py", line 954, in execute
    return self.connectable.execute(*args, **kwargs)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 2075, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 942, in execute
    return self._execute_text(object, multiparams, params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1104, in _execute_text
    statement, parameters
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-01745: invalid host/bind variable name [SQL: 'SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = :{name}'] (Background on this error at: http://sqlalche.me/e/4xp6)

Please help

RE: Python Oracle query - stullis - Oct-08-2018

{name} is not formatted in your code. I'd wager that's the source of the problem. Just list the oracle_connection_string, try using the format method to replace {name} with the username.

RE: Python Oracle query - onenessboy - Oct-08-2018

(Oct-08-2018, 05:00 PM)stullis Wrote: {name} is not formatted in your code. I'd wager that's the source of the problem. Just list the oracle_connection_string, try using the format method to replace {name} with the username.

Hi stullis thanks for reply...
Can you please let me know how to format it properly. When directly hardcoded value it's running fine. But I need to input username at runtime

RE: Python Oracle query - stullis - Oct-08-2018

Unless you're looking for a different username, this change should do the trick. I don't believe the colon is needed in the SQL statement.

import pandas as pd
from pandas import DataFrame
from sqlalchemy import create_engine
import os
import subprocess
import sys
name = sys.argv[1]
os.environ['ORACLE_HOME'] = "/u01/app/oracle/12.1.0.2/db1-base/"
os.environ['ORACLE_SID'] = "orcl"
os.environ['PATH'] = "/u01/app/oracle/12.1.0.2/db1-base/bin/"
oracle_connection_string = 'oracle+cx_oracle://{username}:{password}@{hostname}:{port}/{database}'
user = 'system'
engine = create_engine(
    oracle_connection_string.format(
        username=user,
        password='Oracle_456',
        hostname='localhost',
        port='1521',
        database='orcl',
    )
)
sql = 'SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = :{name}'
data = pd.read_sql_query(sql.format(user),engine)
print(data)

RE: Python Oracle query - onenessboy - Oct-11-2018

Hi,

apologies for delayed reply
Its still show error like this

[oracle@abora ~]$ python orcltest1.py ABTESTLOCK
Traceback (most recent call last):
File "orcltest1.py", line 23, in <module>
data = pd.read_sql_query(sql.format(user),engine)
KeyError: 'name'

any suggestion pls

RE: Python Oracle query - stullis - Oct-11-2018

Sorry, I neglected the keyword in that.

data = pd.read_sql_query(sql.format(name = user),engine)