Executing SQL statements is the primary way in which a Python application communicates with Oracle Database. Statements are executed
using the methods PL/SQL statements are discussed in PL/SQL Execution. Other chapters contain information on specific data types and features. See
Batch Statement Execution and Bulk Loading, Using CLOB and BLOB Data, Working with the JSON Data Type, and
Working with XMLTYPE. cx_Oracle can be used to execute individual statements, one at a time. It does not read SQL*Plus “.sql” files. To read SQL files, use a technique like the one in SQL statements should not contain a
trailing semicolon (“;”) or forward slash (“/”). This will fail: This is correct: cur.execute("select * from MyTable") SQL Queries¶Queries (statements beginning with SELECT or WITH) can only be executed using the method
Important Interpolating or concatenating user data with SQL statements, for example Fetch Methods¶After cur = connection.cursor() for row in cur.execute("select * from MyTable"): print(row) Rows can also be fetched one at a
time using the method cur = connection.cursor() cur.execute("select * from MyTable") while True: row = cur.fetchone() if row is None: break print(row) If rows need to be processed in batches, the method cur = connection.cursor() cur.execute("select * from MyTable") num_rows = 10 while True: rows = cur.fetchmany(num_rows) if not rows: break for row in rows: print(row) If all of the rows need to be fetched, and can be contained in memory, the method cur = connection.cursor() cur.execute("select * from MyTable") rows = cur.fetchall() for row in rows: print(row) The fetch methods return data as tuples. To return results as dictionaries, see Changing Query Results with Rowfactories. Closing Cursors¶A cursor may be used to execute multiple statements. Once it is no longer needed, it should be
closed by calling with connection.cursor() as cursor: for row in cursor.execute("select * from MyTable"): print(row) This code ensures that, once the block is completed, the cursor is closed and resources have been reclaimed by the database. In addition, any attempt to use the variable Changing Fetched Data Types with Output Type Handlers¶Sometimes the default conversion from an Oracle Database type to a Python type must be changed in order to prevent data loss or to fit the purposes of the Python application. In such cases, an output type handler can be specified for queries. Output type handlers do not affect values
returned from Output type handlers can be specified on the
The output type handler is expected to be a function with the following signature: handler(cursor, name, defaultType, size, precision, scale) The parameters are the same information as the query column metadata found in Examples of output handlers are shown in Fetched Number Precision, Fetching LOBs as Strings and Bytes and Fetching Raw Data. Also see samples such as samples/type_handlers.py Fetched Number Precision¶One reason for using an output type handler is to ensure that numeric precision is not lost when fetching certain numbers. Oracle Database uses decimal numbers and these cannot be converted seamlessly to binary number representations like Python floats. In addition, the range of Oracle numbers exceeds that of floating point numbers. Python has decimal objects which do not have these limitations and cx_Oracle knows how to perform the conversion between Oracle numbers and Python decimal values if directed to do so. The following code sample demonstrates the issue: cur = connection.cursor() cur.execute("create table test_float (X number(5, 3))") cur.execute("insert into test_float values (7.1)") connection.commit() cur.execute("select * from test_float") val, = cur.fetchone() print(val, "* 3 =", val * 3) This displays Using Python decimal objects, however, there is no loss of precision: import decimal def number_to_decimal(cursor, name, default_type, size, precision, scale): if default_type == cx_Oracle.DB_TYPE_NUMBER: return cursor.var(decimal.Decimal, arraysize=cursor.arraysize) cur = connection.cursor() cur.outputtypehandler = number_to_decimal cur.execute("select * from test_float") val, = cur.fetchone() print(val, "* 3 =", val * 3) This displays The Python See samples/return_numbers_as_decimals.py Changing Query Results with Outconverters¶cx_Oracle “outconverters” can be used with output type handlers to change returned data. For example, to make queries return empty strings instead of NULLs: def out_converter(value): if value is None: return '' return value def output_type_handler(cursor, name, default_type, size, precision, scale): if default_type in (cx_Oracle.DB_TYPE_VARCHAR, cx_Oracle.DB_TYPE_CHAR): return cursor.var(str, size, arraysize=cur.arraysize, outconverter=out_converter) connection.outputtypehandler = output_type_handler Changing Query Results with Rowfactories¶cx_Oracle “rowfactories” are methods called for each row that is retrieved from the database. The
For example, to fetch each row of a query as a dictionary: cursor.execute("select * from locations where location_id = 1000") columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) data = cursor.fetchone() print(data) The output is: {'LOCATION_ID': 1000, 'STREET_ADDRESS': '1297 Via Cola di Rie', 'POSTAL_CODE': '00989', 'CITY': 'Roma', 'STATE_PROVINCE': None, 'COUNTRY_ID': 'IT'} If you join tables where the same column name occurs in both tables with different meanings or values, then use a column alias in the query. Otherwise only one of the similarly named columns will be included in the dictionary: select cat_name, cats.color as cat_color, dog_name, dogs.color from cats, dogs Fetching Oracle Database Objects and Collections¶Oracle Database named object types and user-defined types can be fetched directly in queries. Each item is represented as a Python object corresponding to the Oracle Database object. This Python object can be traversed to access its elements. Attributes including
For example, if a table cur.execute("select geometry from mygeometrytab") for obj, in cur: dumpobject(obj) Where def dumpobject(obj, prefix = ""): if obj.type.iscollection: print(prefix, "[") for value in obj.aslist(): if isinstance(value, cx_Oracle.Object): dumpobject(value, prefix + " ") else: print(prefix + " ", repr(value)) print(prefix, "]") else: print(prefix, "{") for attr in obj.type.attributes: value = getattr(obj, attr.name) if isinstance(value, cx_Oracle.Object): print(prefix + " " + attr.name + ":") dumpobject(value, prefix + " ") else: print(prefix + " " + attr.name + ":", repr(value)) print(prefix, "}") This might produce output like: { SDO_GTYPE: 2003 SDO_SRID: None SDO_POINT: { X: 1 Y: 2 Z: 3 } SDO_ELEM_INFO: [ 1 1003 3 ] SDO_ORDINATES: [ 1 1 5 7 ] } Other information on using Oracle objects is in Using Bind Variables. Performance-sensitive applications should consider using scalar types instead of objects. If you do use objects, avoid calling
Limiting Rows¶Query data is commonly broken into one or more sets:
The latter can be handled by calling
‘Web pagination’ and limiting the maximum number of rows are discussed in this section. For each ‘page’ of results, a SQL query is executed to get the appropriate set of rows from a table. Since the query may be executed more than once, make sure to use bind variables for row numbers and row limits. Oracle Database 12c SQL introduced an myoffset = 0 // do not skip any rows (start at row 1) mymaxnumrows = 20 // get 20 rows sql = """SELECT last_name FROM employees ORDER BY last_name OFFSET :offset ROWS FETCH NEXT :maxnumrows ROWS ONLY""" cur = connection.cursor() for row in cur.execute(sql, offset=myoffset, maxnumrows=mymaxnumrows): print(row) In applications where the SQL query is not known in advance, this method sometimes involves appending the For Oracle Database 11g and earlier there are several alternative ways to limit the number of rows returned. The old, canonical paging query is: SELECT * FROM (SELECT a.*, ROWNUM AS rnum FROM (YOUR_QUERY_GOES_HERE -- including the order by) a WHERE ROWNUM <= MAX_ROW) WHERE rnum >= MIN_ROW Here, SELECT * FROM (SELECT a.*, ROWNUM AS rnum FROM (SELECT last_name FROM employees ORDER BY last_name) a WHERE ROWNUM <= 20) WHERE rnum >= 1 This always has an ‘extra’ column, here called RNUM. An alternative and preferred query syntax for Oracle Database 11g uses
the analytic SELECT last_name FROM (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) AS myr FROM employees) WHERE myr BETWEEN 1 and 20 Make sure to use bind variables for the upper and lower limit values. Client Result Cache¶Python cx_Oracle applications can use Oracle Database’s Client Result Cache The CRC enables client-side caching of SQL query (SELECT statement) results in client memory for immediate use when the same query is re-executed. This is useful for reducing the cost of queries for small, mostly static, lookup tables, such as for postal codes. CRC reduces network round-trips, and also reduces database server CPU usage. The cache is at the application process level. Access and invalidation is managed by the Oracle Client libraries. This removes the need for extra application logic, or external utilities, to implement a cache. CRC can be enabled by setting the database parameters SQL> ALTER SYSTEM SET CLIENT_RESULT_CACHE_LAG = 3000 SCOPE=SPFILE; SQL> ALTER SYSTEM SET CLIENT_RESULT_CACHE_SIZE = 64K SCOPE=SPFILE; CRC can alternatively be configured in an oraaccess.xml or sqlnet.ora file on the Python host, see Client Configuration Parameters. Tables can then be created, or altered, so repeated queries use CRC. This allows existing applications to use CRC without needing modification. For example: SQL> CREATE TABLE cities (id number, name varchar2(40)) RESULT_CACHE (MODE FORCE); SQL> ALTER TABLE locations RESULT_CACHE (MODE FORCE); Alternatively, hints can be used in SQL statements. For example: SELECT /*+ result_cache */ postal_code FROM locations Fetching Raw Data¶Sometimes cx_Oracle may have
problems converting data stored in the database to Python strings. This can occur if the data stored in the database doesn’t match the character set defined by the database. The encoding_errors parameter to The following sample demonstrates how to use this feature: # define output type handler def return_strings_as_bytes(cursor, name, default_type, size, precision, scale): if default_type == cx_Oracle.DB_TYPE_VARCHAR: return cursor.var(str, arraysize=cursor.arraysize, bypass_decode=True) # set output type handler on cursor before fetching data with connection.cursor() as cursor: cursor.outputtypehandler = return_strings_as_bytes cursor.execute("select content, charset from SomeTable") data = cursor.fetchall() This will produce output as: [(b'Fianc\xc3\xa9', b'UTF-8')] Note that
last value = data[0][0].decode("UTF-8") This will return the value “Fiancé”. If you want to save with cx_Oracle.connect(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb1") as conn: with conn.cursor() cursor: var = cursor.var(cx_Oracle.DB_TYPE_VARCHAR) var.setvalue(0, b"Fianc\xc4\x9b") cursor.execute(""" update SomeTable set SomeColumn = :param where id = 1""", param=var) Warning The database will assume that the bytes provided are in the character set expected by the database so only use this for troubleshooting or as directed. Querying Corrupt Data¶If queries fail with the error “codec can’t decode byte” when you select data, then:
If data really is corrupt, you can pass options to the internal decode() used by cx_Oracle to allow it to be selected and prevent the whole query failing. Do this by creating an
outputtypehandler and setting def output_type_handler(cursor, name, default_type, size, precision, scale): if default_type == cx_Oracle.DB_TYPE_VARCHAR: return cursor.var(default_type, size, arraysize=cursor.arraysize, encoding_errors="replace") cursor.outputtypehandler = output_type_handler cursor.execute("select column1, column2 from SomeTableWithBadData") Other codec behaviors can be chosen for INSERT and UPDATE Statements¶SQL Data Manipulation Language statements (DML) such as INSERT and UPDATE can easily be executed with cx_Oracle. For example: cur = connection.cursor() cur.execute("insert into MyTable values (:idbv, :nmbv)", [1, "Fredico"]) Do not concatenate or interpolate user data into SQL statements. See Using Bind Variables instead. See Transaction Management for best practices on committing and rolling back data changes. When handling multiple data values, use
Inserting NULLs¶Oracle requires a type, even for null values. When you pass the value None, then cx_Oracle assumes the type is STRING. If this is not the desired type, you can explicitly set it. For example, to insert a null Oracle Spatial SDO_GEOMETRY object: type_obj = connection.gettype("SDO_GEOMETRY") cur = connection.cursor() cur.setinputsizes(type_obj) cur.execute("insert into sometable values (:1)", [None]) Which of the following are valid cursor methods used to execute SQL statements Mcq?Explanation: The five steps are Declare Cursor,Open,fetch,CLose and Deallocate.
What are the following types of cursor in SQL?There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors. These are explained as following below. Implicit Cursors: Implicit Cursors are also known as Default Cursors of SQL SERVER.
What are the four types of cursor?SQL Server Different Types of Cursors. Static Cursors. A static cursor populates the result set at the time of cursor creation and the query result is cached for the lifetime of the cursor. ... . Dynamic Cursors. ... . Forward Only Cursors. ... . Keyset Driven Cursors.. What is cursor method in SQL?In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.
|