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

Question

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

You probably connected Python to Oracle. Please describe how you did it :) — beruic
– beruic, Commented Nov 22, 2017 at 9:43

Ivan Bryzzhin · Accepted Answer · 2017-11-22 10:29:30Z

6

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)]

answered Nov 22, 2017 at 10:29

Ivan Bryzzhin

2,16521 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S.Harish · Accepted Answer · 2018-10-03 08:56:16Z

2

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'

answered Oct 3, 2018 at 8:56

S.Harish

1591 silver badge6 bronze badges

Comments

beruic · Accepted Answer · 2017-11-22 09:59:27Z

0

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.

answered Nov 22, 2017 at 9:59

beruic

5,9465 gold badges40 silver badges62 bronze badges

1 Comment

noopur kharche Over a year ago

The solution from the old source works. Thank you for the help

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related