oracle-db-examples/python/CRUD-examples/insert/return_data.py at master · feief/oracle-db-examples

48 lines (36 loc) · 1.65 KB

# Code Sample from the tutorial at https://learncodeshare.net/2015/06/26/insert-crud-using-cx_oracle/

# section titled "Returning data after an insert"

# Using the base template, the example code executes a simple insert using positional bind variables.

# A cursor variable is used to accept the insert statements returning value. This value is then

# used as the parent key value to insert a child record.

import cx_Oracle

import os

connectString = os.getenv('DB_CONNECT') # The environment variable for the connect string: DB_CONNECT=user/password@database

con = cx_Oracle.connect(connectString)

def get_all_rows(label):

# Query all rows

cur = con.cursor()

statement = 'select id, name, age, notes from lcs_people order by id'

cur.execute(statement)

res = cur.fetchall()

print(label + ': ')

print (res)

print(' ')

cur.close()

get_all_rows('Original Data')

cur = con.cursor()

new_id = cur.var(cx_Oracle.NUMBER)

statement = 'insert into lcs_people(name, age, notes) values (:name, :age, :notes) returning id into :new_id'

cur.execute(statement, {'name':'Sandy', 'age':31, 'notes':'I like horses', 'new_id':new_id})

sandy_id = new_id.getvalue()

pet_statement = 'insert into lcs_pets (name, owner, type) values (:name, :owner, :type)'

cur.execute(pet_statement, {'name':'Big Red', 'owner':sandy_id, 'type':'horse'})

con.commit()

print('Our new value is: ' + str(sandy_id).rstrip('.0'))

sandy_pet_statement = 'select name, owner, type from lcs_pets where owner = :owner'

Provide feedback