CODEWITHSUNDEEP
pythonoracle-databaseboto3cx-oracle
Ajit
Ajit

Reputation: 1

Python cx_Oracle module: unable to format query in code

I am using cx_Oracle module to connect to oracle database. In the script i use two variables schema_name and table_name. The below query works fine

cur1.execute("select owner,table_name from dba_tables where owner ='schema_name'")

But i need to query the num of rows of a table, where i need to qualify the table_name with the schema_name and so the query should be

SELECT count(*) FROM "schema_name"."table_name"

This does not work when using in the code, i have tried to put it in triple quotes, single quotes and other options but it does not format the query as expected and hence errors out with table does not exist.

Any guidance is appreciated.

Upvotes: 0

Views: 554

Answers (2)

Christopher Jones
Christopher Jones

Reputation: 10506

In this particular case, you could also try setting Connection.current_schema, see the cx_Oracle API doc

For example, if you create table in your own schema:

SQL> show user
USER is "CJ"
SQL> create table ffff (mycol number);

Table created.

SQL> insert into ffff values (1);

1 row created.

SQL> commit;

Commit complete.

Then run Python code that connects as a different user:

import cx_Oracle
import os

import sys, os
if sys.platform.startswith("darwin"):
    cx_Oracle.init_oracle_client(lib_dir=os.environ.get("HOME")+"/Downloads/instantclient_19_8")

username = "system"
password = "oracle"
connect_string = "localhost/orclpdb1"

connection = cx_Oracle.connect(username, password, connect_string)

connection.current_schema = 'CJ';

with connection.cursor() as cursor:
    sql = """select * from ffff"""
    for r in cursor.execute(sql):
        print(r)

    sql = """select sys_context('USERENV','CURRENT_USER') from dual"""
    for r in cursor.execute(sql):
        print(r)

the output will be:

(1,)
('SYSTEM',)

The last query shows that it is not the user that is being changed, but just the first query is automatically changed from 'ffff' to 'CJ.ffff'.

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

A prepared statement containing placeholders with variables of the form ...{}.{}".format(sc,tb) might be used

sc='myschema'
tb='mytable'

cur1.execute("SELECT COUNT(*) FROM {}.{}".format(sc,tb))
print(cur1.fetchone()[0])

Upvotes: 1

Related Questions