Category Archives: Python

Basic CRUD operations using cx_Oracle

In this series, we’re going to take a look at performing 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 clean up 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

In this series, we will learn how to use cx_Oracle connections to create cursor objects that you can use to run SQL (DDL / DML) and PL/SQL procedures and functions in the Oracle Database.

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.