Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python Oracle query
#1
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
Reply
#2
{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.
Reply
#3
(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
Reply
#4
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)
Reply
#5
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
Reply
#6
Sorry, I neglected the keyword in that.

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


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020