Python Database Bind Variables

Bind variables in Python

Let us assume we have list of computer Id’s. We wish to print all the details of computers whose computer Id is present in the list.

A simpler approach would be to use a parameter in the query and place in the query inside a loop. For each loop, a different value is substituted for the parameter.

import cx_Oracle
	con = cx_Oracle.Connection('user/pswd@192.168.2.1/john')
	cur = cx_Oracle.Cursor(con)
	list_of_Id=[100,102,103,104]
	for id in list_of_Id:
		cur.execute(“SELECT * FROM Computer WHERE CompId=”+str(id)) # Placing the query inside a loop 
		for CompId,Make,Mdel,MYear in cur:
			print(Make,CompId)
	cur.close()
	con.close()

The problem with this approach is the query will be compiled by Oracle every time in the loop. If the loop runs 1000 times, then the query will be compiled by Oracle 1000 times leading to performance issues.

Bind in queries

When we need to execute the same query repeatedly, but we just want to substitute the values each time, then we can use bind variables. They improve performance.

import cx_Oracle
	con = cx_Oracle.Connection('user/pswd@192.168.2.1/john')
	cur = cx_Oracle.Cursor(con)
	list_of_Id=[100,102,103,104]
	for id in list_of_Id:
		cur.execute(“SELECT * FROM Computer WHERE CompId=:c_id”,{“c_id”:id})
		for CompId,Make,Model,MYear in cur:
			print(Make,CompId)
	cur.close()
	con.close()

Here c_id is the bind variable

Bind variables are not Python variables. They are actually variables used in Oracle. They start with a colon symbol.

The mapping between the bind variables and Python variables are supplied through a dictionary. This dictionary has key as the bind variable and the corresponding value as the Python variable. The value of Python variable/value is substituted in Oracle bind variable and is then sent to Oracle.

Demo

Try out the code and observe the results.

import cx_Oracle
	con = cx_Oracle.Connection('user/pswd@192.168.2.1/john')
	cur = cx_Oracle.Cursor(con)
	list_of_Id=[100,102,103,104]
	for id in list_of_Id:
		cur.execute(“SELECT * FROM Computer WHERE CompId=:c_id”,{“c_id”:id})
		for CompId,Make,Model,MYear in cur:
			print(Make,CompId)
	cur.close()
	con.close()

Bind variables in queries

When we use bind variables, the Oracle database will reuse the query thus improving the performance. One caution in using bind for queries is that using bind variables will improve performance only if the query is executed repeatedly for the same connection. If the query is executed in a new connection every time, this will not improve the performance.

import cx_Oracle
	list_of_Id=[100,102,103,104]
	for id in list_of_Id:
		con = cx_Oracle.Connection('user/pswd@192.168.2.1/john')
		cur = cx_Oracle.Cursor(con)
		cur.execute(“SELECT * FROM Computer WHERE CompId=:c_id”,{“c_id”:id})
		for CompId,Make,Model,MYear in cur:
			print(Make,CompId)
		cur.close()
		con.close()

In the above code, since the query is executed in a new connection every time, this will not improve the performance.

Multiple Operations

We can perform multiple operations in a single connection. Let us try to increment the year of all the ‘Dell’ computers by 1

import cx_Oracle
	con = cx_Oracle.Connection('user/pswd@192.168.2.1/john')
	cur = cx_Oracle.Cursor(con)
	cur.execute(“SELECT CompId,MYear from Computer where Make='Dell'”)
	for CompId,MYear in cur:
		new_year=int(MYear)+1
		cur2=cx_Oracle.Cursor(con)
		cur2.execute(“UPDATE Computer SET MYear=:year WHERE CompId=:c_id”,{“year”:new_year,“c_id”:CompId})
	cur.close()
	con.commit()
	con.close()