{"id":3010,"date":"2019-11-22T22:01:15","date_gmt":"2019-11-23T05:01:15","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=3010"},"modified":"2025-05-31T18:45:59","modified_gmt":"2025-06-01T01:45:59","slug":"bind-variables","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/python-oracle\/bind-variables\/","title":{"rendered":"Bind Variables"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the bind variables to pass data to and from Oracle Database.<\/p>\n\n\n\n

An introduction to bind variables #<\/a><\/h2>\n\n\n\n

If you want to pass data to and from the Oracle database, you use placeholders in the SQL statement as follows:<\/p>\n\n\n

sql = ''' SELECT customer_id, name\n          FROM customers\n          WHERE customer_id = :customer_id '''<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
In this query, the :customer_id<\/code> is a placeholder. It is also known as a bind variable or bind parameter.<\/p>\n\n\n\n
When you execute a query using the Cursor<\/code> object, you need to pass the value of the bind variable:<\/p>\n\n\n
cursor.execute(sql, {'customer_id'<\/span>: customer_id})<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, the value of the customer_id variable will be used for the :customer_id<\/code> bind variable in the SQL statement when the query is executed.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
If user data is concatenated with a SQL statement, it will be vulnerable to a SQL injection attack:<\/p>\n\n\n
sql = ''' SELECT customer_id, name\n          FROM customers\n          WHERE customer_id = '''<\/span> + customer_id;<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
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:<\/p>\n\n\n
sql = f''' SELECT customer_id, name\n          FROM customers\n          WHERE customer_id = {customer_id}<\/span>'''<\/span>;<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
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.<\/p>\n\n\n\n
The following example illustrates how to find the customer’s name by id using bind variables:<\/p>\n\n\n
from<\/span> typing import<\/span>  Optional\nimport<\/span> oracledb\nimport<\/span> logging\nfrom<\/span> customer import<\/span> Customer\nfrom<\/span> connect import<\/span> connect\n\n\ndef<\/span> find_by_id<\/span>(customer_id: int)<\/span> -> Optional[Customer]:<\/span>\n\n    sql = ''' SELECT customer_id, name\n              FROM customers\n              WHERE customer_id = :customer_id '''<\/span>\n\n    try<\/span>:\n        with<\/span> connect() as<\/span> connection:\n            with<\/span> connection.cursor() as<\/span> cursor:\n                cursor.execute(sql, {'customer_id'<\/span>: customer_id})\n                row = cursor.fetchone()\n                if<\/span> row:\n                    return<\/span> Customer(customer_id=row[0<\/span>], name=row[1<\/span>])\n                return<\/span> None<\/span> # Return None if no customer is found<\/span>\n    except<\/span> oracledb.Error as<\/span> e:\n        logging.error(f\"Error executing query to find customer by ID: {e}<\/span>\"<\/span>)\n        raise<\/span>\n<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that here is the config.py<\/code> module if you haven’t followed the previous tutorial:<\/p>\n\n\n
username = 'OT'<\/span>\npassword = 'oracle'<\/span>\ndsn = '192.168.2.18\/freepdb1'<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Binding by names #<\/a><\/h2>\n\n\n\n
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:<\/p>\n\n\n
from<\/span> typing import<\/span>  Optional\nimport<\/span> oracledb\nimport<\/span> logging\nfrom<\/span> connect import<\/span> connect\n\nfrom<\/span> dataclasses import<\/span> dataclass\n\n\n@dataclass(frozen=True)<\/span>\nclass<\/span> Product<\/span>:<\/span>\n    product_id: int\n    product_name: str    \n    category_id: int\n    list_price: float\n\ndef<\/span> find_products<\/span>(category_id: int, list_price: float)<\/span> -> Optional[Product]:<\/span>\n\n    sql = ''' SELECT product_id, product_name, category_id, list_price\n              FROM products\n              WHERE category_id = :category_id and list_price > :list_price\n              ORDER BY list_price '''<\/span>\n\n    try<\/span>:\n        with<\/span> connect() as<\/span> connection:\n            with<\/span> connection.cursor() as<\/span> cursor:\n                # Pass the parameter using a dictionary<\/span>\n                cursor.execute(sql, {\n                    'category_id'<\/span>: category_id, \n                    'list_price'<\/span>: list_price\n                })\n                \n                rows = cursor.fetchall()\n                \n                if<\/span> not<\/span> rows:\n                    return<\/span> None<\/span>\n                \n                products = []\n                for<\/span> row in<\/span> rows:\n                    # Create a Customer object from the fetched row<\/span>\n                    product =  Product(\n                        product_id=row[0<\/span>], \n                        product_name=row[1<\/span>],\n                        category_id=row[2<\/span>],\n                        list_price=row[3<\/span>]\n                    )\n                    products.append(product)\n                return<\/span> products\n                \n    except<\/span> oracledb.Error as<\/span> e:\n        logging.error(f\"Error executing query to find customer by ID: {e}<\/span>\"<\/span>)\n        raise<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
The :list_price<\/code> and :standard_cost<\/code> bind variables are named binds:<\/p>\n\n\n
sql = ''' SELECT product_id, product_name, list_price, standard_cost\n          FROM products\n          WHERE list_price >= :list_price and standard_cost <= :standard_cost '''<\/span>        <\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
When executing the SQL statement with named binds, you need to pass the keyword parameter names that match the named binds:<\/p>\n\n\n
cursor.execute(sql, list_price=15<\/span>, standard_cost=13<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Alternatively, you can pass parameters as a dictionary instead of as keyword parameters:<\/p>\n\n\n
cursor.execute(sql, {'list_price'<\/span>: list_price, 'standard_cost'<\/span>: standard_cost})<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
The binding by names allows you to specify meaningful names and use them freely in any position.<\/p>\n\n\n\n
In addition, you just need to specify the name parameters once if a variable is repeated multiple times in a query.<\/p>\n\n\n\n
Binding by positions #<\/a><\/h2>\n\n\n\n
A positional bind is performed when a list of bind values is passed to the Cursor.execute()<\/code> call. For example:<\/p>\n\n\n
<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
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.<\/p>\n\n\n\n
Bind directions #<\/a><\/h2>\n\n\n\n
Bind variables can be IN<\/code> or OUT<\/code>.<\/p>\n\n\n\n
The IN<\/code> bind variables allow you to pass data from Python to Oracle Database while the OUT<\/code> bind variables allow you to get data back from the Oracle Database to Python.<\/p>\n\n\n\n
In the previous examples, you have passed in bind variables to the Oracle Database to query data and used a Cursor<\/code> to fetch the result.<\/p>\n\n\n\n
To have the Oracle Database return data to Python, you need to create a variable by using the Cursor.var()<\/code> method. See the following example:<\/p>\n\n\n
import<\/span> oracledb\nfrom<\/span> connect import<\/span> connect\n\n# construct a PL\/SQL anonymous block<\/span>\nplsql = '''\n    begin \n        select count(*) into :customer_count \n        from customers; \n    end;\n'''<\/span>\n\ntry<\/span>:\n    with<\/span> connect() as<\/span> connection:\n        with<\/span> connection.cursor() as<\/span> cursor:\n            # create a variable<\/span>\n            customer_count = cursor.var(int)\n\n            # execute the pl\/sql anonymous block<\/span>\n            cursor.execute(plsql, customer_count=customer_count)\n\n            # show the value of the variable<\/span>\n            print(f'The number of customers is {customer_count.getvalue()}<\/span>'<\/span>)\nexcept<\/span> oracledb.Error as<\/span> error:\n    print(error)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example:<\/p>\n\n\n\n
First, declare a variable that holds an anonymous PL\/SQL block:<\/p>\n\n\n
plsql = '''\n    begin \n        select count(*) into :customer_count \n        from customers; \n    end;\n'''<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, connect to the Oracle Database and create a new Cursor object from the Connection object.<\/p>\n\n\n\n
Third, create a variable to hold the returned data from the execution of the anonymous PL\/SQL block:<\/p>\n\n\n
customer_count = cursor.var(int)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, execute the PL\/SQL block and receive the returned data:<\/p>\n\n\n
cursor.execute(plsql, customer_count=customer_count)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, show the value of the variable by calling the getvalue()<\/code> method:<\/p>\n\n\n
print(f'Customer count is {customer_count.getvalue()}<\/span>'<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is:<\/p>\n\n\n
The number of customers is<\/span> 319<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use bind variables to pass data to and from Oracle Database.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t