Handling null values in a SQLAlchemy query - equivalent of isnull, nullif or coalesce

SQLAlchemy allows you to use the equivalent of SQL's COALESCE function to handle NULL values. I didn't find the documentation for this easy to understand, so here's a quick tutorial...


Depending on which database management system you're familiar with, you might have used ISNULL, NULLIF or COALESCE in a query to return a specific value in case the database column contains NULL.

My example concerns a PostgreSQL database with a table named CONFIG. I want to find a record that was least recently processed, according to its LAST_PROCESSED_AT column, or was never processed, indicated by LAST_PROCESSED_AT = NULL.

Here's the result of the query

select name, last_processed_at from config:

Note that the third row has a null value for LAST_PROCESSED_AT. Here's a revised query using SQL's coalesce function to map NULLs to the earliest date recognized by PostgreSQL:

select name, coalesce(last_processed_at, to_timestamp(0)) from config

Note the effect on the third row:

Armed with COALESCE, I can find the record I'm looking for with this query:

select name from config order by coalesce(last_processed_at, to_timestamp(0)) limit 1

The question is how to do the same thing with Python and SQLAlchemy. And the answer is this:

import datetime

from sqlalchemy.sql.functions import coalesce

my_config = session.query(Config).order_by(coalesce(Config.last_processed_at, datetime.date.min)).first()

Python Flask - passing JSON to a template in a variable

I ran into a problem in my Python Flask app. I wanted to store some JSON-formatted data in a Python variable, and use JQuery's parseJSON function to consume that data inside a Jinja template. When I tried to parse the JSON in the template, a JavaScript error resulted. I figured out why, and how to work around it.

By the way, the same problem occurs, and the same workaround applies, whether you pass the variable in the call to render_template, or use a session variable.

Here's a copy of my first attempt at the code...

Python:

def index():
        return render_template('viewer_type.html', myVar='{"abc": "123", "def": "456"}')

JavaScript:

<script src='jquery-1.10.0.min.js></script>
<script>
        var myVar = "{{ myVar }}";
        var myJSON = $.parseJSON(myVar);
        prompt("abc", myJSON.abc);
</script>

This failed. prompt never executed, and Chrome's JavaScript console reported this error.

Uncaught SyntaxError: Unexpected token &

I realized that the contents of the variable were being encoded when Flask passed them to the template. Adding this line of JavaScript...

prompt("myVar", myVar);

... revealed that my JSON had turned into:

{"abc": "123", "def": "456"}

Thanks to this helpful post on stackoverflow, I found a convenient way to decode the encoded text in JavaScript. I encapsulated that in a function:

function decodeJSON(encodedJSON) {
            var decodedJSON = $('<div/>').html(encodedJSON).text();
            return $.parseJSON(decodedJSON);
}

Then I just needed to amend my original JavaScript to call decodeJSON, so the whole thing looks like this:

<script src='jquery-1.10.0.min.js></script>
<script>
 function decodeJSON(encodedJSON) {
  var decodedJSON = $('<div/>').html(encodedJSON).text();
  return $.parseJSON(decodedJSON);
 }

 var myVar = '{{ myVar }}';
 prompt('abc', decodeJSON(myVar).abc);
</script>

Success! The expected output, 123, was displayed, and there were no more errors in the JavaScript console.

Iptables settings can interfere with apt-get update

I ran into a problem on my Ubuntu 12.04.3 box today. I issued the command sudo apt-get update. This usually works without a hitch, but today, it obtained a few of the updates successfully but gave 404 errors for many others. Here's an example:

Err http://security.ubuntu.com precise-security/main Sources
  404  Not Found [IP: 91.189.92.200 80]


After a little thought, I realized how this Ubuntu box differed from others I had worked on: I had used iptables to facilitate running Apache Tomcat on ports 80 and 443. (Because these port numbers are below 1024, Tomcat would have to run as a privileged user to access them, and iptables provides a well-known workaround.)

The particular iptables rule that caused the problem was this one:

sudo iptables -t nat -I OUTPUT -p tcp --dport 80 -j REDIRECT --to-ports 8080


By temporarily deleting that rule (which can be done by replacing -I with -D in the above command), I got sudo apt-get update to work.

Running Tomcat 7 on port 80 on Ubuntu 12.04.3

The goal:

• Commission a new Amazon EC2 Instance running Ubuntu 12.04.3.
• Install Apache Tomcat 7 and everything Tomcat requires to run.
• Make Tomcat respond to requests on port 80.

How this differs from other tutorials you might have encountered:

• It's for Ubuntu, not some other flavor of Linux where you can just set AUTHBIND=yes in an /etc/defaults/tomcat7 file.
• It's for Tomcat 7, not some earlier version.
• It doesn't just tell you to edit Tomcat's server.xml file, ignoring the fact that Ubuntu won't let a non-privileged user bind to ports below 1024.
• It doesn't suggest running Tomcat as root, ignoring any resulting security concerns.

How to do it:

• Create a new AWS EC2 Instance.
• Select the AMI Ubuntu Server 12.04.3 LTS, 64-bit.
• Use or create a security group that enables (at least) ports 22 and 80 for your IP address.
• Use an SSH client to connect to the Instance. Most of the remaining steps will be performed in the SSH client.
• Install Tomcat 7: 

wget http://apache.mirrors.lucidnetworks.net/tomcat/tomcat-7/v7.0.50/bin/apache-tomcat-7.0.50.tar.gz

tar -xzvf apache-tomcat-7.0.50.tar.gz

rm apache-tomcat-7.0.50.tar.gz

export CATALINA_HOME=/home/ubuntu/apache-tomcat-7.0.50

export CATALINA_BASE=$CATALINA_HOME

• Install JRE 7. Unfortunately, Oracle requires you to click to accept a license agreement, which you can't do from a headless server. So use another computer to visit http://www.oracle.com/technetwork/java/javase/downloads/server-jre7-downloads-1931105.html, download server-jre-7u51-linux-x64.tar.gz. Find a way to get this file to your home directory on the EC2 Instance. One possibility is to install an FTP or SFTP server on the Instance. Once the file is in your home directory:

cd ~ tar -xzvf /sftp/stevetest/incoming/server-jre-7u51-linux-x64.gz

export JAVA_HOME=/home/ubuntu/jdk1.7.0_51  

• Use iptables to redirect requests on port 80 to port 8080

sudo iptables -t nat -I PREROUTING -p tcp --dport 80 -j REDIRECT --to-ports 8080

sudo iptables -t nat -I OUTPUT -p tcp --dport 80 -j REDIRECT --to-ports 8080

• Start Tomcat:

cd ~/apache-tomcat-7.0.50/bin ./startup.sh

• Now if you type the EC2 Instance's IP address into your browser's address bar, you should see the Tomcat welcome page. No need to specify port 8080!

A tip of the hat goes to Tomcat: The Definitive Guide by Jason Brittain and Ian F. Darwin for a clear explanation of how to use iptables.

In future installments, I hope to cover setting up the server to automatically export the environment variables and start Tomcat when booted, and to enable SSL on port 443.

Modern technology frees desk space

At home, I use a low-cost computer as a basic file and print server. Family members store files such as photos and schoolwork in a shared folder, which is automatically backed up by Carbonite. And an all-in-one printer/scanner is attached via a USB cable for everyone's use. I realize nearly the same thing could be accomplished by using cloud storage and a wireless printer, but my old-fashioned solution is convenient and inexpensive.

For almost ten years, an obsolete desktop computer filled this role in my house. Its slow CPU and minimal RAM didn't have much of a negative effect, since copying small files and printing small documents aren't very taxing. But it sure took up a lot of desk space!

What finally motivated me to replace this old workhorse was when the hard disk, which had a capacity of only 80 GB, got full. After a few days of searching, I found a suitable used laptop for sale on eBay. It's an Acer Aspire V5 with a 11.6-inch screen, 1.5 GHz Celeron dual-core processor, 4 GB RAM, 500 GB hard drive and Windows 8.

With shipping, the cost was around $165.

I was worried that transferring all my files and software to the new computer would be an onerous chore, but it ended up going smoothly and only took a few hours. The steps were:

• Connect to my home wireless network. Windows 8 automatically detected the network, prompted for the key, and connected without a hitch.
• Create a homegroup and a shared folder with read/write access.
• Download and install the printer driver.
• Copy all the files from the old computer.
• Log in to my Carbonite account and transfer the backup to the new computer.

So far, everything is working well. The screen and keyboard are tiny, but since I'll almost never use them, that's actually an advantage. Look at all the desk space I gained!

Java operator precedence can cause unexpected output

My Java program needed to determine whether a particular variable was null. I wasn't getting the expected results, so I added a System.out.println call to print some diagnostic output to the console. The diagnostic output wasn't what I expected, either. It turned out to have to do with operator precedence in Java. Here's some example code that illustrates the problem:

public class Example {
 public static void main(String[] args) {
  Integer n = 1;
  System.out.println("Is n null? " + n == null);
 }
}

I expected the output to be: Is n null? false. Instead, it was just the single word false. Here's why:

The additive operator + has higher precedence than the equality operator ==. So Java was evaluating the string concatenation

"Is n null? " + n

and then comparing the result to null. The result of that comparison was the boolean value false, which is what was displayed. This was easily fixed with the addition of a couple of parentheses:

System.out.println("Is n null? " + (n == null));

Interestingly, if I'd been comparing n to some integer rather than to null, the compiler would have caught the problem for me. This code

public class Example {
 public static void main(String[] args) {
  Integer n = 1;
  System.out.println("Is n one? " + n == 1);
 }
}

causes a compile-time error, Incompatible operand types String and int.

The moral of the story is, whatever language you're programming in, be aware of operator precedence!

Two Spring MVC tutorial projects - Spring 4, Eclipse, Tomcat, multiple models, MultipartFile

Today I present two "hello world"-type Java EE projects using Spring MVC. I've been getting up to speed with Spring for a while now. I've taken advantage of several helpful tutorials, but I found all of them lacking in one way or another.

First, although there are quite a few Spring MVC "hello world" tutorials, I couldn't find one that accommodated all of my requirements, so I created the springmvcshell project. It's based on mkyong's excellent Spring 3 MVC Hello World Example, which I modified to:

• Use the Maven standard directory structure
• Work with the Eclipse IDE
• Work with Tomcat
• Use Spring 4 rather than an earlier version
• Use Java 1.7 rather than an earlier version

Second, I wanted to create an MVC view and corresponding controller method that include fields from two models plus a multipart file upload in a single form. Again, I found several relevant tutorials, but none that met all my needs. So I created the springmvcgoodness project, which provides all the features of springmvcshell, plus:

• Uses a single form to read and write fields from two separate classes
• Includes a file upload control using Spring's MultipartFile interface

The README files in the springmvcshell and springmvcgoodness repos explain how to install and run the two projects. You can of course see all the source code in those two repos. The remainder of this post discusses a few interesting points about the code and settings.

First of all, the Maven pom.xml files for the springmvcshell and springmvcgoodness projects are of critical importance. Both pom.xml's include the dependencies for Spring 4.0.0, as well as a reference to the com.springsource.repository.maven.snapshot repository that's required for Maven to find these dependencies. The springmvcgoodness pom.xml also adds dependencies for  the Java Server Pages Standard Tag Library (JSTL), the Java Persistence API, and Apache Commons FileUpload.

Next, the project settings are critical to making these projects work with Eclipse and Tomcat. If you clone my springmvcshell and springmvcgoodness repos, you'll automatically have the correct settings, which are stored in the .classpath and .project files. However, it's worth understanding some of the details.

• I converted these projects to faceted form (right-click the project and select Configure | Convert to Faceted Form) and added the Dynamic Web Module facet version 3.0. This makes the projects usable as servlets.
• The Java Build Path is key to avoiding compile-time errors due to missing dependencies. I had to add some libraries to the build path (right-click the project and select Build Path | Configure Build Path, then click the Add Library button). I had to add the EAR Libraries. To eliminate some compiler warnings, I also removed the old version 1.5 JRE System Library and replaced it with the JRE System Library [jre7].
• The Deployment Assembly settings are key to successful deploying on Tomcat and are accessed by right-clicking the project, selecting Properties, and then clicking Deployment Assembly. It's critical that the Maven Dependencies be added here if not already present.

Finally, here are a few points about the springmvcgoodness project:

• The approach I took to including fields from two models in one form was to create a wrapper class containing an instance of each model class.
• Once you have such a wrapper class, this view shows how to set the commandName and path attributes to refer to fields of the two model classes.
• Keys to making the file upload work include:
• Adding an HTML input tag with type="file" in the view.
• Adding a CommonsMultipartResolver bean to the servlet configuration XML file.
• Including a parameter like @RequestParam("myfile") MultipartFile myFile in the controller method.

I hope you'll find these example Spring MVC projects helpful!