Better ways to print out column names when using cx_Oracle

Question

Found an example using cx_Oracle, this example shows all the information of Cursor.description.

import cx_Oracle
from pprint import pprint

connection = cx_Oracle.Connection("%s/%s@%s" % (dbuser, dbpasswd, oracle_sid))
cursor = cx_Oracle.Cursor(connection)
sql = "SELECT * FROM your_table"
cursor.execute(sql)
data = cursor.fetchall()
print "(name, type_code, display_size, internal_size, precision, scale, null_ok)"
pprint(cursor.description)
pprint(data)
cursor.close()
connection.close()

What I wanted to see was the list of Cursor.description[0](name), so I changed the code:

import cx_Oracle
import pprint

connection = cx_Oracle.Connection("%s/%s@%s" % (dbuser, dbpasswd, oracle_sid))
cursor = cx_Oracle.Cursor(connection)
sql = "SELECT * FROM your_table"
cursor.execute(sql)
data = cursor.fetchall()
col_names = []
for i in range(0, len(cursor.description)):
    col_names.append(cursor.description[i][0])
pp = pprint.PrettyPrinter(width=1024)
pp.pprint(col_names)
pp.pprint(data)
cursor.close()
connection.close()

I think there will be better ways to print out the names of columns. Please get me alternatives to the Python beginner. :-)

Sorry dude, you nailed it yourself ;) Thanx – ant Sep 13 '17 at 06:17 — ant, Sep 13 '17 at 06:17

score 43 · Answer 1 · answered Feb 18 '18 at 17:20

43

You can use list comprehension as an alternative to get the column names:

col_names = [row[0] for row in cursor.description]

Since cursor.description returns a list of 7-element tuples you can get the 0th element which is a column name.

answered Feb 18 '18 at 17:20

Hemant

531
4
3

score 10 · Answer 2 · answered Jun 21 '18 at 08:22

Here the code.

import csv
import sys
import cx_Oracle

db = cx_Oracle.connect('user/pass@host:1521/service_name')
SQL = "select * from dual"
print(SQL)
cursor = db.cursor()
f = open("C:\dual.csv", "w")
writer = csv.writer(f, lineterminator="\n", quoting=csv.QUOTE_NONNUMERIC)
r = cursor.execute(SQL)

#this takes the column names
col_names = [row[0] for row in cursor.description]
writer.writerow(col_names)

for row in cursor:
   writer.writerow(row)
f.close()

Brilliant thanks ,the code created a nice CSV file . – Osama Al-Banna Nov 06 '19 at 18:27 — Osama Al-Banna, Nov 06 '19 at 18:27

score 5 · Accepted Answer · answered May 25 '11 at 17:12

The SQLAlchemy source code is a good starting point for robust methods of database introspection. Here is how SQLAlchemy reflects table names from Oracle:

SELECT table_name FROM all_tables
WHERE nvl(tablespace_name, 'no tablespace') NOT IN ('SYSTEM', 'SYSAUX')
AND OWNER = :owner
AND IOT_NAME IS NULL

score 1 · Answer 4 · answered Apr 14 '23 at 13:58

1

col_names = [row[0] for row in cursor.description]
data = pd.DataFrame(rows)
data.columns=[col_names]

answered Apr 14 '23 at 13:58

AfroiD

21
1

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Apr 15 '23 at 00:07

Better ways to print out column names when using cx_Oracle

4 Answers4

Linked