Repository navigation
Expand file tree
/
Copy path032_python_oracle_database.py
More file actions
125 lines (95 loc) · 4.07 KB
/
Copy path032_python_oracle_database.py
File metadata and controls
125 lines (95 loc) · 4.07 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
""" MARKDOWN
---
YamlDesc: CONTENT-ARTICLE
Title: python oracle database
MetaDescription: python Connect to oracle database, CRUD, insert, update, select, oracle, create example code, tutorials
MetaKeywords: python oracle database example code, tutorials
Author: Venkata Bhattaram / tinitiate.com
ContentName: python-oracle-database
---
MARKDOWN """
""" MARKDOWN
# Python cx_Oracle Oracle Database Programming
* PYTHON provides mechanisms to connect to various databases
and perform database operations like Create, insert, select
* There are many Open and Commercial Database connectivity
modules available for download.
* In this program, we test with cx_Oracle an OpenSource PYTHON database
connectivity module, And the cx_oracle module is used to connect to Oracle Database
* cx_oracle also supports startup/shutdown commands.
* cx_Oracle module, This is a 3rd party module that needs to be downloaded
separately to avoid errors like: "ImportError: DLL load failed: %1 is not a
Valid Win32 application."
* Make sure the Oracle Client / Python and the cx_Oracle module are of the same
bit version 32 all or 64 all, this is very important for this to work.
MARKDOWN """
"""
set PATH=%PATH%;C:\Personal\instantclient_12_2\
set ORACLE_HOME=C:\Personal\instantclient_12_2\
import os
os.environ["PATH"] += os.pathsep + os.pathsep.join(["C:\\Personal\\instantclient_12_2\\"])
os.environ["ORACLE_HOME"] = "C:\\Personal\\instantclient_12_2\\"
"""
# MARKDOWN ```
# Connecting to Oracle Using cx_oracle module
import cx_Oracle
# Create Connection Object
# ##############################################################################
# Create a connection object, usage string
# USER/PASSWORD@INSTANCE-NAME-AS-IS-IN-TNSNAMES.ORA
connection = cx_Oracle.connect('tinitiate/tinitiate@my_database')
cursor = connection.cursor()
# prints Oracle Version Details
print(connection.version)
# prints Current Schema name
print(connection.username)
# Executing DDL (Data Definition Language)
# ##############################################################################
# Try to Drop the table
try:
cursor.execute("drop table ti_test");
except:
pass;
# python cx_oracle Create a table with multiple data types
cursor.execute("create table ti_test(col1 int,col2 varchar2(100),col3 date,col4 clob )" )
# python cx_oracle Alter Table using cx_python, to add primary key
cursor.execute("alter table ti_test add constraint ti_test_pk primary key (col1)")
# Executing DML (Data Manipulation Language)
# ##############################################################################
# python cx_oracle Insert data into the table
cursor.execute("insert into ti_test values(1,'Test1',sysdate,'THIS IS CLOB DATA .....')")
cursor.execute("insert into ti_test values(2,'Test2',sysdate,'THIS IS CLOB DATA .....')")
# python cx_oracle Update data in the table
cursor.execute("update ti_test set col2 = 'DATA CHANGE' where col1 = 1")
# python cx_oracle delete data in the table
cursor.execute("delete ti_test where col1 = 2")
cursor.execute("insert into ti_test values(3,'Test3',sysdate,'THIS IS CLOB DATA .....')")
cursor.execute("insert into ti_test values(4,'Test4',sysdate,'THIS IS CLOB DATA .....')")
# Commit changes
connection.commit()
# Insert one more row
cursor.execute("insert into ti_test values(5,'Test5',sysdate,'THIS IS CLOB DATA .....')")
# To RollBack changes
conn.rollback()
# Executing Data Selection
# ##############################################################################
# Fetch One Row
cursor.execute("select col1,col2,col3,col4 from ti_test;")
# Prints the Row count of the cursor
print(cursor.rowcount)
# Fetches a Single Row
rowdata = cursor.fetchone()
print(rowdata)
# Reading data from Oracle using cx_Oracle
cursor.execute("SELECT col1, col2, col3, col4 FROM ti_test")
# use fetchall() to get the list of row data
for l_col1, l_col2, l_col3, l_col4 in cursor.fetchall():
print("l_col1: ", l_col1)
print("l_col2: ", l_col2)
print("l_col3: ", l_col3)
print("l_col4: ", l_col4)
# Close the cursor object
cursor.close()
# Close the connection once completed
connection.close()
# MARKDOWN ```