Python programming practice Oracle database operation example

An example of Oracle database operation is presented in this paper. To share for your reference, specific as follows:

1. To enable Python to operate the Oracle database, you first need to install the cx_Oracle package, which can be obtained at the address below

http://cx-oracle.sourceforge.net/

2. In addition, some libraries of oracle are needed. In this case, you need to install the Oracle Instant Client package on the machine running python

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

Find a package that fits your platform and install it. In this case, I'm using the rpm package, so install using the following command

$ sudo rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.i386.rpm

After installation, 1 environment variable should be set as follows

$ export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/lib/oracle/11.2/client/lib

3. Create a simple python file to test whether the installation is successful

import cx_Oracle
conn = cx_Oracle.connect('fkong/fkong@172.17.23.129/orcl')
cursor = conn.cursor ()
cursor.execute ("select * from dual")
row = cursor.fetchone ()
print row[0]
cursor.close ()
conn.close ()

4. The following is a database table and insert operation

import cx_Oracle
conn = cx_Oracle.connect('fkong/fkong@172.17.23.129/orcl')
cursor = conn.cursor ()
cursor.execute ("CREATE TABLE TEST(ID INT, COL1 VARCHAR(32), COL2 VARCHAR(32), COL3 VARCHAR(32))")
cursor.execute ("INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(1, 'a', 'b', 'c')")
cursor.execute ("INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(2, 'aa', 'bb', 'cc')")
cursor.execute ("INSERT INTO TEST (ID, COL1, COL2, COL3)VALUES(3, 'aaa', 'bbb', 'ccc')")
conn.commit()
cursor.close ()
conn.close ()

5. Now let's take a look at the query. There are usually two ways to query: one is to use cursor.fetchall () to get all the query results, and then iterate for 1 row and 1 row; The other is to get one record at a time through cursor.fetchone () until the result is null. Take a look at the following examples:

import cx_Oracle
conn = cx_Oracle.connect('fkong/fkong@172.17.23.129/orcl')
cursor = conn.cursor ()
cursor.execute ("SELECT * FROM TEST")
rows = cursor.fetchall()
for row in rows:
  print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3])
print "Number of rows returned: %d" % cursor.rowcount
cursor.execute ("SELECT * FROM TEST")
while (1):
  row = cursor.fetchone()
  if row == None:
    break
  print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3])
print "Number of rows returned: %d" % cursor.rowcount
cursor.close ()
conn.close ()

More about Python related content interested readers to view this site project: "common database operations Python skills summary", "Python coding skills summary", "Python data structure and algorithm tutorial", "Python Socket programming skills summary", "Python function using techniques", "Python string skills summary", "Python introduction and advanced tutorial" and "Python file and directory skills summary"

I hope this article is helpful for Python programming.