Category Archives: Python

Getting DinoDate Up and Running

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.
  1. Navigate to dino-date/coreDatabase
  2. Run dd_master_install.sql from an account connected “as sysdba”.
  3. First prompt is for the directory into which you extracted Logger, e.g. Logger_3.1.1
  4. Second prompt is what you want to name the schema that holds the Logger database objects.
  5. Third and fourth prompts are for your Tablespace and Temporary Tablespace.
  6. Fourth prompt is for the logger schema password
Common Client

Open a prompt and navigate to dino-date/commonClient

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

Python

Open a prompt and navigate to dino-date/python

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

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

Python

Open a prompt and navigate to dino-date/python

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

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.

Delete (cruD) using cx_Oracle

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.

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

Boilerplate template

The template we will be using is:

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.
When I run this code in my Python session, I see:

Extra Fun 1

Delete all the birds .

Your results should be:

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

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.

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

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

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.
When I run this code in my Python session, I see:

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:

Answer
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

Update (crUd) using cx_Oracle

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.

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

Boilerplate template

The template we will be using is:

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.
When I run this code in my Python session, I see:

Extra Fun 1

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

Your results should be:

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

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.
When I run this code in my Python session, I see:

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.
When I run this code in my Python session, I see:

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:

Answer
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

Insert (Crud) using cx_Oracle

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.

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.

Boilerplate template

The template we will be using is:

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

Simple insert

We will perform a simple insert that adds a single record into 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 INSERT statement, specifying the table and columns to insert the data.
  • Execute the statement using bind variables.  (see the R part of this series for an explanation of bind variables)
  • Commit the transaction.
When I run this code in my Python session, I see:

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:

Insert-02

Answer

2.  Verify that a second connection cannot see your changes till after the commit.

Using data for ‘Suzy’, 31, ‘I like rabbits’ and assuming that you did the previous exercise your results should be:

Insert-03

Notice that after the insert, the connection that made the insert can see Suzy but the second connection can’t.

After the commit, both connections see Suzy.

Answer
Reset the data

Now is a good time to run reset_data.py.

Using Identity Columns

You may have noticed that the id column is not passed in, but is automatically set sequentially.  Prior to Oracle Database 12c, this was accomplished using a sequence and a trigger.

In 12c, this can be accomplished by using an Identity Column.

 You can find more information on identity columns here(pdf).
Returning data after an insert
 Sometimes we need to perform additional operations after an insert using data generated by the database, such as the identity column above.  For example, let’s add a person and a pet for them.

We could run an insert then select the value back using the name.  But if the name is not unique we’ll have a problem.  This is where the RETURNING clause is helpful.

We will perform an insert that adds a single record into the cx_people table. Then using the returned id we will add a pet.  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.
  • Create a variable associated with the cursor to receive the returned value.  Set its type to cx_Oracle.NUMBER.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the people data.
  • Execute the statement using bind variables returning the id into new_id.
  • Get the value from new_id and assign it to sandy_id.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the pet data.
  • Execute the statement using bind variables including the sandy_id value.
  • Commit the transaction.
  • Print the sandy_id value. (It’s a float so we use .rstrip(‘.0’) to make it pretty)
  • Prepare a SQL statement using a bind variable
  • Execute the statement using sandy_id for the bind variable.
  • Fetch the results from the cursor into a variable.
  • Print the results with a little decoration text.
When I run this code in my Python session, I see:

Notice the new value, the owner in Sandy’s pets and Sandy’s id in the New Data are all 3 .

Extra Fun 3

3.  Insert Sandy again but return her id and name.

Your results should be:

Notice that (3, ‘Sandy’..) is still there along with our new (4, ‘Sandy’..) but the returned id is 4.  It should return the new id each time you run it.

Answer
 Reset the data

Now is a good time to run reset_data.py.

Insert more than 1 row

As mentioned above, when you want to insert multiple rows, running multiple insert statements is inefficient and makes multiple trips to the database so instead, we will use executemany.

We will perform an insert that adds two records into the cx_people table.  These are the steps performed in the code snippet below.

  • Create an array populated with our data
  • Get a cursor object from our connection.  We will use this cursor to perform our database operations.
  • Set the cursor’s bindarraysize to the number of records in our array.
  • 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.
  • Execute the statement using bind variables.
  • Commit the transaction.
When I run this code in my Python session, I see:

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

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Select (cRud) using cx_Oracle

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

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

Simple query

We will preform a simple query that pulls all of the records in no particular order.  Here are the steps we’ll follow in the code snippet below.

  1. Get a cursor object from our connection.  We will use this cursor to preform our database operations.
  2. Prepare a SQL SELECT statement, specifying the columns desired from the table.
  3. Execute the statement.
  4. Fetch the results into a variable.
  5. Print the results.
When I run this code in my Python session, I see:
Select-01
Extra Fun 1

Modify the statement to order by age.  When you’re done the results should be:

Select-02

Answer
Select specific rows

Now suppose I only want to see the data for Kim. I want, therefore, to restrict the rows returned by the SELECT. This is done with a WHERE clause. There are several ways to do this.

We could just put the where clause in the statement and it would work.

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.

It is possible to simply concatenate the value into the statement.

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:
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:
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:
  1. Get a cursor object from our connection.  We will use this cursor to preform our database operations.
  2. Assign ‘Kim’ to person_name
  3. Prepare an SQL statement using a bind variable
  4. Using the cursor, execute the query using the prepared statement.
  5. Fetch the results from the cursor into a variable.
  6. Print the results.
This will return only the data for Kim:
Select-03
Extra Fun 2

Modify the statement and variable to get the people older than 30.  When you’re done the results should be:

Select-04

Answer

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.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Basic CRUD operations using cx_Oracle

In this series, we’re going to take a look at preforming CRUD (Create Retrieve Update Delete) operations using the cx_Oracle driver.

A good ORM will handle most of your needs.

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.

Martin Fowler said “Mapping to a relational database involves lots of repetitive, boiler-plate code.  A framework that allows me to avoid 80% of that is worthwhile even if it is only 80%.”

When you have enough knowledge to implement direct CRUD operations, you are in a better position to choose the right tool for the right job.

Common Setup

All examples in this series will use the same database objects and connection information.

Creating the Database Objects

The following can be used to setup the initial tables we’ll use.  Please make sure you’re connected to a schema in which you can safely execute commands like these.

 Making the Connection
  1. Import the cx_Oracle driver.
  2. Import os module used to read the environment variable.
  3. Get the connection string from the environment variable.
  4. Create the connection object.

We will include this code section with all examples and use the “con” connection object “con” throughout the series.

Cleanup

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

Guide to cx_Oracle CRUD Series

Initial Setup
Create records
Retrieve records
Update records
Delete records

Using Environment Variables for Database Credentials

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

or on Windows

Then in Python using the os package

Or in Ruby using ENV

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

Leave me a comment if you have any questions or suggestions.

Problem loading Python driver

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

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.

Off to a slow start

<Excuses> Due to typical work priorities and the fact that I’m still settling into a new position, I didn’t make as much progress as I had hoped last week.  I’m almost certain there was minimal procrastination. </Excuses>

I have to admit, I wish I would have started learning Python a few years back.  The language is very intuitive and the way it’s structured just seems to mesh with the way I think (so far.)

I ran through most of the learnpython.org sections.  I like the way the tutorials flow and the code runner at the bottom is a nice feature.

There were a few times when the code runner didn’t work.  I would hit the run button and not get anything back, no errors no output at all.  If you see that problem try doing a shift+refresh in your browser (kind of a soft cache clear) and if that doesn’t work, hard clearing your cache should.  Once I had to close and re-open my browser.

Usually when I write code and hit something I don’t know/remember, like most people, I google the answer.  So I tend to do the same thing when running tutorials rather than going back and re-reading.  I try to learn something the same way I know I’m going to use it.  I’m a firm believer in “You play like you practice.” This is also helpful in finding tools you can use later.  For example tutorialspoint.com is now in my bookmarks.

Next up is getting my build environment setup.  I’ve installed Oracle Database 11g Express Edition to get started and for most of the quick things.  When I get up to the more advanced features,  I will be running Oracle Database 12c both on VirtualBox and Oracle Cloud.

I’m still trying to pick an editor/IDE.  I don’t plan to spend much time covering IDE features unless I come across something extra good or bad.  I’ll try a couple and settle down quickly.

I plan to just jump right into writing a simple application backed by a database and see how that goes.  I will try to post my code to give everyone a good laugh.

 

Diving into Python

I’m going to start learning Python.  I’ve wanted to pick up Python for a while now and it seems like a great place to dig in.

I’m starting at the very beginning (Howdy World) with the goal of developing a few tutorials to showcase the cx_Oracle driver using some of the newest Oracle Database 12c features.

At this stage, I’m collecting a resource list for getting started tutorials and a decent IDE.   I prefer hands-on tutorials over videos, mainly because I like to copy, paste and break the example code.   But I also like videos, especially when I feel like trying to type fast and keep up with the presenter.

If you have any suggestions for tutorials or IDEs, just drop it in a comment and I’ll give them a shot.