Formatting Python Strings for SQL Queries


One of the trickiest aspects of Python, especially for beginners, is string formatting, particularly building SQL statements for use in tools like Select_analysis or as a query parameter to MakeFeatureLayer. The combination of single- and double-quotes, along with using variables for field names and values, is enough to trip up the most experienced of programmers. Here’s a tip to make it a bit easier.

I find the easiest way to create dynamic SQL query strings in Python is to use the string formatting syntax (official documentation; clearer tutorial). As a quick overview, it means you can do something like:

variable_name = "I am a variable"
print "This string has a variable coming up next: %s" % variable_name

which prints

"This string has a variable coming up next: I am a variable"

So that’s exactly the same as saying

"This string has a variable coming up next: " + variable_name

Except it is a little more formal, and a little more clear in certain circumstances. In particular when you’re constructing long strings out of many variables, it’s a little easier to handle, e.g.

var_1 + " " + var_2 + " some static text " + var_3 + "!"

becomes

"%s %s some static text %s!"%(var_1,var_2,var_3)

It’s particularly handy for SQL queries which involve all that nasty '"'+var_1+'"'+"'"+val_1+"'" business! So in the most basic case you’d say

sql_query = '"%s" = \'%s\'' % (field_name,value_to_select)

Which, while still not easy on the eye, is a little more intelligible. Note the escaping-out of the single quotes around the %s because we used single quotes to define the string. You could just as easily use

sql_query = "\"%s\" = '%s'" % (field_name,value_to_select)

Up to you. You can expand the concept as needed to involve as many fields and variables as you need, and the result is basically always more readable than any combination of quotes, plusses, spaces, and backslashes!

There are more advanced methods for string formatting in Python, too; the link at the top is a good place to start.

It’s also worth noting that as of Python 2.6 (used in ArcGIS 10.0), the method of string formatting discussed in this post is technically old hat. If you’re using ArcGIS 10.0 and/or Python 2.6 or greater, you should be using the new str.format() method detailed here. To quote the documentation:

This method of string formatting is the new standard in Python 3.0, and should be preferred to the % formatting … in new code.

However, it’s not supported in Python versions <= 2.5, so if you’re in an ArcGIS 9.3.1 environment you’ll need to use the % formatting as detailed in this post. The % formatting will work just fine in 10.0, and will still work in code run under ArcGIS 10.1, which uses Python 2.7.

Thom M

One thought on “Formatting Python Strings for SQL Queries

  1. christi parks

    Hello all,I am new and I would like to ask that what are the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance…
    would really appreciate help… and Also i would like to thank for all the information you are providing on sql.

    Reply

Got something to say?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s