Oct-08-2018, 04:41 PM
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.
error:
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