Category Archives: Python

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.

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.

Python

26

27

28

definit_with_packet(self,p):

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

p=[ord(c)forcinp]ifisinstance(p,str)else[cforcinp]

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.

wattcher-min.py | Calculate average watts for the data sample.

Python

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

foriinrange(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.

wattcher-min.py | Calculate the average watts per hour since the last sample and add it to the running total.

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.

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.

HTML

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

dashboard.html - ojChart component

XHTML

7

8

9

10

11

12

13

14

15

16

17

18

19

<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.

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.

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.

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.

Reset the data

Boilerplate change

Change the boilerplate get_all_rows statements to get pet data.

1

2

3

4

5

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.)

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.)

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.

1

2

3

4

5

6

7

8

9

10

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.

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

What is a transaction?

You’ll notice in the bullet points above, I said to commit the transaction.

When you make execute Data Manipulation Language or DML statements, such as the insert I use in this post, those changes are only visible to your current connection or session.

Those changes will not be visible to other sessions (even another session connected to the same schema in which the changes were made) until you commit your changes. That step makes it “permanent” in the database, and available for everyone else to see (and possibly change in a future transaction).

Extra Fun 1 & 2

1. Insert more than 1 row .

Using data for ‘Rob’, 37, ‘I like snakes’ and ‘Cheryl’, 41, ‘I like monkey’ Your results should be:

1

2

3

4

5

6

Original Data:

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

NewData:

[(1,'Bob',35,'I like dogs'),(2,'Kim',27,'I like birds'),(3,'Sandy',31,'I like horses'),

Set the cursor’s setinputsizes. This tells the cursor what to expect from our data items. The first and third items are strings so we define the max length, the second is an int so we just use int. This allows the cx_Oracle driver to pre-define the memory needed.

Prepare a SQL INSERT statement, specifying the table and columns to insert the data.

[(1,'Bob',35,'I like dogs'),(2,'Kim',27,'I like birds'),(3,'Sandy',31,'I like horses'),

(4,'Suzy',29,'I like rabbits')]

There may be an easy way to use the returning option with executemany, but after searching the web for a while, the methods I found were complicated enough that I won’t go over them here. It seems to be easier to just use a PL/SQL function, which is also a topic for another time.

Some things you could try

Loop through an array of people and insert each one returning its id. Using that id add multiple pets with executemany()

Create a large array of people. Time the difference between looping through single inserts and using executemany()

However, we want to choose the name at run time and store it in a variable called person_name. You could accept the value in as an argument or passed into a function, but we’ll just set a variable to keep it simple.

This is very dangerous and opens our code to a SQL Injection attack. You can follow that link for more information, but we won’t be going into detail in this series. Just know that you should, generally, never allow end user input to be fed directly into a dynamic SQL statement.

A much safer way to pass external values into a SQL statement is by using bind variables with prepared statements.

You have a couple different options:

Positional:

cur.execute('select id, name, age, notes from cx_people where name = :2 and age = :1',

('Bob',35))

Notice the :1 and :2 are switched in the two examples. With a positional statement the labels do not matter, it could just as well have been :1 and :something. What matters is the first :variable in the statement will be assigned the first of the provided values- ‘Bob’ and the second – 35.

Named:

1

2

3

4

5

cur.execute('select id, name, age, notes from cx_people where name = :name and age = :age',

{'name':'Bob','age':35})

cur.execute('select id, name, age, notes from cx_people where name = :name and age = :age',

{'age':35,'name':'Bob'})

With this method the :name variable will be assigned the value of ‘name’ in the provided key value set.

Notice, in both examples, that we do not wrap the bind variable for the name with quotes. This is handled automatically when the statement is prepared for execution.

Example:

Get a cursor object from our connection. We will use this cursor to preform our database operations.

In this section we took a look at some basic query functionality. When you experiment with more complex queries, if you run into problems leave a comment here or on twitter and we’ll find an answer together.

Some things you could try

Join the cx_people and cx_pets table to get the people and their pets

Only retrieve the person’s name and age

Change the order to display in descending order.

Hint – If you have trouble getting a query to run in your code, try running it in SQL Plus or another database console tool. This will help determine if the problem is with the query or the code.

An ORM tool can handle many of the repetitive processes when interfacing with a database. Your project might call for an ORM tool such as SQLAlchemy or Pony. No doubt about it, a good ORM can come in very handy. An ORM application will typically have a function for passing in raw SQL if needed, so you may not need to go straight to the driver.

Why learn to use the driver directly?

An ORM brings its own, different complexity to a project. It may be overkill for some projects. There are also times when a specific task is just different enough that an ORM may not be able to help, or its application to your requirements become so complex that your code becomes difficult to maintain.

And if you’re like me, its hard to be satisfied with a black box approach. You want to know more about how your tools work and you want to have options, just in case.

This is not a security discussion

Where to store the database credentials for an app is a long-running discussion that depends on many things. What app server is being used, what OS, how sensitive is the data and of course how much time and money is available to invest (bad reason to skip security).

In my opinion, if a hacker can get far enough into your systems to pull down your files (application code, config files or others) they will be able to find the DB credentials for the app.

It comes down to the point, that the information is accessible to your application somehow. If they breach far enough to get your code or config files, they will be able to find and attack that method.

There are many solutions out there and some are quite good so I’m not going to get into the best way to secure a system.

Using environment variables is relatively simple.

Example

Setting environment variables on Linux

1

export db_connect=user/pw@db

or on Windows

1

setdb_connect=user/pw@db

Then in Python using the os package

1

2

3

4

import cx_Oracle

import os

connectString=os.getenv('db_connect')

con=cx_Oracle.connect(connectString)

Or in Ruby using ENV

1

2

3

require'oci8'

connectString=ENV['db_connect']

con=cx_Oracle.connect(connectString)

Setting the variables on the command line like this is temporary for the current session. Consult your OS instructions for a more permanent method if needed.

Why?

There are a couple reasons I prefer this method over hard-coding or config files.

Accidental commits

1

2

connectString=os.getenv('db_connect')

con=cx_Oracle.connect(connectString)

vs

1

con=cx_Oracle.connect('user/pw@db')

Which would you rather ‘accidentally’ push to GitHub?
I could expand on this but I think that covers it.

Different environments

They are called environment variables for a reason. On your development machine, you may have a DB running in a VirtualBox instance. Your test servers will probably have their own databases. And only certain people should even know the credentials for the Production server.

Using environment variables, there is no need for maintaining multiple versions of config files or worse yet, source code. Each environment is configured independently of the application.

Quick switching

This would be more of a development or test thing. But, if you need to run your code against multiple different databases you would simply change the environment variable and not any config files or source code. Remember, every time you modify the source code, no matter how small of a change, there’s a chance for a mistype to bring it all down.

Deploying to a Platform as a Service (PaaS)

Most PaaS systems will spin resources up and down as needed, including your database. If your application is using a database provided by the PaaS the process of spinning up the database would include creating secure credentials. To simplify the process the PaaS may simply set the environment variables for the credentials and your application never needs to change.

For a bit of extra security, they may automatically change the credentials whenever the system is restarted. You would be able to log onto your server and get the current credentials if you need them, otherwise, they just work.

I ran into a small issue last week when I tried the following command:

1

pip install cx_Oracle

This returns “Python Pip install Error: Unable to find vcvarsall.bat.” This is not a problem with cx_Oracle, it happens with any pip install that tries to compile.

If you want to skip the rant below and just get the driver loaded, the immediate solution is to download the binary installer and move on. That worked fast and easy.

<Rant>
Before I found the simple solution above, my search found recommendations to install the VS compiler for Python 2.7, upgrade pip, and even to load Visual Studio Express.

BTW, the VS compiler for Python 2.7 took a loooooooong time to install. An unacceptably long time. VS Express added a few other apps to my system that I didn’t want. As far as I could tell in the installer, there was no option to not load them.

In the process of trying these different solutions, none of which worked, I wound up with a lot of extra software that I didn’t want.

Now I am a newb to Python, but not to Windows. I went in and tried to remove the bloatware that came with VS express and the other software, such as multiple versions of VS compiler. As I expected, when I installed VS it modified some existing apps somewhere. So when I removed VS it broke other apps that had been working fine before all of this. Thankfully, I’ve done this enough times before, so I had a system restore point ready.

System restored, moving on.

The problem, as far as I can tell, is that the latest Python tools do not play well with the latest Microsoft tools when you are on a 64 bit OS.
</Rant>

Long term, as I move on to building real Python apps, I found a few workarounds that claim to solve this problem for windows that I could try. But first, I plan to switch over to my Linux install and work there. At this point I’m willing to bet it won’t be such a mess.