Scribd
Upload
112 views23 pages

Using Python With Oracle Database 11g

Installing

Uploaded by

Suresh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
112 views23 pages

Using Python With Oracle Database 11g

Installing

Uploaded by

Suresh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
avaons Using Python With Oracle Database 11g Using Python With Oracle Database 11g Purpose ‘This tutorial shows you how to use Pytnon with Oracle Database 11g Time to Complete ‘Approximately 1 hour Overview Python is a popular general purpose dynamic scripting language. With the rise of Frameworks, Python is also becoming common for Web application development. ifyou wantto use Python and an Oracle database, this tutorial helps you get started by giving ‘examples. Hfyou are new to Python review the Appendix: Pyihon Pamer to gain an understanding of te language. Prerequisites 1, Install Oracle Database 11.2 2. Install he Python 2x and the ex_Oracle 5.0 extension ‘3. Extract these files to your $HOME location. Connecting to Oracle ‘To create a connection to Oracle, perform the following steps. 1. Open a terminal window and review the code contained in $aoME/connect. Py [pythonhol@locathost ~]$ more [Link] import cx Oracle con = cx Oracle. connect (' pythonhal /welcome@locatnost/orct' ) print con. version [Link]() [pythonhot@tocathost ~1$ The cx_Oraclle module is imported to provide the API for accessing the Oracle database. Many inbuilt and third party modules can be included in this way in Python scripts. The connect: |) method is passed the usemame "pythonhol", the password Welcome” and the connection sting. In this case, Oracle's Easy Connect connection string syntax is used. It consists ofthe IP of your machine and the DB service name “ore! The ciose() method closes the connection. Any connections nat explicitly closed will be automatically released when the script ends. Ina command line terminal run python [Link] [pythonholghosto1 ~}$ python connect. py [Link].0 [pythonhot@hosto1 ~]s 9 the connection succeeds, the version number is printed: An exception is thrown ifthe connection falls. 2.. Indentation is used in Python to indicate the code structure. There are no statement terminators unlike many other hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him 13 avaons Using Python With Oracle Database 11g languages and there are no beginiend keywords or braces to indicate blocks of code. Open connectpy in an editor. indent the print statement by two spaces ad save the fle con = cx Oracle. connes! ‘Print [Link] ' pythonhol /welcone@iocaihost /or: con. close ( Run the script: python connect py [pythonhol@hosto1 ~]$ python connect. py File “[Link]", line 4 print con. version SyntaxError: invalid syntax [pythonhot@hostol ~|$ The number of spaces or tabs used for indentation isnot important as long as it's used in a consistent way in each black, Here the Python interpreter is not expecting a new code block level afer the connect) call so twamns about the diferent indentation, In other cases such as in if and loop blocks (shown later), take care that all statements in each block are equally Indented, Myou are copying and pasting fom this tutorial, check he pasted indentations comectbefoe running each example, Python treats everything as an object. The “con” object has a “version” attribute, which is a string Change the script to use a “split” string method too: con = ex Oracle. conne: ver = con. version. «plit(" print ver con. close ( Re-run the script in the command line terminal: python connect py [pythonhol@hosto1 ~]$ python connect. py [ity 22h) eh, Oats Beh [pythonhol@hosto1 ~]$ ff The outputis a “ist”, which is a Python's name for an array. Python Lists can be accessed via indexes Change [Link] to: port cx_Oracle con = ex [Link] ('pythonhol /welcome®?[Link]/0: ver = [Link]: print ver Print ver{0) print ver{-1] print ver(1:4] Re-run the script in the command line terminal hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him 2 avaons Using Python With Oracle Database 11g python connect py [pythonhol@hoste1 ~]$ python connect .py [EdD itt! ac ABE] @ [eae ets ta [pythonhol@hostol ~]$ ff Python Lists are zero based, 50 ver[0] prints the first element of the list. The last element ofthe listis ver-1].A list slice is created by ver{1:4] This retums the elements starting at positon 1 and up to, but not including, elements ‘rom position 4 «Python lists have methods and can also be manipulated with operators, Change [Link] to: import ex_oracie con = ex [Link] ('pythonhol/welcome®?[Link]/oreL") ver = [Link].9p print ver Print [Link] ("1") [Link] ("2") print ver veel = [11 ver2 28 print verl + ver? con. close Re-run the serpt in the command line terminal python [Link] [pythonhol@host91 ~]$ python connect. py [tras "28, 784, 114, te") 3 ['11", '0", *1', '0'] HRN 2] The index("1") method returns the index of the "1" element, counting rom zero. The remove("2") method deletes an element from the list. The "+" operator can be used to join two list. Other datatypes Python has are Dictionaries, which are associative arrays, and a type called a tuple, which is lke a list but cannot be changes. Loops can be used to iterate over lists. Change connect .py to: for v in vari print v Print "Not 11" con. close ( Make sure the indentation is correct! The colon are both inside the loop. Re-run the script in the command line terminal hitpalww oracle. comMvebolderechnetwork?utorils/belebfoow Olaython_dblpyon_db.him Used to indicate a code block. The first print and ifare at the same level of indentation because they 323 arse015 Using Python With Oracle Database 11g python [Link] [pythonhol@host81 ~]$ python [Link] nl It's 2 Not 12 eo Not 12 1 Not 12 eo Not 11 [pythonhol@host91 ~]$ ‘The loop prints and tests each value from thelist in tur. Using Database Resident Connection Pooling Database Resident Connection Pooling is a new feature of Oracle Database 11g. Its useful {or short lived scripts such as typically used by web applications. it allows the number of ‘connections to be scaled as web site usage grows. It allows multiple Apache processes on ‘multiple machines to share a small pool of database server processes. Without DRCP, a Python connection must start and terminate a server process. Below leftis diagram of nonpooting. Every script has ils own database server proces. ‘Scripts not doing any database work stil hold onto a connection until tne connection is. ‘closed and the server is terminated. Below rightis a diagram with DRCP. Scripts can use database servers from a pool of servers and return them when no longer needed (Coes Reauests) (Ctiaaie Tie), (Database Ter) (Web Requests) (iiddie Tier) (Catabase Ter), Si Batch scripts doing long running jobs should generally use non-pooled connections. This tutorial shows how DRCP can be used by new or existing applications without witing ‘or changing any application logic. Perform the following steps: 1. Review the code contained in $HoME/connect_drop.py hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him 43 avaons Using Python With Oracle Database 11g [pythonhoi@tocathost ~]$ more connect_drcp.py limwort cx oracte lcon = cx_Oracte.connect(*pythonnot’, *welcone', ‘Localnost/orct: pooled’ , celass = "PYTHONHOL", purity = cx [Link] PURITY SELF) lprint [Link] leon. ctose() [pvtnonhol@lacathost ~1$ This is similar o connectpy but “pooled is appended to the connection string. A Connection Class "HOL"is also passed into the connect() method and the "purity" ofthe connection is defined as the ATTR PURITY SmLF constant, The connection class tells the database server pool thal connections are related. Session information (such as the default date format) might be retained between connection call, giving performance benefits. Session information willbe discarded ifa pooled server is later reused by an application wih a different connection class name. ‘Applications that should never share session information should use a different connection class andor use [ATTR_PURITY_NBW to force creaton of a new session. This reduces overall scalabilty but prevents applications mmis-iSing session information Run & L_dzep.py python connect_drep.py [pythonhol@host0l ~]$ python connect_drcp.py [Link].0 [pythonhot@hosto1 ~]$ [] The outputs again simply the version of the database. Scriptiogic does not need to be changed to benefit from DRCP connection pooling, Creating a Simple Query ‘[Link] task when developing Web applications is to query a database and display the results in a Web browser. There are a number of functions you can use to query an Oracle database, bul the basics of querying are always the same 1. Parse the statement for execution. 2. Bina data values (optional) 43. Execute the statement 4, Fetch the results from the database, To create a simple query, and display the results, perform the following steps. 1. Review the code contained in $HOME/[Link] [pythonhot@locathost ~]§ more query. py limport cx_Oracte con = cx Oracle. connect (' pythonhol/welcome@locathost/orct") cur = [Link]() [Link]( ‘select * from departments order by department_id") for result in cur: print result [Link]() [Link]() [pythonhot@tocathost ~1$ The cursos {) method opens a cursor for statements to use, The execure |) method parses and executes the statement. The loop fetches each row from the cursor and prints i hitpalww. oracle. comMebolderechnework?utorils/belebfoow laython_dblpyton_db.him 523 avaons Using Python With Oracle Database 11g Run the scriptin a terminal window: python query-Py [pythonhotghosto1 ~]$ python query. py (18, ‘Adninistration’, 200, 1700) (28, ‘Marketing’, 201, 1808) (38, ‘Purchasing’, 114, 1760) (40, ‘Hunan Resources’, 203, 2490) (58, "Shipping", 121, 1500) (60, ‘IT’, 103, 1400) (78, ‘Public Relations’, 264, 2700) (80, "sales", 145, 2500) (90, ‘Executive’, 100, 1706) (100, ‘Finance’, 108, 1700) (110, ‘Accounting’, 205, 1700) (120, ‘Treasury’, None, 1760) (130, ‘Corporate Tax’, None, 1700) (140, ‘Control And Credit’, None, 1700) (150, ‘Shareholder Services’, None, 1700) (160, 'Benefits', None, 1760) (170, ‘Manufacturing’, None, 1700) (188, 'Construction', None, 1708) (190, ‘Contracting’, None, 1700) (208, ‘Operations’, None, 1700) (210, ‘IT Support’, None, 1700) (228, ‘NOC’, None, 1708) (230, ‘IT Helpdesk", None, 1700) (248, ‘Government Sales", None, 1706) (250, ‘Retail Sales’, None, 1700) (268, ‘Recruiting’, None, 1760) (270, ‘Payroll’, None, 1768) [pythonhol@hostol ~]$ ff The results of he query are displayed as Python tuples’, which are arrays that cannot be changed Fetching Data ‘There are a number of ways to fetch data from an Oracle database. Perform the following steps. 1. Review the code contained in $E0ME/query_one.py [pythonhol@locathost ~]$ more query [Link] limport cx_Gracte Icon = cx_Oracle. connect (' pythonhol/welcome@locathost/orct') lcur = [Link]() [Link]( ‘select * from departments order by department_id") row = [Link]() print row row = [Link]() print row [Link]() [Link]() [pythonhoL@Locathost ~1$ This uses the fetchone() method to return just a single row as a tuple. When called multiple time, consecutive rows are returned Run the scrptin a terminal window: hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him 623 avaons Using Python With Oracle Database 11g python query _one.py [[pythonhot@hosto1 ~]$ python query_one.py (18, ‘Administration’, 200, 1700) (20, ‘Marketing’, 201, 1800) [pythonhotghosto1 ~1s The two fetchone( calls print wo records. Review the code contained in $E0ME/query_many.pY [pythonhot@locathost ~]$ more query many. py limport cx_Oracte con = cx Oracle. connect (' pythonhol/welcome@localhost/orcl") cur = [Link]() [Link]( ‘select * from departments order by department_id") res - cur. fetchmany (numRows~3) print res [Link]() [Link]() [pythonnoL@Locathost ~]$ The fetchmany () method retuns alist oftuples. Here the be retumed, sesious parameter specifies that three rows should Run the scrptin a terminal window: python query_nany.py Tpythonhol@hostOl ~1$ python query_nany py [(10, *Adninsstration’, 200, 1706), (20, "Marketing", 201, 1900), (30, ‘Purchasing’, 114, 1709)]| Ipythonholghostel ~1$ The frst three rows ofthe table are retumed as a list of tuples. Review the code contained in sHoME/query [pythonhol@locathost ~]$ more query_all.py limport cx_Gracte Icon = cx_Oracle. connect ( ‘ pythonhol/welcome@locathost/orct’) lcur = [Link]() [Link]( ‘select * from departments order by department_id") res - [Link]() lprint res [Link]() [Link]() [pythonhoL@Locathost ~]$ Run the scriptin a terminal window: python query_all.py pytneanctenoster =1s pyt 0, Aansns jaman’rescurces, ipping”, 121, 1500), (50, "iT", 163, 1809), (70, “Puotic Retaticns| 704, 2700), (80, "Sates", 145, 2500), (90, "Executive", 190; 1700), (160, ‘Finance’, 108, 1700), (110 icccanting’ . 205, 1706). "(120 ‘Treasury’. None, 1700), (130, ‘Corporate Tak", None, 1790), (140._‘CoF lrrot. tna Creait’, Nore, 1760), (150, "sharehotaer Services’, None, 1760), (169, Benefits’, Hone, 1760) | (G70, “Manutacrurang™, Wone,'2700), (180, "constrvction’, one, 1709), (290, ‘contracting’, none, 1700) |. (206, ‘operations’, tone, 1700), (210, ‘IT Support", None, 1700), (229, “NOC”, None, 1790}, (236, ‘IT fietpaesk", None, 1706), (240, "Government Sales", lone, 1769), (250, "Retail Sates", None, 1706), (260, Recruiting", Hone, 1706), (270, “Payrott’, one, 1709} ltpythonnot@hostor ~15 hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him m3 avaons Using Python With Oracle Database 11g This uses the fetchall{) method to return all rows. The outputs a list (Python's name for an array) of tuples. Each tuple contains the data for one row. 3. The listcan be manipulated in the any Python manner, Edit $HoME/query_al1..py and change the code to the following (in bold) and rerun the script again import cx_oracle con = cx_oracle, connect ('pythonhol /xelcomeBlocaThost/ore!") execute (‘select * ‘cur .fetehall () © by department id") print F [pythonhol@host81 ~]$ python query [Link] (10, ‘Administration’, 290, 1760) (20, ‘Marketing', 261, 1800) (30, ‘Purchasing’, 114, 1700) (40, ‘Hunan Resources’, 203, 2490) (50, ‘Shipping’, 121, 1590) (60, ‘IT’, 163, 1498) (70, ‘Public Relations’, 204, 2709) (80, ‘Sales’, 145, 2560) (98, ‘Executive’, 160, 1700) (190, ‘Finance’, 108, 1780) (110, ‘Accounting’, 265, 1700) (120, ‘Treasury*, None, 1700) (130, ‘Corporate Tax', None, 1700) (149, ‘Control And Credit’, None, 1700) (150, ‘Shareholder Services*, None, 1706) (160, ‘Benefits’, None, 1700) (170, ‘Manufacturing’, None, 1700) (180, ‘Construction’, None, 1760) (190, ‘Contracting’, None, 1706) (206, ‘Operations’, None, 1760) (216, ‘IT support’, None, 1780) (220, ‘NOC’, None, 1760) (230, ‘IT Helpdesk", None, 1766) (240, ‘Government Sales’, None, 1700) (250, ‘Retail Sales’, None, 1760) (260, ‘Recruiting’, None, 1700) (270, ‘Payroll’, None, 1760) [pythonhol@host91 ~]$ Now each tuple is printed separately. The choice of which fetch method to use will depend on how you want to process the returned data, Improving Query Performance ‘This section demonstrates a way o improve query performance by increasing the number of ntipuwwww_.[Link]/tutorials/obeldb/oow1Olpython_dbipytivon_db.him avaons Using Python With Oracle Database 11g rows retuned in each batch from Oracle to the Python program. Perform the following steps: 1. First, create a table with a large number of rows. Review $HOME/query_arraysize.sql I[pythonhot@tocathost ~]$ more query [Link] et echo on hirop table bigtab; reate table bigtab (nycol varchar2(2@)) ; pegin for i in 1..20000 Loop insert into bigtab (mycol) values (dbms_random.string('A',20)); end loop; how errors ‘onmit; I[pythonhot@tocathost ~1s Ina terminal window use SQL“Plus to un the script aqipius pythonho} /weicone#iocaihost/orc) equery_arraysize exit SOL> @query_arraysize SaL> SOL> drop table bigtab; drop table bigtab ERROR at Line 1: ORA-00942: table or view does not exist SQL> create table bigtab (mycol varchar2(20}); Table created. SOL> begin 2 for 4 in 1.20006 3 Loop 4 insert into bigtab (mycol) values (dbns_random.string('A’,26)); 5 end Loop; 6 end; Hf PL/SQL procedure successfully conpleted. SaL> SOL> show errors No errors. saL> SQL> commit; Commit complete. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release [Link].1 With the Partitioning, OLAP, Data Mining and Real Application Testing optii [pythonhot@hosto1 ~|$ hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him 9 avaons + Edit saoue/q Using Python With Oracle Database 11g «Review the codecontained in $home/query_arraysize.py [pythonhol@locathost ~]$ nore query_arraysize.py limport time limport cx_Oracte con = cx Oracle. connect‘ pythonhot/wetcone@locathost/orct') start - [Link]() lcur = [Link]() [Link] = 100 [Link]( ‘select * from bigtab’) lres = cur. fetchall() }é print res # uncomment to display the query results elapsed = ([Link]() - start) print, elapsed, "seconds" [Link]() [Link]() [pythonhol@tocathost ~1s This uses the ime’ module to measure elapsed time of the query. The arraysize is Seto 100. This causes batches of 100 records ata time to be retumed fom the database to a cache in Python. This reduces the number of "roundtrips" made to the database, often reducing network load and reducing the number of context switches on the database server. The fet chione (), fetchisany () and even feccha11() methods wil read from the cache before requesting more data from the database. From a terminal window, run python query_arraysize.py [[pythonhol@hostoi ~1$ python query arraysize.py_ Jo.0545se002359 seconds [pythonhoighosto1 ~1$ Reload a few times to see the average times. [pythonhol@hostol ~]$ python query arraysize. py 0.0363039970398 seconds [pythonhol@nosto1 ~]$ python query [Link] 0.0355949401855 seconds [pythonholehosto1 ~]§ python query [Link] 0.0352649688721 seconds [pythonhol@hostel ~}$ Note: The time values you observe may differ from those displayed in the screenshots because of hardware or system load differences, y_azraysize.py and change the arraysize from to cur-arraysize = 2000 Rerun the scripta few times and compare the performance of he two arraysize settings. In general, larger array sizes improve performance. Depending how fast your systems, you may need to use different arraysizes than those given here to see a meaningful ime difference. python query, [Link] hitpalwworacte-com/vebolderechnetworkutorils/belebfoow Olaython_dblpyton_db.him 1073 avaons Using Python With Oracle Database 11g [pythonhol@hostOl ~1$ python query [Link] Jo.0255860195923 seconds [pythonhol@host91 ~|$ python query [Link] Jo.0248520510101 seconds [pythonholghosto1 ~]$ python query [Link] Jo..0240499973207 seconds [pythonhol@host91 ~]$ python query [Link] Jo.0242881774902 seconds [pythonhotghost91 ~]$ The default araysize used by cx_cracle Is 60, There isa time/space tradeoff for increasing the arraysize, Larger arraysizes will require more memory in Python for buffering the records. Noto: The time values you observe may differ from those displayed in the screenshot because of harcware or system load differences. Using Bind Variables Bind variables enable you to re-execute statements with new values, without the overhead ‘of reparsing the statement. Bind variables improve code reusability, and can reduce the risk ‘of SQL Injection attacks. ‘To use bind variables in this example, perform the following steps. 11, Review the code contained in s:i0Me/bing_query.oy limport cx_Oracte lcon cx_Oracle. connect ( ' pythonhol/welcome@Locathost/orcl') cur = [Link]() [Link]( ‘select * from departments where department_id = :10")| [Link](None, {*id": 210}) res = [Link]() print res [Link](None, {"id': 110}) lres = cur. fetchalt() print res [Link]() [Link]() [pythonhol@tocathost ~]s The statement contains a bind variable "ia", The statements only prepared once but executed twice with diferent values for the WHERE clause. The special symbol None’is used in place ofthe statement text argument fo execute () because the prenare () method has already set the statement, The second argument o the execate () callis a Python Dictionary. In the first execute call, this associative array has the value 210 for the key of id” The frst execute uses the value 210 for the query. The second execute uses the value 110. From a terminal window, run python bind_query.py [pythonhol@hostoi ~1§ python bind_query.py [(219, "IT Support", None, 1709)] [(119, ‘Accounting’, 265, 1700)] [pythonhot@hosto1 ~|$ ff The output shows the details for the two departments. hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him 1173 avaons Using Python With Oracle Database 11g 2. The cx_cracle driver supporis array binds for INSERT statements, which can greatly improve performance over single row inserts, Review the following commands to create a table for inserting data: sqlplus pythonhol drop table mytab; create table aytab (id number, data varchar? (20)); exit /welconel localhost /orel Run SQL*Plus and cut-and-paste the commands. [eythonkotelocaihost =15 drop table mytat; lirop taple myran leRROR at Line 1: loRa-20942: table or view coes rot exist Ka~ create table nytab (4d number, data varchar2(20)); fable created. Isau> exit, lpssconnected trom oracla Database 129 Enterprise Edition Roleaca [Link].0 - Production \ith the Partitioning, OLAP, Data Mining and Real Application Testing options Ipytnonnotetocatnost ~13 3, Review the code contained in $HOME/bind_ingert.py hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him avaons Using Python With Oracle Database 11g [pythonhot@locathost ~]$ more bind [Link] limport cx_Oracle con = cx_Oracle. connect (' pythonhol /welcome@localnost/orct' ) Fourth) (5, "Fatt (6, "sixth (7, "Seventn™ ) 1 cur = [Link]() [Link] = 7 [Link][int, 20) [Link]("insert into mytab(id, data) values (: ", rows) ‘con. commit () Now query the results back lcur2 = [Link]|) [Link]('select * fron mytab') res = [Link]() print res [Link]() |[Link]() [Link]() [pythonhoL@Locathost ~]$ The rows’ array contains the data to be inserted. ‘The bindarraysize Is here set to 7, meaning to insert all seven rows in one step. The setinputsizes() call describes the columns. The first column is integral. The second column has a maximum af 20 bytes. call inserts all seven rows, The commit call is commented out, and does not execute. The final part ofthe script queries the results back and displays them as a list of tuples. From a terminal window, run python bind [Link] [fpythonhet@rostox 1s python bind insert. py Ica, "Farst'), (2, ‘Secona), (3, ‘Tnird'), (4, tpytnonnctenostor 1s Fourth’), (S, ‘Fatth'), (6, ‘Sixth"), (7, *Seventh')] The new resulls are automatically rolled back at the end of the script so re-running the script will always show the same number of rows in the table. Creating Transactions When you manipulate data in an Oracle Database (insert, update, or delete data), the ‘changed or new data is only available within your database session until itis committed to the database. When the changed data is committed Io the database, itis then available to ‘other users and sessions, This is a database transaction, Perform he following steps: 1. Edit snomz/bind_ingest..py and uncomment the commit call (in bold below): import cx Oracle con = ex_Oracle.connect ("pythonhol/welcome®?[Link]/orcL") hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him 19723 arse015 Using Python With Oracle Database 11g rows = ( (1, “Firs=" ), (2, “Second” }, G, “third” ), (1, “foursh” }, (S, “ELEtR ), (6, “Sixth” )y (1, "Severth™ ) 1 cur = [Link] (} cur-bindarraysize = 7 cur setinputsizes (int, 20) cur-executenany ("insert into mytab(id, data) values (:1, :2)", rows) con. commit () # Now query the results back cur? = con. cursor () cur? execute |'select * res ~ [Link] () print res om nytab") cur-clese() [Link]() [Link]() The commit() is on the connection, not on the cursor. Rerun the script several times and see the number of rows in the table increasing each time: python bind_insert.py [pytonnctenost1 =13 python bind [Link] (C2, "PArst'D, (2, "Second" ), [By "TaLee ), Cy "FOUR Dy (By FAFEN), (Oy “SERED, (2, Seventh’), ( h, “eirst'), (2, ‘Secona'), (3, ‘thica’), (4, “Fourth'), (S, “Fufen’), (6, *Sixtn'], (7, “Severtn’)] Ipytnonnotehos tba =1$ python bing insertpy (1, "Faeet"), (2, *Seeond"), (3, "Tate ). (4, "Fourth ), (5, "FEFEN), (6, *SLeER'), (2, Seventh"). h, ‘earst’), (2, ‘Second'), (3, ‘Thara'), (4, “Fourth"), (5, “Fattn’), (6, ‘Sixth"), (7, “Seventh"), (2, Furst), (2, “Secone"), (3, "Tra, (4, “HourRN'), (3, SALTED, UB, “SaxtH), (7, “seventh” 7) teytnonnotenostor =15 Hyou need to initiate a rollback ina script, the con. rol back () method can be used. In general you want all or none of your data committed. Doing your own transaction control has performance and data-integrity benefits. Using PL/SQL Stored Functions and Procedures PUSQL is Oracle's procedural language extension to SQL. PLISQL procedures and functions are stored and run in the database. Using PL/SQL lets all database applications reuse logic, no matter how the application accesses the database. Many data-related ‘operations can be performed in PL/SQL faster than extracting the data into a program (for ‘example, Python) and then processing it. Oracle also supporls Java stored procedures. In this tutorial, you will ereate a PL/SQL stored function and procedure and call them in Python scripts. Perform the following steps: 1, Start SQL*Plus and create a new table, ptab with the following command: agiplus pythonhol /welcomet ocathost/orel create table ptab (mydata varchar(20), myid number) ; exit hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him avaons Using Python With Oracle Database 11g [pythonkotBlocalhost ~15 sqlplus pythonhol/welcome@localhost/orct lsot+Ptus: Retense [Link].0 Production on Tue Jul 20 17:52:45 2010 copyright (c} 1982, 2609, Oracle. ALI rights reserved connected to loracte patapase 119 Enterprise Edition Release [Link].9 - Production the Partitioning, OLAP, Data Mining anc Real Application Testing options fboL> create table ptsb (aydata varchar(z9), myid number): [Table created. lsoL> exit lbssconnectea rrom oracle Database 11g Enterprise Edition Release [Link].0 - Production (ith the Partitioning, OLAP, Data Mining ang Real Application Tecting options [pythonkoLelocathost 18 2. Review $nome/ezeate_fune.sql which creates a PLISOL stored function nyFunc () to insert a row into the ptab table, and return double the inserted value’ [pythonhot@locathost ~1$ more create _func.sql create or replace function nyfuncid_p in varchar2, 1_p in number) return nunber as lbegin insert into ptab (mydata, myid) values (dp, ip); return (ip * 2): lena: y [pythonhot@tocathost ~1$ Start SQL*Plus and run the script agiplus pythonhol/welcomet locathost/orel set eche on Boreate_fune show errors exit SOL> @create_func SQL> create or replace function 2 myfunc(d_p in varchar2, ip in number) return number as 3. begin 4 insert into ptab (nydata, myid) values (dp, ip); 5 return (ip * 2); 6 end; 77 Function created SQL> show errors No errors. SQL> 3. Review the code in $HoME/pisqi_func.py hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him avaons Using Pyten Wit Oracle Daabase 119 [pythonhet@locathost ~]$ more plsqt_func.py limport cx_Oracte con = cx Oracle. connect (' pythonhol/welcome@locathost/orct’) lcur = [Link](} res = [Link]('myfunc*, cx [Link], (‘abe", 2)) print res [Link]() [Link]() [pythonhot@tocathost ~1$ This uses cal func () to execute the function, The constant cx_oracle NUMBER indicates that the return value is numeric. The two PL/SQL function parameters are passed as a luple and bound to the function parameter arguments, From a terminal window, run python plsql_func.py [pythonhoU@hosto1 ~]$ python plsql_tunc. py 4.0 [pythonholghosto1 ~]s The outputs result of the PL/SQL function calculation « Tocall aPLISQL procedure, use the [Link](} method, Review $#0Mt/create_proc. sql which creates a PLISQL procedure mypzoc!) to accept two parameters. The second parameter contains an OUT return value. [pythonhol@locathost ~]$ more create proc. sql create or replace procedure Inyproc(vi_p in number, v2_p out number) as began vp - vip * 2; / [pythonhol@locathost ~]$ Start SQL*Plus and run the script sqiplus pythonhol /welconet locathost/orcl fet echo on Bereate proc exit SQL> Gcreate proc SQL> create or replace procedure 2° myproc(v1_p in number, v2_p out number) as 3 begin 4 v2pizvipt2 5 end; 6/ Procedure created. SQL> show errors No errors. so ff hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him avaons Using Python With Oracle Database 11g 5. Review the code contained in $HomE/pleql_proc. Py. [pythonhol@locathost ~]$ more plsql_proc.py limport cx_Oracte Icon = cx Oracle. connect (' pythonhol/welcome@localnost/orct' ) lcur = [Link]() Inyvar = cur. var(cx_Oracle.NUNBER) [Link](*myproc’, (123, myvar)) lprint [Link]() [Link]() [Link]() [pythonhol@locathost ~]$ [pytnonhougtocatnost ~1$ [pytnonhovgtocatnost ~]$ This creates a numeric variable myvar to hold the cur parameter, The number 123 and the return variable name ‘are bound to the procedure call parameters using a tuple. From a terminal window, run python pisql_proc.py [pythonhol@hostO1 ~]$ python ptsql_proc.py 246.0 [pythonhoLghosto1 ~]s The getvaiue() method displays the retumed value Using Continuous Query Notification Continuous Query Notifcation (also called Database Change Notifeation) allows applications to receive notations when a table changes, for example when rows have been inserted This can be useful in many citaumstances, including for mid-ter eache invalidation. A cache mighthold some values that depend on data n a fable. the table changes, ne cached values must be updated withthe new information. ‘This example show DCN events being handled in Python. Perform the following steps: 1, Review the code contained in $HoME/den py hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him 1773 avaons Using Python With Oracle Database 11g [pythonhol@locathost ~1§ more [Link] import cx_Oracte Jaet DCNCaLtvack(message) print "Notification: for tab in [Link]: print "Table:", [Link] for row in tab. rows: if [Link] & cx Oracle.OPCODE_INSERT print "INSERT of rowid:", [Link] if [Link] & cx [Link] DELETE: print "DELETE of rowid:", [Link] con = cx_Oracle.Connection("pythonhol/welcone@locathost/arct" events = True) subscriptioninsbel = con. subscribe (callback = DENCaLLback, operations = cx_Oracle.OPCODE_INSERT | cx Oracle. OPCODE_DELETE, rowids ~ True) subscriptionInsDel. registerquery('select * from mytab’) raw_input("Hit Enter to conclude this demo\n") [pythonhol@tocathost ~1$ This script creates a function called ocwcal hack (). This function willbe called when a table changes. The ‘message’ parameter is a cx_Oracle object that will contain information about the changes. The function simply prints out the kinds of changes that have occurred and the affected rowids. ‘The main body of the scriptis below the function - note the indentation level of'con = .."is the same as for'def, The bady creates the database connection withthe ‘events = True’ parameter to allow the database to send an event notification to Python when a table change takes place, The subscribe) call registers the Dexcai Iback () to be called in a new thread when an NSE? or UPDATE occurs. Only one Python thread can run at any given time. Python switches between threads as needed. The rowids True parameler lets rowids be accessed in the callback The registerquery() call registers a query that selects everything from the Nvras table. Any change to the table that is an JPOATE or DELETS will Cause 2cNCal back () to be called. The Nv7as table was created in a previous section of he lab, The script concludes with a xav_input (} call that waits for user input before terminating, Torun the DCN example, open two terminal windows. inthe first window run: python [Link] [pythonhol@hosté1 ~]$ python [Link] Hit Enter to conclude this deno This will pinta message and wait without retuming a prompt Leave itrunning for the moment and continue with the next step, 2. Ina second terminal window, enter the following commands sqiplus pythorhol/welconet localhost /orcl insert inte mytab (id) values (11); commit: hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him avaons Using Python With Oracle Database 11g [pythonnol@locathost ~]§ sqlplus pythonhol/welcone@localhost/oret ISQL*Plus: Release [Link].9 Production on Tue Jul 28 18: 1 2010 lcopyraght (c) 1982, 2009, Oracte. ALL rights reserved lconnectea to: loracte Database 11g Enterprise Edition Release [Link].0 - Production lwith the Partitioning, OLAP, data Mining and Real Application Testing options ISaL> insert into mytab (id) vatues (11); 1 row createa. lsau> conmst; commit complete Isau> When the commit occurs, the Python script (switch to your original terminal window) will receive notification and print the change notification message. [pythonhol@hosto1 ~]$ python dcn. py Hit Enter to conclude this demo Notatication: rable: [Link] INSERT of rowid: AAASYRAAEAAAAUHAAV Note: The rowid willbe different on your system, ‘Switch to your SQL*Plus terminal window and delete the new row by executing the following SQL commands: delete from commit ab where id = 11; SOL> delete from mytab where id 1 row deleted. SQL> commat; Commit complete SL The new notification is printed and the Python terminal now looks like: [pythonhol@hosté1 ~]$ python [Link] Hit Enter to conclude this deno Notatacation: Table: [Link] INSERT of rowid: AAASYRAZEARAAUHAAV Notification: ‘Table: [Link] DELETE of rowid: AAASYRAREARAAUHAAV Note: The rowids will be different on your system. Experiment with several operations. Try doing an “wisi flowed by a pit.1:0 before commiting. This shows notification is received of each individual operation. hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him avaons Using Python With Oracle Database 11g When you are finished, nit Enter to conclude the demo in the Python window to exit he script. Tpythonhol@hostoi ~1§ python [Link] Hat Enter to conclude this aemo Notification: |Table: PYTHONHOL .MYTAB INSERT of rowid: AAASyRAAEAAAAUHAAV Notification: Table: PYTHONHOL .MYTAB DELETE of rowid: AAASYRAAEAAAAUHAAV [pythonhoighosta1 ~1$ Extend [Link] to also notify when an UecATE occurs on MYTAS, In the subscribe) call, change the operations parameter to ,INSERD | cx_Oracle.02CODE_DELE” operations = cx Oracle.0Pco0! ‘x_Oracte.ol ‘Add a new if testto the octicaiinack function , Oracle PCOnE_UPATE UPORIE Bf rowid:", [Link] The [Link] script should now look as follows (the changes are in bold) f [Link] & ex_Oracle.OPCODE_INSERT. print "INSERT of Zowid:", [Link] f [Link] & cx_Oracle.OPCODE_/ print "DELETE of Zowid:", [Link] Lf [Link] & cx Oracle .OPCODE UPDATE: PEint "UPDATE of Zowid:", [Link] con = cx_Oracle.Connect ion (pythonho! /welcone@locathost orc)", oninsbel caltback, ations = ox ¢ : | ex_Oracte.oPcops | & oracle oPcobr_upparz, rowids = true! [Link] ("select * from mytab") (mit sti conclude this deno\n") In the Python terminal, restart the script: [pythonhot@hosto1 ~]$ python den. py Hit Enter to conclude this deno In the SQL*Plus terminal, create a row and update it insert into (a) values 1) update mytab set id = 12 where id = 11 commit; hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him 2008 vas Using Phan Wit Oracle Database 119 SQL> insert into mytab (id) values (11); 1 row created. lsaL> update mytab set id = 12 where id = 11; 1 row updated. Isau> commit; lconmit complete. Isou> ‘The new message should be displayed [[pythonnot@nostoi ~1$ python dcn.py_ Hat Enter to conclude thas aeno Notification: Hable: PYTHONHOL. MYTAB INSERT of rowid: ASASyRAAEAAAAUHAAW UPDATE of rowid: ASASyRAAEAAAAUHAAW Note: The rowids will be different on your system. 7, When you are finished, hit Enter to conclude this demo in the Python window to exitthe script [pythonhol@hostél ~1$ python [Link] Hit Enter to conclude this deno Notification: rable: [Link] INSERT of rowid: ASASyRAAEAAAAUHAAN UPDATE of rowid: AMASYRAAEAAAAUHAAN [pythonhoignoster ~1$ Database Change Notifcation is an efficient way to monitor able changes. can also be used to natiy when any ofa subset of raws selected by a given query are changed, Summary In this tutorial, you have learned howto: Create a connection Use Database Resident Connection Pooling Create a simple query Fetcn data Improve query performance Use bind variables. Create transactions Use PLISQL stored functions and procedures Use Continuous Query Notification Appendix: Python Primer Python is a dynamically typed scripting language. itis most often used to run command-line scripts but is also used in Web applications, Strings can be enclosed in single or double quotes: "a string constant! "another conezant™ hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him 21s avaons Using Python With Oracle Database 11g Mult line stings use a triple-quote syntax Variables do not need types declared; Associative arrays are called ‘dicionaries’ 2.7182) Ordered arrays are called "ists a3 = (02, 4, 67 Tuples are lke lists but cannot be changed once they are created. They are created with parentheses: ag= (3, 7, 10) Strings and variables can be displayed with a print statement print "Hello, Wo: print "H's Formatted outputis also possible: print "There are 4d ts" & (vl, v2 Everything in Python is an object As one example, given the value of the list a3 above, the ‘append() method can be used to add a value to the list 23 append (23 Now a3 contains (101, 4,67, 23] Code flow can be controlled with tests and loops. The itelifelse statements look like: Lf sal > 900000: print ‘Salary is way too big! elif sal > 500000: prin: y is huge’ print ‘Salary might be ox This also shows how the clauses are delimited with colons, and each sub block of code is indented, A traditional loop is for i in range (0, prin: i This prints the numbers 0 to 9. The value of is incremented in each iteration. The ‘or’ command can also be used to iterate over lists and tuples: a5 = [aat, ‘eb, + prin: v This sets v to each element ofthe list 25 in turn, A lunction may be defined. func (ply p2): jon documentation: add two nunbe: Functions may or may not retum values. This function could be called using v3 = ayfune(l, 3) hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him rns avaons Using Python With Oracle Database 11g Function calls must appear after their function definition. Functions are also objects and have attributes. The inbuilt__doc__atlibule can be used to find the function description: print yfunc. doe ‘Sub-files can be included in Python scripts with an import statement. import os impo: Many predefined modules exist, such as the os and the sys modules. ‘Comments are either single ine # a shor consent ‘or mult-ine using the tiple-quote token Integrated Cloud conte ste ue Lagu Noman ev one esata &Piatorn Sovzes enone hitpalww oracle. com/vebolderechnetwarkfutorilslobelebfoowlaython_dblpyton_db.him 28

You might also like