Python Database Retrieving Specific Columns

Retrieving Specific Columns in Python

When we get a row from the cursor, the individual elements of the row tuple relate to the individual columns. For example,

SELECT * FROM Computer

This selects all the 4 columns in the table. Hence each row extracted as a tuple will have four elements in it. The first column in the table will the first element in the tuple.

When we select only specific columns, for example,

SELECT Make,CompId FROM Computer

The row tuple will now have only two elements where the first element will be make and the second element will be compId.

Thus the order of elements in a tuple depends on the order of columns specified in the SELECT clause. If the column names are not specified the order of elements in the tuple depends on the order in which the columns are defined for the table in database

Select Query

We can extract the individual columns from the row tuple in two ways:

In the first way, individual variable names are used for each value in the tuple:

cur.execute(“SELECT * FROM Computer”)
	for column1,column2,column3,column4 in cur:
		print(column3,column1)

Note : In the above example column1, column2, etc are actually Python variables. These names have no connection to the database.

In the second way the entire tuple is stored in a single Python variable:

cur.execute("SELECT * FROM Computer")
	for row in cur:
		print(row[2],row[0])

We can access individual columns by using the index position, which starts with zero.

We have already seen that the column names in the query have no significance in the Python code. The column values are converted into their equivalent Python data types.

import cx_Oracle
	con = cx_Oracle.Connection('user/pswd@192.168.2.1/john')
	cur = cx_Oracle.Cursor(con)
	cur.execute(“SELECT sum(Salary) AS sum FROM Employee”)
	for row in cur:
		print(row[0])
	cur.close()
	con.commit()
	con.close()

In the above code, we are using an alias, but in the Python code, we are just interested in the value of the column and not the column name itself.