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. :-)

Lovellalovelock

Sorry dude, you nailed it yourself ;) Thanx – Variate

Answer 1

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

Renegado

Answer 2

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.

Semantic

Answer 3

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

Glengarry

Brilliant thanks ,the code created a nice CSV file . – Caponize

Answer 4

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

Tandi

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? – Gyrose