CODEWITHSUNDEEP
pythonsqloracle-databasecx-oracle
noopur kharche
noopur kharche

Reputation: 59

How do I display attributes of table in oracle using python?

I connected Python to Oracle.

I am trying to display the attributes of the table using python i.e I want to display table schema. I am using 'describe' statement but while executing it gives me an error ' Invalid SQL Statement'.

I have done the following: 

queryString = 'Describe Customer'
onCursor.execute(queryString)

"Customer" is the table name

Upvotes: 1

Views: 6643

Answers (3)

S.Harish
S.Harish

Reputation: 159

In below sql you replace the table and owner name. It gives the columns names and datatype of column.

SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='table_name' and OWNER='schema name'

Upvotes: 2

Ivan Bryzzhin
Ivan Bryzzhin

Reputation: 2155

If you need SQL statement columns description then just use cursor.description. If you need exact table schema the select from ALL_TAB_COLUMNS oracle view:

    cnn = cx_Oracle.connect(cnn_str)
    cursor = cnn.cursor()
    cursor.execute("SELECT * FROM dual")
    print(cursor.description)

    cursor.execute("select * from ALL_TAB_COLUMNS where table_name = 'DUAL'")
    print(cursor.fetchall())

    cursor.close()

It will provede output:

[('DUMMY', <class 'cx_Oracle.STRING'>, 1, 4, None, None, 1)]
[('SYS', 'DUAL', 'DUMMY', 'VARCHAR2', None, None, 1, None, None, 'Y', 1, None, None, 1, b'X', b'X', 1, 0, 1, datetime.datetime(2009, 4, 25, 23, 49, 59), 1, 'CHAR_CS', 1, 'YES', 'NO', 2, 1, 'B', 'NO', 'YES', 'NONE', 'NO', 'NO', None, None, None)]

Upvotes: 6

beruic
beruic

Reputation: 5876

Now this is a bit of an old source, but I think your issue is that describe is not really a query.

Try getting the information from the data dictionary instead. Documentation for Oracle 12.2 is here.

Upvotes: 0

Related Questions