how to refer values based on column names in python

Question

i am trying to extract and read the data from a SQL query. Below is the sample data from SQL developer:

target_name    expected_instances   environment   system_name         hostname
--------------------------------------------------------------------------------------  
ORAUAT_host1   1                    UAT           ORAUAT_host1_sys    host1.sample.net
ORAUAT_host2   1                    UAT           ORAUAT_host1_sys    host2.sample.net

Normally i pass the system_name to the query (which has a bind variable for system_name) and get the data as a list,but not the column names. Is there a way in Python to retrieve the data along with the column names and reference values with column name like target_name[0] giving the value ORAUAT_host1?Please suggest.Thanks.

Are you using cx_Oracle ? If so, do you want to retrieve the column names from the cx_Oracle.cursor method ? You should publish the code you want to amend, it would help us to understand better the problem — Roberto Hernandez
– Roberto Hernandez, Commented Aug 27, 2020 at 14:18
@RobertoHernandez yes i am using cx_oracle..will try the cursor method.Thanks. — vivek
– vivek, Commented Aug 27, 2020 at 14:32

Roberto Hernandez · Accepted Answer · 2020-08-27 14:54:07Z

If what you want is to get the column names from the table you are querying, you can do something like this:

My example is printing a csv file

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

The way to print the columns is using the method description of the cursor object

Cursor.description This read-only attribute is a sequence of 7-item sequences. Each of these sequences contains information describing one result column: (name, type, display_size, internal_size, precision, scale, null_ok). This attribute will be None for operations that do not return rows or if the cursor has not had an operation invoked via the execute() method yet.

The type will be one of the database type constants defined at the module level.

https://cx-oracle.readthedocs.io/en/latest/api_manual/cursor.html#

Collectives™ on Stack Overflow

how to refer values based on column names in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related