Category Archives: Python

Install and Configure the Oracle Cloud Command Line Interface

March 6, 2020 bcarter 2 Comments

I love working with cloud resources. I don’t have to bog down my laptop and I don’t have to maintain all of the back-end stuff. I don’t mean to make it sound as if I think the “back-end stuff” is easy. It’s actually, I understand just enough of that “stuff” to make it go (most of the time) so it’s nice to have experts in the cloud taking care of it for me.

Some of you may be thinking “but cloud resources are expensive.” That’s true sometimes, but it depends on who’s cloud you’re using. If you’ve been following me you should already know how much I like Oracle’s Always Free Services and you should know that I love to automate anything I can. If you’d like to follow along but you don’t have an Oracle Cloud account, click that link, create an Always Free account then come back.

In this post I’ll cover how to use the Oracle Cloud Command Line Interface (OCI-CLI) to access and control your Oracle Cloud resources.

CLI Overview

Oracle’s cloud CLI is a small application you can use to control your Oracle Cloud resources. It gives you the same core functionality as you’d get using the Web Console, and some extra commands. It allows you to control your cloud account from your local console application so you can easily automate the control of your resources.

Pre-requisits

Python 3.5+

The CLI is built with Python so make sure you have Python version 3.5 or higher installed.

RSA Key Pair

You will need an RSA key attached to your cloud user in order to remotely access your account. This must be an RSA key pair in PEM format (minimum 2048 bits).

The easiest way to generate this key par is with openssl. The following commands work in Linux/Mac environments. For Windows you can use your favorite tool or execute the commands in GitBash, WSL or some other Linux shell environment.

1 2	openssl genrsa -out myPrivateKey.pem 2048 openssl rsa -pubout -in myPrivateKey.pem -out myPublicKey.pem

The first command creates a PRIVATE KEY called ‘myPrivateKey.pem’ (name yours whatever you’d like). This is the key you will use to access remote systems. DO NOT share this key, whoever has this key can connect to those systems as you. Think of it as your admin password.

The second command uses your private key to create a PUBLIC KEY called ‘myPublicKey.pem’ (name yours whatever you’d like). This is the key you will share with remote systems. Those systems will add your PUBLIC KEY to their authorized keys list, allowing you to access their system using your private key.

Store these keys in a secure location. On Linux, the default location is usually in the ~/.ssh directory. But, if you’re creating separate keys for your projects, you can store them wherever you’d like. Just remember the location for later.

Cloud Account

You need to have access to an Oracle Cloud account with a user that is authorized to preform the tasks you intend to automate. A good way to tell if your user has the correct permissions is to log onto your account through the Web Console and create an Always Free database then terminate it.

While you’re logged into the Web Console collect some information.

Tenancy
1. In the menu, under Administration, click Tenancy Details.
2. Locate the OCID and click Copy.
3. Save this value for later.
User
1. In the menu under Identity click Users.
2. Select your user.
3. Near the bottom click ‘API Keys’ under the resource menu.
4. Click the ‘Add Public Key’ button.
5. Choose the PUBLIC key file you generated earlier.
6. Click the ‘Add’ button.
7. Your key should now show up in the ‘API Keys’ list.
8. Copy the fingerprint of your key and save it for later.
9. Near the top of the user page, locate the OCID and click Copy. Save this value for later.

Quickstart Install

You can download an execute a script that will ask you typical installation configuration questions, after which it will install and configure the OCI-CLI.

The following is current as of the publish date for this post, but you may want to review the instructions in case things change.

Linux / Mac

Open a terminal and run the following command.

1	bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

Windows

Open PowerShell as Administrator.

Set-ExecutionPolicy RemoteSigned

powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.ps1'))"

In either system, answer the questions to complete the install.

Install with Python

If you don’t have the rights on your computer or you’d rather not execute the above scripts, you can install the OCI-CLI with Python.

I recommend using a virtual environment when working with python. It helps keep all of your projects clean and isolated.

python3 -m venv oci-cli-env

# Linux / Mac

source oci-cli-env/bin/activate

# Windows

oci-cli-env\Scripts\activate.bat

Once your virtual environment is active, install the OCI-CLI with pip.

1	pip install oci-cli

You can see if the install was successful by checking the version.

oci -v

2.9.5

Configure the OCI-CLI

Using the values you saved from above, you can create a config file with this command.

1	oci setup config

You will be prompted for the following information.

Enter a location for your config [/home/bcarter/.oci/config]:

Enter a user OCID: <User OCID from above>

Enter a tenancy OCID: <Tenancy OCID from above>

Enter a region (e.g. us-ashburn-1, us-phoenix-1): us-ashburn-1

Do you want to generate a new RSA key pair? [Y/n]: n

Enter the location of your private key file: /home/bcarter/.ssh/myPrivateKey.pem

Fingerprint: <saved value>

The default location for the config file is typically ‘~/.oci/config’. If you use a different location you will need to remember where it is.
Enter the user and tenancy OCIDs saved from your account.
Enter the region you want to work in. The system will display some examples you can choose from.
You already generated an RSA key pair so enter ‘n’.
Enter the location of your PRIVATE key. This will not be uploaded, the CLI will use your PRIVATE key to make the connection to the cloud.
Enter the Fingerprint you saved when you uploaded your PUBLIC key.

Once it’s complete, you should see a response similar to this.

1	Config written to /home/bcarter/.oci/config

Your OCI-CLI should now be configured. If you open the config file it should look similar to this.

[DEFAULT]

user=ocid1.user.oc1..aaaaaaaat5nvwcna5j6aqzjcaty5eqbb6qt2jvpkanghtgdaqedqw3rynjq

fingerprint=20:3b:97:13:55:1c:5b:0d:d3:37:d8:50:4e:c5:3a:34

key_file=~/.oci/oci_api_key.pem

tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq

region=us-ashburn-1

You can add other connection profiles manually by following this format or you can use the same command to add a new profile. If you re-run the command, it will ask you for a name to use for the new profile which will be added to the config file.

[DEFAULT]

user=ocid1.user.oc1..aaaaaaaat5nvwcna5j6aqzjcaty5eqbb6qt2jvpkanghtgdaqedqw3rynjq

fingerprint=20:3b:97:13:55:1c:5b:0d:d3:37:d8:50:4e:c5:3a:34

key_file=~/.oci/oci_api_key.pem

tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq

region=us-ashburn-1

[ACCOUNT2]

user=ocid1.user.oc1..aaaaaaaa65vwl7zut55hiavppn4nbfwyccuecuch5tewwm32rgqvm6i34unq

fingerprint=72:00:22:7f:d3:8b:47:a4:58:05:b8:95:84:31:dd:0e

key_file=~/.ssh/other_key.pem

tenancy=ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr5vqstifsfdsq

region=us-ashburn-1

Quick Test

Enter the following command.

1	oci iam availability-domain list

If everything is working, you should receive a response similar to this.

{

"data": [

{

"compartment-id": "ocid1.tenancy.oc1..biglongguid",

"id": "ocid1.availabilitydomain.oc1..biglongguid",

"name": "qVbG:US-ASHBURN-AD-1"

{

"compartment-id": "ocid1.tenancy.oc1..biglongguid",

"id": "ocid1.availabilitydomain.oc1..biglongguid",

"name": "qVbG:US-ASHBURN-AD-2"

}

]

}

Now that your OCI-CLI is installed and configured, you should familiarize yourself with the OCI-CLI documentation to learn about the many, many commands you can use to automate the control of your Oracle Cloud resources.

You can use these same commands from any system with the OCI-CLI installed, including Oracle Cloud Compute instances.

CodeCard, Information, Python, Tools

You Went to Oracle Open World 19 and got a Code Card, now what?

September 24, 2019 bcarter 20 Comments

We handed out several hundred Code Cards this year and I’m guessing a few of you are wondering what to do next.

If you got home and pressed one of the buttons you more than likely saw this screen.

Don’t worry, it’s not broken, you just need to get it connected to the internet. There are a few different ways to do this.

Note: The Code Card uses a 2.4 GHz WiFi connection.

Create a WiFi Network

The easiest way to get it connected is to create a WiFi network using the credentials that are already set on the Code Card.

1 2	SSID=pmac851 Password=thinkcodetrigger

One way you can do this is to create guest network on your WiFi router. Please refer to your router manual for instructions on setting this up.

Alternatively, you could create a hotspot on your phone, but make sure you’re OK with any charges from your service provider.

Edit:

At Oracle we like to keep everything patched and up to date. After I published this post, the security certificate for the REST back end was updated. This means you will also need to update the fingerprint settings for the four button options.

This also means that creating a WiFi SSID as in the paragraph above will only work if you also update the following four settings. For that you’ll need to choose one of the methods below. (I have added them to the examples below.)

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Change the WiFi credentials on the card

If you’d rather just connect the Code Card to your existing WiFi network (2.4 GHz only), you can change the settings on the card.

In order to do this you need to open a serial connection to the card and send the following commands (using your WiFi credentials):

ssid=pmac851

password=thinkcodetrigger

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

A Little Python

If you’d like to use Python:

Install Python 3 if you don’t already have it.
Open a terminal and install pyserial.

Shell

python3 -m pip install pyserial

1

python3 -m pip install pyserial

Create a file using the following code. I named mine ccSerial.py.

import serial
import serial.tools.list_ports as port_list
import time

ser = serial.Serial()

settings = ['ssid=myHomeSSID',
            'password=myPassword',
            'fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',
            'fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',
            'fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',
            'fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47']

def findPort():
    ports = list(port_list.comports())
    cardPort = None
    for p in ports:
        if "USB to UART" in str(p):
            cardPort = p.device
            break
    return cardPort

def openPort():
    global ser
    if ser.is_open:
        print('Closing Port')
        ser.close()

usbPort = findPort()

if usbPort:
        ser.baudrate = 115200
        ser.timeout = 5
        ser.port = usbPort
        print("Using Port:", ser.port)
        ser.open()

return ser

def readAll(ser):
    response = b''
    while True:
        chunk = ser.read(size=200)
        response += chunk
        if not len(chunk) == 200:
            break

return response.decode('utf-8', 'ignore')

def sendSettings(ser):
    print("Updating Settings\n")
    for setting in settings:
        print(setting)
        ser.write(str.encode(setting))

print(readAll(ser).split('\r\n')[0] + '\n')

def cardReady():

cardPort = openPort()

attempts = 30
    for attempt in range (1, attempts + 1):
        print('Attempt %i of %i.' % (attempt, attempts))

try:
            if cardPort.is_open == False:
                print('CodeCard not detected on USB')
                cardPort = openPort()
                time.sleep(2)
            else:
                cardPort.write(str.encode('status'))
                cardStatus = readAll(cardPort).split('\r\n')

if cardStatus[0].startswith('IP address: '):
                    cardPort.close()
                    return True
                elif len(cardStatus[0]) == 0:
                    print('****************************************************')
                    print('*      Press A and B Buttons on the CodeCard.      *')
                    print('* The screen should flash and display the barcode. *')
                    print('****************************************************\n')
                else:
                    while len(cardStatus[0]) > 0:
                        print('Clearing Card Buffer')
                        # print(cardStatus)
                        cardStatus = readAll(cardPort).split('\r\n')
                        time.sleep(1)

time.sleep(1)
        except serial.SerialException:
            print('Connection Error. Attempting to re-connect.')
            cardPort = openPort()
            time.sleep(1)
    
    if cardPort.is_open:
        cardPort.close()

def setDefaults():
    if cardReady():
        print('Setting Configuration Values')
        cardPort = openPort()
        sendSettings(cardPort)

if cardPort:
            print('Restarting Code Card')
            print('Please turn it off and on again\n')
            cardPort.write(str.encode('restart'))
            cardPort.close()

if __name__ == '__main__':
    setDefaults()

100

101

102

103

104

105

106

107

108

109

110

111

112

import serial

import serial.tools.list_ports as port_list

import time

ser = serial.Serial()

settings = ['ssid=myHomeSSID',

'password=myPassword',

'fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',

'fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',

'fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',

'fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47']

def findPort():

ports = list(port_list.comports())

cardPort = None

for p in ports:

if "USB to UART" in str(p):

cardPort = p.device

break

return cardPort

def openPort():

global ser

if ser.is_open:

print('Closing Port')

ser.close()

usbPort = findPort()

if usbPort:

ser.baudrate = 115200

ser.timeout = 5

ser.port = usbPort

print("Using Port:", ser.port)

ser.open()

return ser

def readAll(ser):

response = b''

while True:

chunk = ser.read(size=200)

response += chunk

if not len(chunk) == 200:

break

return response.decode('utf-8', 'ignore')

def sendSettings(ser):

print("Updating Settings\n")

for setting in settings:

print(setting)

ser.write(str.encode(setting))

print(readAll(ser).split('\r\n')[0] + '\n')

def cardReady():

cardPort = openPort()

attempts = 30

for attempt in range (1, attempts + 1):

print('Attempt %i of %i.' % (attempt, attempts))

try:

if cardPort.is_open == False:

print('CodeCard not detected on USB')

cardPort = openPort()

time.sleep(2)

else:

cardPort.write(str.encode('status'))

cardStatus = readAll(cardPort).split('\r\n')

if cardStatus[0].startswith('IP address: '):

cardPort.close()

return True

elif len(cardStatus[0]) == 0:

print('****************************************************')

print('* Press A and B Buttons on the CodeCard. *')

print('* The screen should flash and display the barcode. *')

print('****************************************************\n')

else:

while len(cardStatus[0]) > 0:

print('Clearing Card Buffer')

# print(cardStatus)

cardStatus = readAll(cardPort).split('\r\n')

time.sleep(1)

except serial.SerialException:

print('Connection Error. Attempting to re-connect.')

cardPort = openPort()

time.sleep(1)

if cardPort.is_open:

cardPort.close()

def setDefaults():

if cardReady():

print('Setting Configuration Values')

cardPort = openPort()

sendSettings(cardPort)

if cardPort:

print('Restarting Code Card')

print('Please turn it off and on again\n')

cardPort.write(str.encode('restart'))

cardPort.close()

if __name__ == '__main__':

setDefaults()

Change lines 7 and 8 to use your WiFi credentials.

In the terminal run the new module and follow the instructions.
You should see something similar to the following.

[Projects]$ python3 ccSerial.py 
Using Port: /dev/ttyUSB0
Attempt 1 of 30.
****************************************************
*      Press A and B Buttons on the CodeCard.      *
* The screen should flash and display the barcode. *
****************************************************

Attempt 2 of 30.
Clearing Card Buffer
Clearing Card Buffer
Attempt 3 of 30.
Setting Configuration Values
Using Port: /dev/ttyUSB0
Updating Settings

ssid=myHomeSSID
Value saved for ssid: myHomeSSID

password=myPassword
Value saved for password: myPassword

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
Value saved for fingerprinta1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
Value saved for fingerprinta2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
Value saved for fingerprintb1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
Value saved for fingerprintb2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Restarting Code Card
Please turn it off and on again
[Projects]$

[Projects]$ python3 ccSerial.py

Using Port: /dev/ttyUSB0

Attempt 1 of 30.

****************************************************

* Press A and B Buttons on the CodeCard. *

* The screen should flash and display the barcode. *

****************************************************

Attempt 2 of 30.

Clearing Card Buffer

Attempt 3 of 30.

Setting Configuration Values

Using Port: /dev/ttyUSB0

Updating Settings

ssid=myHomeSSID

Value saved for ssid: myHomeSSID

password=myPassword

Value saved for password: myPassword

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Value saved for fingerprinta1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Value saved for fingerprinta2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Value saved for fingerprintb1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Value saved for fingerprintb2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Restarting Code Card

Please turn it off and on again

[Projects]$

Arduino IDE

If you have a Serial communication tool that you like, you should be able to use it to change the settings.

If not the Arduino IDE includes a Serial Monitor tool that work great.

Do not connect your Code Card to the computer yet.
Download, install and run the Arduino IDE.
Click on Tools / Port.
See what Ports are already in use.
Connect your Code Card and turn it on.
Click on Tools / Port.
The newest Port should be your Code Card; select it.
Click on Tools / Serial Monitor.
On the bottom of the Serial Monitor, choose 115200 baud.
Turn your Code Card off and on again.

Press and release the A and B buttons at the same time.
You should see something like this.

rll⸮⸮|⸮l⸮|⸮l⸮b|⸮⸮⸮⸮r⸮blb⸮⸮nn⸮lnn⸮⸮⸮bp⸮lrlrlp⸮n⸮⸮l⸮⸮bn⸮|l⸮⸮b⸮⸮nn⸮l⸮⸮l`⸮nnl`nr⸮⸮⸮nb⸮lr⸮⸮nb⸮l⸮bbb⸮⸮⸮⸮l`⸮⸮n⸮***************************************************************************************
  Code Card v1.0
  Oracle Developer Community
  developer.oracle.com/codecard
***************************************************************************************
Commands:
  ls                Show all stored key/values
  help              Show this help
  shortpress[a|b]   Simulate the press of a button
  longpress[a|b]    Simulate the long press of a button
  connect           Connect to wifi
  disconnect        Disconnect wifi
  restart           Restart wifi
  status            Show wifi status
  home              Show home screen
  reset             Reset to factory settings

Usage:
  Read saved key value:
    key
  Save new key value:
    key=[value]

Available keys:
  ssid, password, buttona1, buttona2, buttonb1, buttonb2, fingerprinta1, fingerprinta2, 
  fingerprintb1, fingerprintb2, methoda1, methoda2, methodb1, methodb2, 
>>>
Connecting to 'pmac851' ....................
Could not connect to 'pmac851'. Please check your ssid and password values. Type 'help' for more info.
>>>

Code Card v1.0

Oracle Developer Community

developer.oracle.com/codecard

***************************************************************************************

Commands:

ls Show all stored key/values

help Show this help

shortpress[a|b] Simulate the press of a button

longpress[a|b] Simulate the long press of a button

connect Connect to wifi

disconnect Disconnect wifi

restart Restart wifi

status Show wifi status

home Show home screen

reset Reset to factory settings

Usage:

Read saved key value:

key

Save new key value:

key=[value]

Available keys:

ssid, password, buttona1, buttona2, buttonb1, buttonb2, fingerprinta1, fingerprinta2,

fingerprintb1, fingerprintb2, methoda1, methoda2, methodb1, methodb2,

>>>

Connecting to 'pmac851' ....................

Could not connect to 'pmac851'. Please check your ssid and password values. Type 'help' for more info.

>>>

In the input field at the top enter (use your SSID)

ssid=myHomeSSID

1

ssid=myHomeSSID

Click Send.
You should see something like

Value saved for ssid: myHomeSSID >>> Use the command 'restart' for changes to take effect. >>>

1
2
3
4

Value saved for ssid: myHomeSSID
>>>
Use the command 'restart' for changes to take effect.
>>>
At the top enter (use your password)

password=myPassword

1

password=myPassword

Click Send.
You should see something like

Value saved for password: myPassword >>> Use the command 'restart' for changes to take effect. >>>

1
2
3
4

Value saved for password: myPassword
>>>
Use the command 'restart' for changes to take effect.
>>>
At the top enter (use your password)

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

1

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Click Send.
You should see something like

Value saved for fingerprinta1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47 >>>

1
2

Value saved for fingerprinta1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
>>>
At the top enter (use your password)

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

1

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Click Send.
You should see something like

Value saved for fingerprinta2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47 >>>

1
2

Value saved for fingerprinta2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
>>>
At the top enter (use your password)

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

1

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Click Send.
You should see something like

Value saved for fingerprintb1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47 >>>

1
2

Value saved for fingerprintb1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
>>>
At the top enter (use your password)

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

1

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Click Send.
You should see something like

Value saved for fingerprintb2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47 >>>

1
2

Value saved for fingerprintb2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
>>>
Unplug your Code Card.
Turn it off and on again.
It should now connect to your network and the buttons should work.

What Else Can You Do?

There are a lot of things you can do with your Code Card.

For some ideas check out this GitHub page for some how to guides and other information.

Information, Learning, PL/SQL, Python

Execute PL/SQL calls with Python and cx_Oracle

July 30, 2018 bcarter 4 Comments

After you’ve got the hang of performing Basic CRUD operations with cx_Oracle you’re ready to start tapping into some of the real power of the Oracle Database.

Why use PL/SQL?

Python is an excellent language for most things you want your application to do, but when you’re processing data it just goes faster if you do the work where the data is.

This post will cover how to execute Oracle PL/SQL functions and procedures using Python and cx_Oracle. I’m assuming you’re already familiar with PL/SQL if not, you can get some help from Steven Feuerstein and Bryn Llewellyn. (Additional resources at the end.)

Prerequisites

Python 3
Oracle Database version 12+
Basic Oracle PL/SQL and SQL knowledge.

Setup

If you’d like to follow along with the examples you’ll need to create the following objects in a database schema that is safe to experiment in. Make sure you have permissions to create the following objects.

CREATE TABLE lcs_people (

id NUMBER GENERATED BY DEFAULT AS identity,

name VARCHAR2(20),

age NUMBER,

notes VARCHAR2(100)

)

ALTER TABLE LCS_PEOPLE

ADD CONSTRAINT PK_LCS_PEOPLE PRIMARY KEY ("ID")

CREATE TABLE LCS_PETS (

id NUMBER GENERATED BY DEFAULT AS IDENTITY,

name VARCHAR2(20),

owner NUMBER,

type VARCHAR2(100)

)

ALTER TABLE LCS_PETS ADD CONSTRAINT PK_LCS_PETS PRIMARY KEY ("ID")

ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")

INSERT INTO lcs_people (name, age, notes)

VALUES ('Bob', 35, 'I like dogs')

INSERT INTO lcs_people (name, age, notes)

VALUES ('Kim', 27, 'I like birds')

INSERT INTO lcs_pets (name, owner, type)

VALUES ('Duke', 1, 'dog')

INSERT INTO lcs_pets (name, owner, type)

VALUES ('Pepe', 2, 'bird')

COMMIT

To keep everything clean, I’ll be putting my PL/SQL code into a package called pet_manager.

CREATE OR REPLACE PACKAGE pet_manager AS
    PROCEDURE reset_data;

PROCEDURE add_pet (
        name_p       IN VARCHAR2,
        owner_id_p   IN NUMBER,
        pet_type_p   IN VARCHAR2
    );

FUNCTION add_pet (
        name_p       IN VARCHAR2,
        owner_id_p   IN NUMBER,
        pet_type_p   IN VARCHAR2
    ) RETURN NUMBER;

FUNCTION add_pet (
        name_p           IN VARCHAR2,
        owner_id_p       IN NUMBER,
        pet_type_p       IN VARCHAR2,
        need_license_p   OUT VARCHAR2
    ) RETURN NUMBER;

END;
/

CREATE OR REPLACE PACKAGE BODY pet_manager IS
  
  /*
   * Reset the example data
   */

PROCEDURE reset_data
      AS
   BEGIN
      DELETE FROM lcs_pets;

EXECUTE IMMEDIATE ('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)');
      
      DELETE FROM lcs_people;

EXECUTE IMMEDIATE ('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)');
      
      INSERT INTO lcs_people (id, name, age, notes)
      VALUES (1, 'Bob', 35, 'I like dogs');

INSERT INTO lcs_people (id, name, age, notes)
      VALUES (2, 'Kim', 27, 'I like birds');

INSERT INTO lcs_pets (id, name, owner, type)
      VALUES (1, 'Duke', 1, 'dog');

INSERT INTO lcs_pets (id, name, owner, type)
      VALUES (2, 'Pepe', 2, 'bird');

COMMIT;
   END reset_data;
  
  /*
   * Add a new Pet
   */

PROCEDURE add_pet (
      name_p       IN VARCHAR2,
      owner_id_p   IN NUMBER,
      pet_type_p   IN VARCHAR2
   )
      IS
   BEGIN
      INSERT INTO lcs_pets (
         name,
         owner,
         type
      ) VALUES (
         name_p,
         owner_id_p,
         lower(pet_type_p)
      );

COMMIT;
   END add_pet;
  
  /*
   * Add a new Pet return new id
   */

FUNCTION add_pet (
      name_p       IN VARCHAR2,
      owner_id_p   IN NUMBER,
      pet_type_p   IN VARCHAR2
   ) RETURN NUMBER IS
      new_pet_id   NUMBER;
   BEGIN
      INSERT INTO lcs_pets (
         name,
         owner,
         type
      ) VALUES (
         name_p,
         owner_id_p,
         lower(pet_type_p)
      ) RETURNING id INTO new_pet_id;

COMMIT;
      RETURN new_pet_id;
   END add_pet;
  
  /*
   * Add a new Pet return new id and if it needs a license.
   */

FUNCTION add_pet (
      name_p           IN VARCHAR2,
      owner_id_p       IN NUMBER,
      pet_type_p       IN VARCHAR2,
      need_license_p   OUT VARCHAR2
   ) RETURN NUMBER IS
      new_pet_id   NUMBER;
   BEGIN
      INSERT INTO lcs_pets (
         name,
         owner,
         type
      ) VALUES (
         name_p,
         owner_id_p,
         lower(pet_type_p)
      ) RETURNING id INTO new_pet_id;

IF lower(pet_type_p) IN ('dog', 'cat') THEN
         need_license_p := 'yes';
      ELSE
         need_license_p := 'no';
      END IF;

COMMIT;
      RETURN new_pet_id;
   END add_pet;

END pet_manager;
/

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

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

CREATE OR REPLACE PACKAGE pet_manager AS

PROCEDURE reset_data;

PROCEDURE add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

);

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

) RETURN NUMBER;

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2,

need_license_p OUT VARCHAR2

) RETURN NUMBER;

END;

CREATE OR REPLACE PACKAGE BODY pet_manager IS

* Reset the example data

PROCEDURE reset_data

BEGIN

DELETE FROM lcs_pets;

EXECUTE IMMEDIATE ('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)');

DELETE FROM lcs_people;

EXECUTE IMMEDIATE ('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)');

INSERT INTO lcs_people (id, name, age, notes)

VALUES (1, 'Bob', 35, 'I like dogs');

INSERT INTO lcs_people (id, name, age, notes)

VALUES (2, 'Kim', 27, 'I like birds');

INSERT INTO lcs_pets (id, name, owner, type)

VALUES (1, 'Duke', 1, 'dog');

INSERT INTO lcs_pets (id, name, owner, type)

VALUES (2, 'Pepe', 2, 'bird');

COMMIT;

END reset_data;

* Add a new Pet

PROCEDURE add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

)

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

);

COMMIT;

END add_pet;

* Add a new Pet return new id

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

) RETURN NUMBER IS

new_pet_id NUMBER;

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

) RETURNING id INTO new_pet_id;

COMMIT;

RETURN new_pet_id;

END add_pet;

* Add a new Pet return new id and if it needs a license.

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2,

need_license_p OUT VARCHAR2

) RETURN NUMBER IS

new_pet_id NUMBER;

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

) RETURNING id INTO new_pet_id;

IF lower(pet_type_p) IN ('dog', 'cat') THEN

need_license_p := 'yes';

ELSE

need_license_p := 'no';

END IF;

COMMIT;

RETURN new_pet_id;

END add_pet;

END pet_manager;

Cleanup

To clean up the database when you are finished with the series, you need to drop the two tables and the package. Please make sure you’re connected to the correct schema where you created the tables.

drop table lcs_pets

drop table lcs_people

drop package pet_manager

Boilerplate template

The template we will be using is:

Install cx_Oracle.
Import the cx_Oracle driver.
Import os module used to read the environment variable.
Get the connection string from the environment variable.
Create the connection object.
Create the cursor object.

import cx_Oracle

import os

connectString = os.getenv('db_connect')

con = cx_Oracle.connect(connectString)

cur = con.cursor()

# Your code here

I will include this code section with all Python examples and use the connection object “con” and the cursor object “cur” throughout the series.

For each exercise, replace the “# Your code here” line with your code.

Anonymous PL/SQL Block

I’m going to start off with the most basic process and simply execute an anonymous block of PL/SQL code to reset the database tables.

# reset data

statement = """

BEGIN

DELETE FROM lcs_pets;

EXECUTE IMMEDIATE ('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)');

DELETE FROM lcs_people;

EXECUTE IMMEDIATE ('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)');

INSERT INTO lcs_people (id, name, age, notes)

VALUES (1, 'Bob', 35, 'I like dogs');

INSERT INTO lcs_people (id, name, age, notes)

VALUES (2, 'Kim', 27, 'I like birds');

INSERT INTO lcs_pets (id, name, owner, type)

VALUES (1, 'Duke', 1, 'dog');

INSERT INTO lcs_pets (id, name, owner, type)

VALUES (2, 'Pepe', 2, 'bird');

COMMIT;

END;"""

cur.execute(statement)

Example: python3 anon_plsql.py

Example:

You can execute any DDL or DML statement like this, but if you’re going to run PL/SQL it’s usually best to compile it to the database.

Execute a PL/SQL Procedure

Using the code from the anonymous block I created a procedure in the PL/SQL package called reset_data.

To call this procedure from Python we use the cursor.callproc method and pass in the package.procedure name to execute.

1	cur.callproc('pet_manager.reset_data')

Assuming everything works, there will not be any response. So this works as a ‘fire and forget’ way to call database procedures.

Example: python3 execute_procedure.py

Example:

Pass Parameters

I have a procedure in my PL/SQL package that we can use to create a new pet in the lcs_pets table. It accepts the pet_name, owner_id and pet_type. Using these values it will insert a new entry into the lcs_pets table.

* Add a new Pet

PROCEDURE add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

)

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

);

COMMIT;

END add_pet;

Now on the Python side.

I prefer to set my values with variables so that my code is easier to read, so I’ll create and set pet_name, owner_id and pet_type.

Next, I’ll call the cursor.callproc method and add an array containing the values to pass in the order they are defined in the database.

pet_name = 'Roger'

owner_id = 1

pet_type = 'fish'

# add a new pet

cur.callproc('pet_manager.add_pet',

[pet_name, owner_id, pet_type])

If everything works there will not be any response.

Example: python3 pass_parameters.py

Example:

You can also use keyword parameters. This also makes your code easy to read and also makes it so you don’t need to worry about the order of the parameters.

# add a new pet

cur.callproc('pet_manager.add_pet',

keywordParameters={"pet_type_p": "fish", "name_p": "Roger", "owner_id_p": 1})

Once again, if everything works there will not be any response.

Example: python3 pass_parameters.py

Example:

Get PL/SQL Function Return Values

When a row is added to the lcs_pets table a new id is automatically generated. Having this id can be useful so I created a function in my PL/SQL package that will create a new pet in the lcs_pets table, just like in the previous function, but it will return the new id.

* Add a new Pet return new id

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2

) RETURN NUMBER IS

new_pet_id NUMBER;

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

) RETURNING id INTO new_pet_id;

COMMIT;

RETURN new_pet_id;

END add_pet;

Using Python to call a function in the database and get the return value I’ll use the cursor.callfunc method.

I set the variables that I’ll use as arguments to the function.
Define a new_pet_id variable and assign it the value returned from callfunc.
The second argument of the callfunc method is used to define the type of the data being returned. I’ll set it to int. (cx_Oracle will handle the NUMBER to int conversion.)
I pass in the array of values just like I did when I used callproc.
Print the returned value for new_pet_id.

pet_name = 'Roger'

owner_id = 1

pet_type = 'fish'

# add a new pet

new_pet_id = cur.callfunc('pet_manager.add_pet',

int,

[pet_name, owner_id, pet_type])

print (new_pet_id)

Example: python3 execute_function.py

Example:

Out Parameters

Out parameters can be very handy when you need to pass back more than one piece of information. I have an add_pet function in the PL/SQL package that will check to see if the pet type you’re adding needs a license or not. The function will return the new id like before, and a ‘yes’ or ‘no’ through the out parameter.

* Add a new Pet return new id and if it needs a license.

FUNCTION add_pet (

name_p IN VARCHAR2,

owner_id_p IN NUMBER,

pet_type_p IN VARCHAR2,

need_license_p OUT VARCHAR2

) RETURN NUMBER IS

new_pet_id NUMBER;

BEGIN

INSERT INTO lcs_pets (

name,

owner,

type

) VALUES (

name_p,

owner_id_p,

lower(pet_type_p)

) RETURNING id INTO new_pet_id;

IF lower(pet_type_p) IN ('dog', 'cat') THEN

need_license_p := 'yes';

ELSE

need_license_p := 'no';

END IF;

COMMIT;

RETURN new_pet_id;

END add_pet;

To work with the out parameter in Python I’ll add a string variable called ‘need_license’. It can be defined using ‘cursor.var(str)‘. Then we just add the new variable to the values array in the correct position. This works the same when using out parameters with the callproc method.

To get the value from ‘need_license’ we call it’s getvalue() function.

pet_name = 'Roger'

owner_id = 1

pet_type = 'dog'

need_license = cur.var(str)

# add a new pet

new_pet_id = cur.callfunc('pet_manager.add_pet',

int,

[pet_name, owner_id, pet_type, need_license])

print ("New pet id: {}\nLicense needed: {}".format(new_pet_id, need_license.getvalue()))

Example: python3 execute_function_out.py

New pet id: 5

License needed: yes

Example:

Accept Argument Values

So far I’ve hard-coded the variable values in the Python code and the methods are fairly simple, so there’s a low chance of errors. But, for most methods, we want to accept parameter values that can be passed into the Python code then on to the PL/SQL functions. I’ll modify the Python method to accept command line arguments.

We need to import sys so that we can use sys.argv[] to grab the command line arguments and assign them to the variables.

import cx_Oracle

import os

import sys

connectString = os.getenv('db_connect')

con = cx_Oracle.connect(connectString)

cur = con.cursor()

pet_name = sys.argv[1]

owner_id = sys.argv[2]

pet_type = sys.argv[3]

need_license = cur.var(str)

# add a new pet

new_pet_id = cur.callfunc('pet_manager.add_pet',

int,

[pet_name, owner_id, pet_type, need_license])

print ("New pet id: {}\nLicense needed: {}".format(new_pet_id, need_license.getvalue()))

If I run this to add a dog, I get:

Example: python3 accept_input.py rover 2 dog

New pet id: 4

License needed: yes

Adding a fish, I get:

Example: python3 accept_input.py roger 1 fish

New pet id: 4

License needed: no

PL/SQL Exceptions

Now that I’m accepting outside argument values, the odds that I’ll eventually get errors with the above code is almost a certainty. If an error happens in the Python code you can handle it as you normally would. But, what if there’s an error thrown by the PL/SQL code?

It’s easy enough to test this. Make the same call as before but pass in a string for the second value.

Example: python3 accept_input.py rover 2x dog

Traceback (most recent call last):

File "accept_input.py", line 22, in <module>

[pet_name, owner_id, pet_type, need_license])

cx_Oracle.DatabaseError: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 1

I would recommend that you handle errors as close to where they happen as you can. In this example, you could catch the error in the PL/SQL function and either handle it or raise it. If you don’t handle it in PL/SQL it will be passed back to cx_Oracle which will throw a cx_Oracle.DatabaseError. At that point, you can handle it as you would when any other Error is thrown in your Python application.

Additional Resources

cx_Oracle Documentation
https://www.python.org/about/gettingstarted/
Steven Feuerstein’s Blog
Bryn Llewellyn’s Blog
Oracle DevGym
Oracle LiveSQL
If you have any problems using cx_Oracle, you can get help here.

DevOps, Learning, Python

Deploy a Python application to Oracle Application Container Cloud Service

December 22, 2017 bcarter 2 Comments

About Application Container Cloud Service

ACCS provides a pre-configured platform (Platform as a Service or PAAS) where you can quickly deploy and host your applications. For many of today’s applications, the hosting server is just that, a place to host the application. Most of the time the only thing an application needs from the server is to have it support the application’s programming language and to provide in and out connections through ports. Using a PAAS such as ACCS frees you from all of the extra work of configuring and maintaining a server and allows you to focus on perfecting your application.

ACCS supports multiple languages but for this post, I’ll focus on Python.

DinoDate

For the examples, I will be deploying the DinoDate application. DinoDate was written as an open source learning application that can be used to demonstrate database concepts with multiple programming languages. It currently has both Python and NodeJS mid-tier applications and is backed by an Oracle Database.

The following instructions show how to deploy the Python version of DinoDate to an Oracle ACCS instance.

If you don’t have access to Oracle Cloud services, you can try the Oracle Cloud with $300 of free credit.

Download/Clone the DinoDate application.

Database

First, you’ll need a database.

Create an Oracle Cloud database or if you already have an Oracle Database, make sure that you can safely create and destroy the DD and DD_NON_EBR schema.

Connect to your database as sys with sysdba and run coreDatabase/dd_master_install.sql. (Use your password and connect string)

1	sql sys/YourPassword@YourJdbcConnecString as sysdba @coreDatabase/dd_master_install.sql

Prepare the DinoDate Application

Download oraclejet.zip (version 4.1.0). (Current versions as of the time of this post.)

Extract the Oracle JET files
Run bower install

unzip oraclejet.zip -d dino-date/commonClient/jet/

cd dino-date/commonClient/jet/

bower install

cd ../../

Download necessary files

The Docker container for Python used by ACCS comes with Python installed. We’ll need to include the rest of the dependencies.

Oracle Instant Client Package – Basic Light (.zip file)
Debian libaio package libaio1_0.3.110-1_amd64.deb

Package the Files to Deploy

Create a deploy directory with a lib subdirectory.
Copy the front end client into the deploy directory.
Copy the python application into the deploy directory.
Extract the Oracle instant client files into the deploy/lib directory. (Change the command to point to where your files are located.)
Change to the deploy directory.

mkdir -p deploy/lib

cp -r commonClient/ deploy/

cp -r python/ deploy/

unzip instantclient-basiclite-linux.x64-12.2.0.1.0.zip -d deploy/lib

cd deploy

Create a shell script, launchPython.sh, to install the dependencies and launch the application.

#!/bin/sh

# Install the LIBAOI libs

dpkg-deb -R libaio1_0.3.110-1_amd64.deb ${APP_HOME}

# Install Python packages into python/modules folder

pip --no-cache-dir install -r python/requirements.txt -t ${PYTHONPATH}

#launch DinoDate

cd python

python app.py

Create the manifest file: manifest.json

This file declares that we will use Python version 3.6.0 and provides the command that will be used to start the application.

{

"runtime": {

"majorVersion": "3.6.0"

"command": "sh ./launchPython.sh",

"release": {},

"notes": "DinoDate"

}

Create the deployment file: deployment.json

This file includes the environment variables DinoDate needs and sets the ACCS deployment to use 1G of memory and only install 1 instance. PYTHONPATH is the directory we will install the Python modules into and LD_LIBRARY_PATH is used by cx_Oracle to locate the Oracle client files.

Replace “YourJdbcConnecString” with the JDBC connect string for your database.

{

"memory": "1G",

"instances": "1",

"environment": {

"dd_connectString":"YourJdbcConnecString",

"dd_user":"dd",

"dd_password":"dd",

"dd_clientAppCodeDir":"jet",

"dd_python_clientAppCodeDir":"jet",

"dd_python_port":"${PORT}",

"PYTHONPATH":"${APP_HOME}/python/modules",

"LD_LIBRARY_PATH":"${APP_HOME}/lib/instantclient_12_2:${APP_HOME}/lib/x86_64-linux-gnu"

}

Important Note

ACCS is pre-configured to listen on $PORT so we set our application to listen on that port. Do not attempt to change $PORT. When ACCS performs its post-deploy check it will open the application using $PORT, if the application is not listening on that port and returns a 404 the deployment will fail and be removed.

Create a zip file with the required DinoDate deploy files.

1	zip -rq DinoDatePythonACCS.zip commonClient/ python/ lib/ libaio1_0.3.110-1_amd64.deb launchPython.sh

Deploy to ACCS

In your browser navigate to the Oracle Application Container Cloud Service Console.

Push the Create Application button to open the platform selection panel.

Push the Python button to open the application definition panel and expand the ‘More Options’ section.

Populate [Name] with DinoDatePython.
Click ‘Choose File’ for Archive and select the DinoDatePythonACCS.zip file.
Click ‘Choose File’ for Manifest and select the manifest.json file.
Click ‘Choose File’ for Deployment Configuration and select the deployment.json file.

You can change the values in the other fields as you’d like, but notice that since we defined “memory”: “1G” and “instances”: “1” in the deployment.json file those values will change automatically.

It’s also possible to include the manifest.json file in the DinoDatePythonACCS.zip file instead of uploading it separately.

Click Create.

It may take several minutes for ACCS to setup the environment and deploy the application. Once it’s done click on the URL: link to open the application.

Try it out

You can log in with any of the existing users, such as:

Bob
bob@example.com
Admin
admin@example.com

Use any value for the password, the application doesn’t check it.

Click on the Search tab and search for ‘eat’ it should return 6 of the pre-loaded dinosaurs.

Quick Review

Download the dependencies.
Create a launch script that will install the dependencies and launch the application.
Collect the required deployment artifacts and dependencies into a .zip file.
Create a manifest.json file that contains at least the required Python version and the command used to start your application.
Create a deployment.json file that contains any needed environment variable definitions. Optionally you can include ACCS environment definitions such as required memory and number of instances. (This file is optional. You could include the environment variables in your launch script.)
Reminder: ACCS will use the pre-defined environment variable $PORT. Make sure your application listens on $PORT.
Use the ACCS service console to upload your 3 files and create your new application.

If you run into any trouble, leave a comment and I’ll be happy to help.

DevOps, JET, NodeJS, Python

Deploy NodeJS & Python3 applications on an Oracle Cloud Compute instance

October 16, 2017 bcarter 4 Comments

DinoDate

DinoDate currently has both Python and NodeJS mid-tier applications and is backed by an Oracle Database.

The following instructions show how to deploy DinoDate to an Oracle Cloud Compute instance. However, if you just need to deploy a NodeJS or Python application, the same instructions should help you install Node and/or Python 3.

If you don’t have access to an Oracle Database you can try the Oracle Cloud for free.

Database

Download/Clone DinoDate to get the database scripts you’ll need.

Create an Oracle Cloud database.

Connect to your database as sys with sysdba and run coreDatabase/dd_master_install.sql. (Use your password and connect string)

1	sql sys/YourPassword@YourJdbcConnecString as sysdba @coreDatabase/dd_master_install.sql

Compute

Create an Oracle Cloud Compute instance.

Open the ports for our NodeJS and Python apps.

Download and scp the following to your new compute instance. (Current versions as of the time of this post.)

Open an ssh connection to your compute instance. (Use your ssh key and the public IP address for your compute instance)

1	ssh -i ~/.ssh/yourKey opc@publicIPforYourCompute

Switch to su
Update your instance
Install some tools we’ll need

sudo su

yum update -y

yum install -y gcc-c++ make git

Install both Oracle Instant Client files

1 2	yum install -y oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm yum install -y oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm

Install NodeJS 8

curl -sL https://rpm.nodesource.com/setup_8.x | bash -

yum install -y nodejs

npm install -g pm2 bower

Install some tools we’ll need
Enable the config manager
Install Python 3.5
Enable Python 3.5
Upgrade pip
Install the python modules for DinoDate
- cx_Oracle
- bottle
Exit scl bash:

yum install -y yum-utils

yum-config-manager --enable ol7_software_collections

yum install -y scl-utils rh-python35 --skip-broken

scl enable rh-python35 bash

pip install --upgrade pip

python -m pip install cx_Oracle bottle --upgrade

exit

Exit su

exit

Add the following to your .bash_profile:

Create the environment variables for DinoDate (use the JDBC connect string for your database)
Enable Python 3.5.

echo "

export dd_connectString=YourJdbcConnecString

export dd_user=dd

export dd_password=dd

export dd_port=8888

export dd_python_port=8088

export dd_node_port=3000

export dd_clientAppCodeDir=jet

export dd_python_clientAppCodeDir=jet

export dd_node_clientAppCodeDir=jet

export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib/:$LD_LIBRARY_PATH

scl enable rh-python35 bash" >> ~/.bash_profile

Re-run .bash_profile

1	source ~/.bash_profile

Clone DinoDate to your Compute instance
Extract the Oracle JET files
Run bower install

git clone https://github.com/OsBlaineOra/dino-date.git

unzip ~/oraclejet.zip -d ~/dino-date/commonClient/jet/

cd ~/dino-date/commonClient/jet/

bower install

Install the NodeJS modules
Use pm2 to start the NodeJS version of DinoDate
The –watch parameter will restart the application if the files change.

cd ~/dino-date/nodejs/

npm install

pm2 start server.js --watch

(We already installed the Python modules above)
Use pm2 to start the NodeJS version of DinoDate
The –watch parameter will restart the application if the files change.

1 2	cd ~/dino-date/python pm2 start app.py --watch

‘pm2 startup’ will generate the command needed to restart our applications on boot. The following will extract and execute the command from the generated text.

1	$(pm2 startup \| grep sudo)

Try it out

Open a browser and pull up DinoDate:

NodeJS
http://YourComputePublicIP:3000
Python
http://YourComputePublicIP:8088

You can log in with any of the existing users, such as:

Bob
bob@example.com
Admin
admin@example.com

Use any value for the password, the application doesn’t check it.

Click on the Search tab and search for ‘eat’ it should return 6 of the pre-loaded dinosaurs.

If you run into any trouble, leave a comment and I’ll be happy to help.

Information, JET, Meetups/Conventions, Python

Track-a-Watt – IoT to the Database: code walkthrough

June 8, 2017 bcarter Leave a comment

This is a companion post to my Track-a-Watt – IoT to the Database presentation.

If I missed you at GLOC 2017, you can still catch it at KScope 2017 or OpenWest 2017.

I’ve packed loads of stuff into this presentation, including: soldering (no software involved), Python, Javascript, HTML, PL/SQL and a little SQL (there has to be at least a little SQL in any application! :-)).

Even if I had a few hours of presentation time, it’d be hard to do justice to all these different scripts in their different languages, without losing lots of my audience somewhere along the way. So the presentation keeps things brief and to the point, and I will use this post to provide more depth for some of the code sections.

Python modules

sensorhistory.py

I mention that there are some names and labels used in this module that reference “5 minutes”.

I didn’t find any instances where a value for 5 minutes (300 seconds) is used in the functionality. Five minutes is only used as labels and object names.

The declarations for these can be found on lines:

103 – cumulative5mwatthr.
A variable used to store the cumulative watts per hour readings since the timer was started. We’ll call this total-watt-hours below.
105 – fiveminutetimer.
A variable used to store the time when the timer was initialized. We’ll call this start-time below.
119 – reset5mintimer.
A function to reset start-time and total-watts.
123 – avgwattover5min.
A function that prints the current data and returns the calculated average watts per hour since the timer started.
124 – fivetimer.
A text label in the print statement.
125 – 5mintimer and 5minwatthr
Labels in the text returned by the __str__ function.

This is just a demo, so I didn’t rename these objects. I only highlight these in case the names cause confusion after I change the timer to 10 seconds.

xbee.py

I only made one change in this module due to an error I received. I have been running this project on both Windows 7 and Fedora 25 machines. On one machine the values for p are passed in as Unicode and the other they are Strings.

The change here just checks to see if p is a String if so, convert it to Unicode otherwise accept it as is. Thanks, Anthony Tuininga for making this clean and compact.

def init_with_packet(self, p):

# p = [ord(c) for c in p]

p = [ord(c) for c in p] if isinstance(p, str) else [c for c in p]

wattcher.py to wattcher-min.py

The original code for the Tweet-a-Watt project has some functionality that I don’t intend to use for my simple graph. I created the wattcher-min.py module by stripping out most of these features.

Average Watts/Hour calculation

As far as I can prove with my (cough cough) math skills, the algorithm used to calculate watts per hour works for whatever time slice you want to track.

I have not gone through all of the code that leads up to this point, but as I understand it:

The kill-o-watt is collecting a constant stream of readings.
The kill-o-watt X-Bee transmits the reading to the computer every 2 seconds where the data is stored in the array, wattdata[].
This code calculates and stores the average watts used in the last second.

127

128

129

130

131

132

# sum up power drawn over one 1/60hz cycle

avgwatt = 0

# 16.6 samples per second, one cycle = ~17 samples

for i in range(17):

avgwatt += abs(wattdata[i])

avgwatt /= 17.0

To calculate the average W/Hr during our current time slice:

Calculate the number of seconds since the last reading.
Multiply the average watts per second by the elapsed seconds then divide by 3600 (seconds in an hour).
Reset the last reading timer.
Print the data.
Add the calculated average W/Hr for this time slice to the running total.

146

147

148

149

150

151

152

# add up the delta-watthr used since last reading

# Figure out how many watt hours were used since last reading

elapsedseconds = time.time() - sensorhistory.lasttime

dwatthr = (avgwatt * elapsedseconds) / (60.0 * 60.0) # 60 seconds in 60 minutes = 1 hr

sensorhistory.lasttime = time.time()

print("\t\tWh used in last {} seconds: {}".format(elapsedseconds,dwatthr))

sensorhistory.addwatthr(dwatthr)

Here’s a basic explanation:

When a chunk of data comes in, we calculate the average W/Hr for the first second of that chunk. Multiply that value by the number of seconds since the previous reading. This gives us the average W/Hr for a 2 second time slice. If we were to collect those slices for one hour and add them together we would have X watts used in one hour.

The cumulative watts used will continue to accrue until we pass the limit of the timer we’re using to determine how often to send the data up to ORDS.

154

155

156

157

158

159

160

# Determine the minute of the hour (ie 6:42 -> '42')

currminute = (int(time.time())/60) % 10

print(int(time.time()))

# Figure out if its been five minutes since our last save

if (((time.time() - sensorhistory.fiveminutetimer) >= 60.0)

and (currminute % 5 == 0)

154

155

156

157

158

159

160

# Determine the minute of the hour (ie 6:42 -> '42')

currminute = (int(time.time())/60) % 10

print(int(time.time()))

# Figure out if its been five minutes since our last save

if (((time.time() - sensorhistory.fiveminutetimer) >= 10.0)

# and (currminute % 5 == 0)

To calculate the average W/Hr during the last 10 seconds:

Multiply the cumulative watts used by 3600 (seconds in an hour).
Divide by the seconds since the last time we sent data to ORDS.

123

124

125

def avgwattover5min(self):

print("cumulative: %f, time.time: %f, fivetimer: %f" % (self.cumulative5mwatthr, time.time(), self.fiveminutetimer))

return self.cumulative5mwatthr * (60.0*60.0 / (time.time() - self.fiveminutetimer))

The short explanation is if we were getting a consistent reading of 5 watts per hour for every sample, every 10 seconds this calculation would come out to 5 W/Hr during the last 10 seconds. However, it’s not likely that we will get the same 5 W/Hr every reading so this function will give us the average W/Hr during the last 10 seconds.

I can understand if you’re a bit confused at this point. There seem to be a couple extra steps here than what should be needed for my simple graph. I had to work out a simulation in a spreadsheet before I could accept that it was working. However, I left the calculation code alone assuming that it may be needed for some of the more advanced versions of the project.

If your math skills are better than mine and you find that my explanation is wrong or you can explain it better, please leave a comment.

Oracle Jet

The Oracle Jet graph used in the example is the basic Line with Area Chart. I’m using the Y axis for the W/Hr data and the X axis for the timestamps.

The graph has the capability to track multiple series of data which would be useful for multiple kill-a-watts, but I’m only using one in the presentation.

The relationship between the X and Y axises is positional using the array position for the data elements in two arrays.

JavaScript

This is a typical jQuery ajax GET function.

Inside the success function:

Get the items array from the response.
Create a variable for the X-axis data.
Create a variable for the Y-axis data. Since we’re only tracking one sensor we can define the name value and initialize an items array for it.
Loop through the response items.
Populate the items array for our sensor (Y axis).
Populate the timestamp array (X axis).
Set the ko.observable objects for the two axises of the graph.

Next is a short function to call getData() every 5 seconds.

var getData = function() {

$.ajax({

type: "GET",

url: 'https://oda-a425020.db.us2.oraclecloudapps.com/apex/kaw_demo/power/usage',

success: function(res) {

//Get the items from the response

var resData = res.items;

var groupData = [];

var areaSeries = [{name: "Sensor 1", items:[]}];

for (var i = 0; i < resData.length; i++) {

//push the watt value to the areaSeries[0] items array

// We are only tracking one sensor so it's always areaSeries[0]

areaSeries[0].items.push(resData[i].watt);

//push the created_on value to the groupData array

groupData.push(resData[i].created_on);

}

//set the observables to the proper values

self.areaSeriesValue(areaSeries);

self.areaGroupsValue(groupData);

failure: function(jqXHR, textStatus, errorThrown) {

alert(textStatus);

}

});

};

//Get the data every 5 seconds.

setInterval(function() {

getData();

}, 5000);

HTML

We copy the HTML from the cookbook for just the graph component.

Since we’re not using the additional functionality from the Jet Cookbook example we remove the highlighted lines (14, 15).

<div id="lineAreaChart" data-bind="ojComponent: {

component: 'ojChart',

type: 'lineWithArea',

series: areaSeriesValue,

groups: areaGroupsValue,

animationOnDisplay: 'auto',

animationOnDataChange: 'auto',

orientation: orientationValue,

stack: stackValue,

hoverBehavior: 'dim'

style="max-width:1000px;width:100%;height:350px;">

</div>

Go try something new

The goal of this presentation is to encourage people to go out and try something a little out of their comfort zone. If you think your soldering skills are lacking find a maker group in your area and take a class. If you are strong in one programming language try another.

This is a great project to experiment with, there are a few different skills all mixed together, each of them is fairly close to entry level and they are popular enough that there should be a lot of help available.

As always, if you run into issues feel free to leave a comment here or hit me up on twitter and I’ll be glad to help get you going.

I plan to update this post as questions arise. If you’d like to see it all running together catch one of my upcoming sessions.

Learning, NodeJS, PL/SQL, Python

Getting DinoDate Up and Running

April 8, 2016 bcarter 4 Comments

You’ve downloaded DinoDate, the premier dating website for dinosaurs. Now lets get it up and running!

Prerequisites

Download Logger and extract it to dino-date/coreDatabase
Check Logger’s create_user.sql file. If you see an exit statement at the very end , remove it.
Install Bower.
Install Oracle Instant Client with the sdk modules.

Installation

Database Schemas and Objects

IMPORTANT – Please make sure you’re using a database instance in which you can safely create schemas named DD, DD_NON_EBR and DD_LOGGER.

Navigate to dino-date/coreDatabase
Run dd_master_install.sql from an account connected “as sysdba”.
First prompt is for the directory into which you extracted Logger, e.g. Logger_3.1.1
Second prompt is what you want to name the schema that holds the Logger database objects.
Third and fourth prompts are for your Tablespace and Temporary Tablespace.
Fourth prompt is for the logger schema password

Common Client

Open a prompt and navigate to dino-date/commonClient

1	bower install

RESTful Tier

Currently there are RESTful APIs written in both NodeJS and Python. You can choose to run one or both.

NodeJS

Open a prompt and navigate to dino-date/nodejs

1	npm install

Python

Open a prompt and navigate to dino-date/python

1 2	pip install cx_Oracle pip install bottle

Configure your Environment

DinoDate uses environment variables for database connection and port settings.

Create the following environment variables using the correct values for your system.

dd_connectString=localhost:1521/orcl
dd_user=dd
dd_password=dd
dd_port=8888
dd_python_port=8080
dd_node_port=3000

Run DinoDate

NodeJS

Open a prompt and navigate to dino-date/nodejs

1	node server.js

DinoDate (NodeJS) will be listening on the dd_node_port port you defined above.

1	http://localhost:3000/#/

Python

Open a prompt and navigate to dino-date/python

1	python3 app.py

DinoDate (Python) will be listening on the dd_python_port port you defined above.

1	http://localhost:8080/#/

Running Both

You can run both versions at the same time, provided you specified different ports.

This will allow you to switch between languages by changing the ports and view the code examples specifically for each language.

Please leave a comment if you run into trouble.

Learning, Python

Delete (cruD) using cx_Oracle

July 9, 2015 bcarter 2 Comments

In this post we’re going to take a look at the D in CRUD: Delete.

We use the cx_Oracle driver to delete some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

My helper function get_all_rows() encapsulates a select statement used to verify that the deletes worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

def get_all_rows(label, data_type='people'):

# Query all rows

cur = con.cursor()

if (data_type == 'pets'):

statement = 'select id, name, owner, type from cx_pets order by owner, id'

else:

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

cur.execute(statement)

res = cur.fetchall()

print(label + ': ')

print (res)

print(' ')

cur.close()

Add this function to the top of your file.

Resetting the data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.py with the following code and run it whenever you would like to reset the data. (Notice this version adds people and pet data not included in other sections.)

import cx_Oracle
import os
connectString = os.getenv('db_connect')
con = cx_Oracle.connect(connectString)
cur = con.cursor()

# Delete rows
statement = 'delete from cx_pets'
cur.execute(statement)
 
# Reset Identity Coulmn
statement = 'alter table cx_pets modify id generated BY DEFAULT as identity (START WITH 8)'
cur.execute(statement)

# Delete rows
statement = 'delete from cx_people'
cur.execute(statement)

# Reset Identity Coulmn
statement = 'alter table cx_people modify id generated BY DEFAULT as identity (START WITH 8)'
cur.execute(statement)

# Insert default rows
rows = [(1, 'Bob', 35, 'I like dogs'),
        (2, 'Kim', 27, 'I like birds'),
        (3, 'Cheryl', 23, 'I like horses'),
        (4, 'Bob', 27, 'I like rabbits'),
        (5, 'Stacey', 45, 'I like snakes'),
        (6, 'Pete', 23, 'I like cats'),
        (7, 'Pat', 36, 'I like dogs')]
cur.bindarraysize = 2
cur.setinputsizes(int, 20, int, 100)
cur.executemany("insert into cx_people(id, name, age, notes) values (:1, :2, :3, :4)", rows)
con.commit()

# Insert default rows
rows = [(1, 'Duke', 1, 'dog'),
        (2, 'Dragon', 2, 'bird'),
        (3, 'Sneaky', 5, 'snake'),
        (4, 'Red', 2, 'bird'),
        (5, 'Red', 3, 'horse'),
        (6, 'Buster', 1, 'dog'),
        (7, 'Fido', 7, 'cat')]
cur.bindarraysize = 2
cur.setinputsizes(int, 20, int, 100)
cur.executemany("insert into cx_pets (id, name, owner, type) values (:1, :2, :3, :4)", rows)
con.commit()

cur.close()

import cx_Oracle

import os

connectString = os.getenv('db_connect')

con = cx_Oracle.connect(connectString)

cur = con.cursor()

# Delete rows

statement = 'delete from cx_pets'

cur.execute(statement)

# Reset Identity Coulmn

statement = 'alter table cx_pets modify id generated BY DEFAULT as identity (START WITH 8)'

cur.execute(statement)

# Delete rows

statement = 'delete from cx_people'

cur.execute(statement)

# Reset Identity Coulmn

statement = 'alter table cx_people modify id generated BY DEFAULT as identity (START WITH 8)'

cur.execute(statement)

# Insert default rows

rows = [(1, 'Bob', 35, 'I like dogs'),

(2, 'Kim', 27, 'I like birds'),

(3, 'Cheryl', 23, 'I like horses'),

(4, 'Bob', 27, 'I like rabbits'),

(5, 'Stacey', 45, 'I like snakes'),

(6, 'Pete', 23, 'I like cats'),

(7, 'Pat', 36, 'I like dogs')]

cur.bindarraysize = 2

cur.setinputsizes(int, 20, int, 100)

cur.executemany("insert into cx_people(id, name, age, notes) values (:1, :2, :3, :4)", rows)

con.commit()

# Insert default rows

rows = [(1, 'Duke', 1, 'dog'),

(2, 'Dragon', 2, 'bird'),

(3, 'Sneaky', 5, 'snake'),

(4, 'Red', 2, 'bird'),

(5, 'Red', 3, 'horse'),

(6, 'Buster', 1, 'dog'),

(7, 'Fido', 7, 'cat')]

cur.bindarraysize = 2

cur.setinputsizes(int, 20, int, 100)

cur.executemany("insert into cx_pets (id, name, owner, type) values (:1, :2, :3, :4)", rows)

con.commit()

cur.close()

Boilerplate template

The template we will be using is:

import cx_Oracle

import os

connectString = os.getenv('db_connect')

con = cx_Oracle.connect(connectString)

def get_all_rows(label, data_type='people'):

# Query all rows

cur = con.cursor()

if (data_type == 'pets'):

statement = 'select id, name, owner, type from cx_pets order by owner, id'

else:

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

cur.execute(statement)

res = cur.fetchall()

print(label + ': ')

print (res)

print(' ')

cur.close()

get_all_rows('Original Data', 'pets')

# Your code here

get_all_rows('New Data', 'pets')

For each exercise, replace the “# Your code here” line with your code.

Reset the data

First let’s run reset_data.py to setup our data.

Simple delete

We will perform a simple delete that removes a single record in the cx_people table. These are the steps performed in the code snippet below.

Get a cursor object from our connection. We will use this cursor to perform our database operations.
Prepare a SQL DELETE statement, deleting the cx_pets record with an id of 1.
Execute the statement using bind variables. (see the R part of this series for an explanation of bind variables)
Commit the transaction.

cur = con.cursor()

statement = 'delete from cx_pets where id = :id'

cur.execute(statement, {'id':1})

con.commit()

When I run this code in my Python session, I see:

Original Data:

[<del>(1, 'Duke', 1, 'dog')</del>, (6, 'Buster', 1, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

New Data:

[(6, 'Buster', 1, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

Extra Fun 1

Delete all the birds .

Your results should be:

Original Data:

[(6, 'Buster', 1, 'dog'), <del>(2, 'Dragon', 2, 'bird')</del>, <del>(4, 'Red', 2, 'bird')</del>, (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

New Data:

[(6, 'Buster', 1, 'dog'), (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

Answer

cur = con.cursor()

statement = 'delete from cx_pets where type = :type'

cur.execute(statement, {'type':'bird'})

con.commit()

Reset the data

Now is a good time to run reset_data.py.

Boilerplate change

Change the boilerplate get_all_rows statements to get people and pet data.

get_all_rows('Original People Data', 'people')

get_all_rows('Original Pet Data', 'pets')

# Your code here

get_all_rows('New People Data', 'people')

get_all_rows('New Pet Data', 'pets')

Deleting records referenced by Foreign Keys

If you are using integrity constraints in your database (of course you are, because then you let the database do some heavy lifting for you), you will sometimes need to change the way you process your changes.

In our design, we have a Foreign Key constraint in cx_pets that ensures if a pet has an owner, that owner exists.

This is the statement that creates the constraint in the Creating the Database Objects section of the Initial Setup post.

1 2	ALTER TABLE CX_PETS ADD CONSTRAINT FK_CX_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "CX_PEOPLE" ("ID") /

If we attempt to delete a record in cx_people that is referenced in cx_pets (Person has a pet,) we get an error.

cur = con.cursor()

statement = 'delete from cx_people where id = :id'

cur.execute(statement, {'id':1})

con.commit()

When I run this code in my Python session, I see:

Traceback (most recent call last):

File "delete.py", line 25, in <module>

cur.execute(statement, {'id':1})

cx_Oracle.IntegrityError: ORA-02292: integrity constraint (DD.FK_CX_PETS_OWNER)

violated - child record found

Before deleting the person you have to handle the pet (watch out for claws and teeth).

There are a few options here, depending on your database design:

If: pets are not required to have an owner and you only want to delete the person, not the pets. Then: you can update the pets and set their owner to null.
If: pets are required to have an owner. Then: you can delete the pets for the owner.

In either of the above scenarios you can update the pets and set their owner to another person.

Bob is moving out of our area and his new apartment doesn’t allow pets, so he’s giving them to Kim. Let’s use that last option here.

Get a cursor object from our connection. We will use this cursor to perform our database operations.
Prepare a SQL UPDATE statement, changing owner to 2 (Kim) for the records with an owner of 1 (Bob). Updating is covered in the U part of this series.
Execute the statement using bind variables.
Prepare a SQL DELETE statement, deleting records with an id of 1 (Bob).
Execute the statement using bind variables. (see the R part of this series for an explanation of bind variables)
Commit the transaction.

cur = con.cursor()

statement = 'update cx_pets set owner = :1 where owner = :2'

cur.execute(statement, (2, 1))

statement = 'delete from cx_people where id = :id'

cur.execute(statement, {'id':1})

con.commit()

When I run this code in my Python session, I see:

Original People Data:

[<del>(1, 'Bob', 35, 'I like dogs')</del>, (2, 'Kim', 27, 'I like birds'), (3, 'Cheryl', 23, 'I like horses'), (4, 'Bob',

27, 'I like rabbits'), (5, 'Stacey', 45, 'I like snakes'), (6, 'Pete', 23, 'I like cats'), (7, 'Pat', 36, 'I like dogs')]

Original Pet Data:

[(1, 'Duke', 1, 'dog'), (6, 'Buster', 1, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

New People Data:

[(2, 'Kim', 27, 'I like birds'), (3, 'Cheryl', 23, 'I like horses'), (4, 'Bob', 27, 'I like rabbits'), (5, 'Stacey', 45, 'I like snakes'), (6, 'Pete', 23, 'I like cats'), (7, 'Pat', 36, 'I like dogs')]

New Pet Data:

[(1, 'Duke', 2, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), (6, 'Buster', 2, 'dog'), (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

When you change data it’s a good idea to verify the number of affected rows. This is covered in the R part of this series.

Extra Fun 2

Due to a zoning change, snakes are no longer allowed in our area. Stacey has decided to move and take Sneaky with her.

Lets fix our data.

Your results should be:

Original People Data:

[(2, 'Kim', 27, 'I like birds'), (3, 'Cheryl', 23, 'I like horses'), (4, 'Bob', 27, 'I like rabbits'), <del>(5, 'Stacey', 45, 'I like snakes'),</del> (6, 'Pete', 23, 'I like cats'), (7, 'Pat', 36, 'I like dogs')]

Original Pet Data:

[(1, 'Duke', 2, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), (6, 'Buster', 2, 'dog'), (5, 'Red', 3, 'horse'), <del>(3, 'Sneaky', 5, 'snake'),</del> (7, 'Fido', 7, 'cat')]

New People Data:

[(2, 'Kim', 27, 'I like birds'), (3, 'Cheryl', 23, 'I like horses'), (4, 'Bob', 27, 'I like rabbits'), (6, 'Pete', 23, 'I like cats'), (7, 'Pat', 36, 'I like dogs')]

New Pet Data:

[(1, 'Duke', 2, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), (6, 'Buster', 2, 'dog'), (5, 'Red', 3, 'horse'), (7, 'Fido', 7, 'cat')]

Answer

cur = con.cursor()

statement = 'delete from cx_pets where owner = :owner'

cur.execute(statement, {'owner':5})

statement = 'delete from cx_people where id = :id'

cur.execute(statement, {'id':5})

con.commit()

Some other things you could try

Change the database constraints to delete or Null the child record on delete (a cascading delete). Delete a person and let the database handle the children.
Remove the people who don’t have any pets.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Learning, Python

Update (crUd) using cx_Oracle

July 2, 2015 bcarter 5 Comments

In this post we’re going to take a look at the U in CRUD: Update.

We use the cx_Oracle driver to update some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

My helper function get_all_rows() encapsulates a select statement used to verify that the updates worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

def get_all_rows(label, data_type='people'):

# Query all rows

cur = con.cursor()

if (data_type == 'pets'):

statement = 'select id, name, owner, type from cx_pets order by owner, id'

else:

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

cur.execute(statement)

res = cur.fetchall()

print(label + ': ')

print (res)

print(' ')

cur.close()

Add this function to the top of your file.

Resetting the data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.py with the following code and then run it whenever you would like to reset the data. (Notice this version adds pet data not included in other sections.)

import cx_Oracle
import os
connectString = os.getenv('db_connect')
con = cx_Oracle.connect(connectString)
cur = con.cursor()

# Delete rows
statement = 'delete from cx_people'
cur.execute(statement)

# Reset Identity Coulmn
statement = 'alter table cx_people modify id generated BY DEFAULT as identity (START WITH 3)'
cur.execute(statement)

# Insert default rows
rows = [(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]
cur.bindarraysize = 2
cur.setinputsizes(int, 20, int, 100)
cur.executemany("insert into cx_people(id, name, age, notes) values (:1, :2, :3, :4)", rows)
con.commit()

# Insert default rows
rows = [(1, 'Duke', 1, 'dog'),
        (2, 'Pepe', 2, 'bird'),
        (3, 'Princess', 1, 'snake'),
        (4, 'Polly', 1, 'bird'),
        (5, 'Rollo', 1, 'horse'),
        (6, 'Buster', 1, 'dog'),
        (7, 'Fido', 1, 'cat')]
cur.bindarraysize = 2
cur.setinputsizes(int, 20, int, 100)
cur.executemany("insert into cx_pets (id, name, owner, type) values (:1, :2, :3, :4)", rows)
con.commit()

cur.close()

import cx_Oracle

import os

connectString = os.getenv('db_connect')

con = cx_Oracle.connect(connectString)

cur = con.cursor()

# Delete rows

statement = 'delete from cx_pets'

cur.execute(statement)

# Reset Identity Coulmn

statement = 'alter table cx_pets modify id generated BY DEFAULT as identity (START WITH 8)'

cur.execute(statement)

# Delete rows

statement = 'delete from cx_people'

cur.execute(statement)

# Reset Identity Coulmn

statement = 'alter table cx_people modify id generated BY DEFAULT as identity (START WITH 3)'

cur.execute(statement)

# Insert default rows

rows = [(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]

cur.bindarraysize = 2

cur.setinputsizes(int, 20, int, 100)

cur.executemany("insert into cx_people(id, name, age, notes) values (:1, :2, :3, :4)", rows)

con.commit()

# Insert default rows

rows = [(1, 'Duke', 1, 'dog'),

(2, 'Pepe', 2, 'bird'),

(3, 'Princess', 1, 'snake'),

(4, 'Polly', 1, 'bird'),

(5, 'Rollo', 1, 'horse'),

(6, 'Buster', 1, 'dog'),

(7, 'Fido', 1, 'cat')]

cur.bindarraysize = 2

cur.setinputsizes(int, 20, int, 100)

cur.executemany("insert into cx_pets (id, name, owner, type) values (:1, :2, :3, :4)", rows)

con.commit()

cur.close()

Boilerplate template

The template we will be using is:

import cx_Oracle

import os

connectString = os.getenv('db_connect')

con = cx_Oracle.connect(connectString)

def get_all_rows(label, data_type='people'):

# Query all rows

cur = con.cursor()

if (data_type == 'pets'):

statement = 'select id, name, owner, type from cx_pets order by owner, id'

else:

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

cur.execute(statement)

res = cur.fetchall()

print(label + ': ')

print (res)

print(' ')

cur.close()

get_all_rows('Original Data')

# Your code here

get_all_rows('New Data')

For each exercise, replace the “# Your code here” line with your code.

Simple update

We will perform a simple update that modifies a single record in the cx_people table. These are the steps performed in the code snippet below.

Get a cursor object from our connection. We will use this cursor to perform our database operations.
Prepare a SQL UPDATE statement, changing age to 31 for the record with an id of 1.
Execute the statement using bind variables. (See the R part of this series for an explanation of bind variables.)
Commit the transaction.

cur = con.cursor()

statement = 'update cx_people set age = :1 where id = :2'

cur.execute(statement, (31, 1))

con.commit()

When I run this code in my Python session, I see:

Original Data:

[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]

New Data:

[(1, 'Bob', 31, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]

Extra Fun 1

Update Bob’s notes to ‘I like cats’ .

Your results should be:

Original Data:

[(1, 'Bob', 31, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]

New Data:

[(1, 'Bob', 31, 'I like cats'), (2, 'Kim', 27, 'I like birds')]

Answer

cur = con.cursor()

statement = 'update cx_people set notes = :1 where id = :2'

cur.execute(statement, ("I like cats", 1))

con.commit()

Reset the data

Now is a good time to run reset_data.py.

Boilerplate change

Change the boilerplate get_all_rows statements to get pet data.

get_all_rows('Original Data', 'pets')

# Your code here

get_all_rows('New Data', 'pets')

Make sure your where clause is specific

In the above example, notice that we used the id column in our where clause. For our data set, id is the primary key. You do not always have to use a primary key, but you should make sure you only update the rows you intend to.

Next let’s look at updating multiple rows. We’ll have Bob give his dog Duke to Kim.

Get a cursor object from our connection. We will use this cursor to perform our database operations.
Prepare a SQL UPDATE statement, changing owner to 2 (Kim) for the records with an owner of 1 (Bob) and a type of ‘dog’.
Execute the statement using bind variables. (See the R part of this series for an explanation of bind variables.)
Commit the transaction.

cur = con.cursor()

statement = 'update cx_pets set owner = :1 where owner = :2 and type = :3'

cur.execute(statement, (2, 1, 'dog'))

con.commit()

When I run this code in my Python session, I see:

Original Data:

[(1, 'Duke', 1, 'dog'), (3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), (2, 'Pepe', 2, 'bird')]

New Data:

[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird'), (6, 'Buster', 2, 'dog')]

In our example we only used owner and type, assuming that Bob only had one dog, Duke, as it is in our original data. With the new reset data function we added a second dog Buster. This example is intended to demonstrate what may happen when multiple users are working with the same data set.

In our data, the only unique identifier for cx_pets is id. Bob may have two dogs, or even two dogs named Duke. Make sure if you intend to change a specific row you use a unique identifier.

It also helps to…

Verify the number of affected rows

Now lets give Buster back to Bob. This time we will use the unique id column and we will print out the number of rows affected using Cursor.rowcount.

Get a cursor object from our connection. We will use this cursor to perform our database operations.
Prepare a SQL UPDATE statement, changing owner to 1 (Bob) for the records with an id of 6 (Buster).
Execute the statement using bind variables. (See the R part of this series for an explanation of bind variables.)
Commit the transaction.

cur = con.cursor()

statement = 'update cx_pets set owner = :1 where id = :2'

cur.execute(statement, (1, 6))

con.commit()

print('Number of rows updated: ' + str(cur.rowcount))

print(' ')

When I run this code in my Python session, I see:

Original Data:

[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird'), (6, 'Buster', 2, 'dog')]

Number of rows updated: 1

New Data:

[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird')]

Cursor.rowcount will show you the number of rows affected for insert, update and delete statements and the number of rows returned in a select statement.

Extra Fun 2

Give all birds to Kim that she doesn’t already have and print the number of affected rows .

Your results should be:

Original Data:

[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird')]

Number of rows updated: 1

New Data:

[(3, 'Princess', 1, 'snake'), (5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird'), (4, 'Polly', 2, 'bird')]

Answer

cur = con.cursor()

statement = 'update cx_pets set owner = :1 where type = :2 and owner != :3'

cur.execute(statement, (2, 'bird', 2))

con.commit()

print('Number of rows updated: ' + str(cur.rowcount))

print(' ')

Some other things you could try

Change multiple column values
Preform an update that changes all rows, if the rowcount is greater than 2, rollback the change

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Learning, Python

Insert (Crud) using cx_Oracle

June 26, 2015 bcarter 1 Comment

In this post, we’re going to take a look at the C in CRUD: Create.

We will be using the cx_Oracle driver to create some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

I will be using a helper function get_all_rows(). This is a select statement used to verify that the inserts worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

def get_all_rows(label):

# Query all rows

cur = con.cursor()

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

cur.execute(statement)

res = cur.fetchall()

print(label + ': ')

print (res)

print(' ')

cur.close()

Add this function to the top of your file.

Resetting the data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.py with the following code and then run it whenever you would like to reset the data.

CLI Overview

Pre-requisits

Python 3.5+

RSA Key Pair

Cloud Account

Quickstart Install

Linux / Mac

Windows

Install with Python

Configure the OCI-CLI

Quick Test

Share this:

Create a WiFi Network

Change the WiFi credentials on the card

A Little Python

Arduino IDE

What Else Can You Do?

Share this:

Prerequisites

Setup

Cleanup

Boilerplate template

Anonymous PL/SQL Block

Execute a PL/SQL Procedure

Pass Parameters

Get PL/SQL Function Return Values

Out Parameters

Accept Argument Values

PL/SQL Exceptions

Additional Resources

Share this:

About Application Container Cloud Service

DinoDate

Database

Prepare the DinoDate Application

Download necessary files

Package the Files to Deploy

Important Note

Deploy to ACCS

Try it out

Quick Review

Share this:

DinoDate

Database

Compute

Try it out

Share this:

Python modules

sensorhistory.py

xbee.py

wattcher.py to wattcher-min.py

Average Watts/Hour calculation

Oracle Jet

JavaScript

HTML

Go try something new

Share this:

Prerequisites

Installation

Database Schemas and Objects

IMPORTANT – Please make sure you’re using a database instance in which you can safely create schemas named DD, DD_NON_EBR and DD_LOGGER.

Common Client

RESTful Tier

NodeJS

Python

Configure your Environment

Run DinoDate

NodeJS

Python

Running Both

Share this:

Helper Function

Resetting the data

Boilerplate template

Reset the data

Simple delete

Extra Fun 1

Reset the data

Boilerplate change

Deleting records referenced by Foreign Keys