0

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.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
Ajit
  • 1
  • 1

2 Answers2

1

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])
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • 1
    Good solution. But I quibble about the terminology in this context: that use of 'bind variables' isn't Oracle's definition of bind variables. Oracle's bind variables are a way to keep data separate from code. With this snippet it is still important to validate the sc & tb values against known 'safe' lists. – Christopher Jones Feb 19 '21 at 01:08
  • thanks @ChristopherJones you're right, fixed the explanation. – Barbaros Özhan Feb 19 '21 at 07:48
  • 2
    You can also use f-strings, as in `cur1.execute(f"select count(*) from {sc}.{tb}")` -- but as noted, you need to ensure the values of sc and tb are known or validated. – Anthony Tuininga Feb 19 '21 at 16:39
0

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'.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
  • Thanks Christopher, I tried using it in my code, but i find that even after setting the schema to what i want when i try to query a table that exists in the schema it says table does not exist and also when i try to query the current user it shows admin, which is the user with which db connection was established and not the user that is being set using Connection.current_schema. – Ajit Feb 22 '21 at 04:16
  • ``` schema_name='myschemaname' table_name='mypassword' dsn_tns1 = cx_Oracle.makedsn(db_host,db_port,db_name) conn1 = cx_Oracle.connect(user=username, password=password, dsn=dsn_tns1) cur1 = conn1.cursor() conn1.current_schema = schema_name cur1.execute("SELECT COUNT(*) FROM {}".format(table_name)) print(cur1.fetchall()) cur1.execute("select sys_context('USERENV','CURRENT_USER') from dual") ``` – Ajit Feb 22 '21 at 04:19
  • Thanks Christopher, For testing purposes i created a table test in schema test and it works fine. But when i query my actual table which has underscore in its name the code fails. I assume this has to do something to with the table name having to be in double quotes. Even in sqlplus when i query the table select * from test.test it works fine, but while i query select * from scheme.table_name it fails i have to specify table_name in double quotes – Ajit Mar 09 '21 at 07:24