Summary: in this tutorial, you will learn how to use the bind variables to pass data to and from Oracle Database.
An introduction to bind variables
If you want to pass data to and from the Oracle database, you use placeholders in the SQL statement as follows:
sql = ('select name '
'from customers '
'where customer_id = :customer_id')
Code language: Python (python)In this query, the :customer_id is a placeholder. It is also known as a bind variable or bind parameter.
When you execute a query using the Cursor object, you need to pass the value of the bind variable:
cursor.execute(sql,[100])Code language: Python (python)In this case, the number 100 will be used for the :customer_id bind variable in the SQL statement when the query is executed.
Bind variables make the code more secure and help avoid SQL injection security issues because user data is never treated as a part of the executable SQL statement.
If user data is concatenated with a SQL statement, it will be vulnerable to a SQL injection attack:
sql = ('select name '
'from customers '
'where customer_id = ' + customer_id)Code language: Python (python)For this reason, it is a good practice to always use bind variables in your query and never concatenate or interpolate user data into an SQL statement like the following:
sql = f"""select name
from customers
where customer_id = {id}"""Code language: Python (python)Besides the security benefit, bind variables can improve the performance of a query if an SQL statement is executed multiple times with different values because Oracle just needs to parse and cache the SQL statement once.
The following example illustrates how to find the customer’s name by id using bind variables:
import cx_Oracle
import config
def find_customer_by_id(customer_id):
"""
Find customer name by id
:param customer_id: id of the customer
:return: customer name
"""
sql = ('select name '
'from customers '
'where customer_id = :customer_id')
customer_name = None
try:
# establish a new connection
with cx_Oracle.connect(
config.username,
config.password,
config.dsn,
encoding=config.encoding) as connection:
# create a cursor
with connection.cursor() as cursor:
cursor.execute(sql, [100])
row = cursor.fetchone()
if row:
customer_name = row[0]
except cx_Oracle.Error as error:
print(error)
return customer_name
print(find_customer_by_id(100)) # Verizon
Code language: Python (python)Note that here is the config.py module if you haven’t followed the previous tutorial:
username = 'OT'
password = '<password>'
host = 'localhost'
port = 1512
service = 'pdborcl'
dsn = 'localhost/pdborcl'
encoding = 'UTF-8'Code language: Python (python)Binding by names
If bind variables are associated with names, you have named binds. The named binds require the keyword parameter names or key of the dictionary to match the bind variable names. For example:
import cx_Oracle
import config
sql = ('select product_name '
'from products '
'where list_price > :price and '
'standard_cost <= :cost')
try:
with cx_Oracle.connect(
config.username,
config.password,
config.dsn,
encoding=config.encoding) as connection:
cursor = connection.cursor()
for row in cursor.execute(sql, price=600, cost=500):
print(row)
cursor.close()
except cx_Oracle.Error as error:
print(error)
Code language: Python (python)The :price and :cost bind variables are named binds:
sql = ('select product_name '
'from products '
'where list_price > :price and '
'standard_cost <= :cost')
Code language: Python (python)When executing the SQL statement with named binds, you need to pass the keyword parameter names that match the named binds:
cursor.execute(sql, price=600, cost=500)
Code language: Python (python)Alternatively, you can pass parameters as a dictionary instead of as keyword parameters:
cursor.execute(sql, {price:600, cost:500})
Code language: Python (python)The binding by names allows you to specify meaningful names and use them freely in any position.
In addition, you just need to specify the name parameters once if a variable is repeated multiple times in a query.
Binding by positions
A positional bind is performed when a list of bind values is passed to the Cursor.execute() call. For example:
import cx_Oracle
import config
sql = ('select product_name '
'from products '
'where list_price > :price and '
'standard_cost <= :cost')
try:
with cx_Oracle.connect(
config.username,
config.password,
config.dsn,
encoding=config.encoding) as connection:
cursor = connection.cursor()
for row in cursor.execute(sql, [600, 500]):
print(row)
cursor.close()
except cx_Oracle.Error as error:
print(error)
Code language: Python (python)The order of the bind values must exactly match the order of each bind variable. If you use a bind variable multiple times in the query, you must repeat the bind values.
Bind directions
Bind variables can be IN or OUT.
The IN bind variables allow you to pass data from Python to Oracle Database while the OUT bind variables allow you to get data back from the Oracle Database to Python.
In the previous examples, you have passed in bind variables to the Oracle Database to query data and used a Cursor to fetch the result.
To have the Oracle Database return data to Python, you need to create a variable by using the Cursor.var() method. See the following example:
import cx_Oracle
import config
# construct a PL/SQL anonymous block
plsql = ('begin '
'select count(*) into :customer_count '
'from customers; '
'end;')
try:
# establish a new connection
with cx_Oracle.connect(
config.username,
config.password,
config.dsn,
encoding=config.encoding) as connection:
# create a cursor
with connection.cursor() as cursor:
# create a variable
customer_count = cursor.var(int)
# execute the pl/sql anonymous block
cursor.execute(plsql, customer_count=customer_count)
# show the value of the variable
print(f'The number of customers is {customer_count.getvalue()}')
except cx_Oracle.Error as error:
print(error)
Code language: Python (python)In this example:
First, declare a variable that holds an anonymous PL/SQL block:
plsql = ('begin '
'select count(*) into :customer_count '
'from customers; '
'end;')
Code language: Python (python)Second, connect to the Oracle Database and create a new Cursor object from the Connection object.
Third, create a variable to hold the returned data from the execution of the anonymous PL/SQL block:
customer_count = cursor.var(int)Code language: Python (python)Fourth, execute the PL/SQL block and receive the returned data:
cursor.execute(plsql, customer_count=customer_count)Code language: Python (python)Finally, show the value of the variable by calling the getvalue() method:
print(f'Customer count is {customer_count.getvalue()}')Code language: Python (python)The output is:
The number of customers is 320Code language: Python (python)In this tutorial, you have learned how to use bind variables to pass data to and from Oracle Database.
