David Underhill

PostgreSQL does not yet support the UPSERT command (though it is on their Todo list). If you have a row you want to update (if it already exists in the database) or insert (if it doesn’t exist yet), then PostgreSQL unfortunately makes you implement the logic yourself. Other popular databases like SQLite (INSERT OR IGNORE) and MySQL (ON DUPLICATE KEY UPDATE) both support upserting. I haven’t run across a generic PL/pgSQL function which can do this, but you could write a trigger (like this one) for each table where this functionality is needed.

Unfortunately, this is a bit of a pain if you want to use UPSERT on many tables, so I wrote a Python method which takes care of the UPSERT logic generically. To use it, you call it with a cursor connected to your database, the schema and table name, a list of primary key field names, and the key-value pairs for each field.

For example, let’s say you have a table which tracks scores (and only the last score counts):

CREATE TABLE MySchema.Scores (
    user_id integer PRIMARY KEY,
    score   integer NOT NULL
);

To UPSERT a row into this table you would:

db_conn = psycopg2.connect("...")
db_cur = db_conn.cursor()
upsert(db_cur, 'Scores', ('user_id',), schema='MySchema', user_id=..., score=...)
db_conn.commit()

Here’s the code for the Python-based upsert method:

David Underhill Python PostgreSQL, Python

App Engine’s URL Fetch API supports fetching URLs asynchronously.  However, a request handler may only simultaneously fetch up to 10 URLs.  To fetch more than 10, it must wait for one to finish before starting another. This is a little tricky to do efficiently*, so I put together a Python module which takes care of the details.  The module provides an AsyncURLFetchManager class with a simple interface – just tell it what URLs you want and it fetches them as quickly as possible.  This interface also simplifies the starting of an asynchronous request into a single method call:

fetch_asynchronously(url)

You can also pass fetch_asynchronously() any arguments which urlfetch.make_fetch_call() accepts (e.g., method, payload). You can also ask it for a callback which will conveniently include the RPC object (which contains the results) as well as any other positional or keyword arguments you would like.

At the end of your request, just call wait() to ensures that any pending fetches and their callbacks are completed prior to the request handler terminating.

* Unfortunately, App Engine does not currently provide select() or any other non-blocking mechanism which can check if an RPC has completed.  Once it does, this implementation could be improved to ensure that it only waits on an RPC which has already completed (currently we just have to wait on the oldest one – this is sub-optimal since later RPCs may actually finish first).

David Underhill Google App Engine asynchronous, Google App Engine, Python, urlfetch

You may have some functionality on your app engine site that you want to protect from robots and prevent users from executing too frequently. For example, perhaps users can leave comments but you only want them to be able to leave a comment every N seconds – faster than that and the “user” is either a bot or is not using the system as intended.

One way to discourage this behavior is to limit how often a user can take a certain action to a fixed rate. I’ve created a RateLimiter class which handles the logic of tracking how quickly a user is making requests, and determines when your code (optionally) should challenge the user with a captcha before allowing them to continue. If you simply want to rate limit the user’s requests, you can ignore the captcha business and just return an error to the user whenever they exceed the allowed rate.

The source is available at http://gist.github.com/437051 (including the optional captcha handling code).

Example Usage:
The example code below shows a rate limiter which allows a user to interact with a particular page once every 2 seconds. It also gives the user 3 “tokens” which allows the user to violate this limit by up to 3 requests. Tokens are consumed if a user makes a request within 2 seconds of the previous request. Tokens are returned if the user if the user slows down, or if the user solves a captcha.

This example is written as if the request is expected to be made via JavaScript on your page. The client-side JavaScript would check the response for the 'captcha-show' text and prompt the user with a captcha if that test was present. When the captcha is answered, another AJAX call would be made to send the user’s response to the CaptchaHandler class in rate_limit.py. You are free to integrate the captcha challenge however you like. Just call RateLimiter.captcha_solved() or RateLimiter.rate_limit(uid, captcha_solved=True) when the user meets your challenge (it doesn’t even have to be a captcha).

David Underhill Google App Engine bot, captcha, Google App Engine, Python, rate limit

The Google App Engine infrastructure provides many services, but sessions is not one of them. There are several Python-based session middlewares which already do this so I considered them first (spoiler: I ended up writing my own and it is orders of magnitudes faster than the alternatives: gae-sessions).

Beaker is a solid implementation, but it lacks support for memcache on app engine. This means every request must go to the datastore to fetch session data – yuck.

gaeutilities is designed for app engine and takes advantage of both memcache and the datastore. Unfortunately, the code is a bit heavyweight – it is coupled to unrelated functionality (e.g., “flash” messaging) and it is complicated by support for options I do not need (e.g., cookie-only sessions and automatic token rotation). Most significantly, its performance suffers from excess API calls and inefficient model storage.

Since I was unsatisfied with these options, I wrote my own sessions middleware, gae-sessions. It strives to be lightweight, fast (but reliable), secure, and easy to use. I ended up with a pretty small library (200 lines of code) which met these goals. It uses memcache (for speed) and the datastore (for reliability) but only reads and writes when it must. db.Model objects are efficiently stored by converting them to protobufs instead of using the automatic pickling functionality (which is slow since app engine lacks cPickle).

Consider gae-sessions if you need sessions support for a Python web application hosted on Google’s app engine. The project includes demo code which you can run without modification on the app engine development server. The demo shows gae-sessions working with an OpenID-based authentication system powered by RPX (check it out to see how easy it is to integrate with RPX).

Update: I’ve created an in-depth comparison page which compares both the features and performance of alternative sessions libraries (beaker, geautilities, gmemsess, and suas) with gae-sessions.

David Underhill Google App Engine app-engine, gae-sessions, OpenID, Python, sessions

I love Python‘s logging module. I use it all the time to log a wide variety of information — messages to help me debug as well as informative messages for the user. Though you can toggle which messages you want to be printed, if the Python interpreter encounters a logging method call it still creates the string for the log message (the argument to the method) (sadly there Python doesn’t have lazy evaluation like Haskell). If creating this string is expensive, then your application’s performance may suffer. Unfortunately, there is no Python preprocessor (like C’s cpp … though preprocess might be able to do it) so it is difficult to automatically remove a large number of logging statements prior to running an application in a production environment.

The best solution I’ve seen is to prefix logging statements with if __debug__: so that they are optimized away by python -O (see this post on StackOverflow). I like it, but it unfortunately requires this statement to be prefixed to every logging statement I don’t want in a production environment. That’s a lot of ugly extra code and it isn’t easy to change which statements it applies to either.

I decided to write a script which automatically parses a Python file and replaces logging statements of a particular level with a pass statement and a commented out copy of the logging code. It can also do the reverse operation. It has some limitations (see the code, or run the script with the --help option), but it should work for most Python files. I used it for the VNS project and it successfully operated on every file in the project. It also improved performance dramatically – the maximum throughput of the VNS simulator increased by 25%! In comparison, running the code with Psyco only garnered a 6% improvement (though pretty substantial for the minimal 13 lines I had to add to take advantage of it).

I think this script is worth using before running your code in a production environment if you are a heavy user of the logging module like I am. You can find the code here (it is hosted on Siafoo, a neat site for sharing code). Here’s the latest version of the code:

David Underhill Coding, Python comment, logging, performance, preprocess, Python, script

Twisted is an awesome event-driven networking engine. Unfortunately, it does not have good support for interfacing with raw sockets (unlike its support for many network protocols, which is amazing). Anyway, I recently needed to work with raw sockets so I had to find a way to make it work with Twisted. Though Twisted does have a module (twisted.pair) which tries to provide some support for raw sockets, the module is poorly documented and requires a library which is not readily available.

Luckily, I stumbled on a module which works on top of the libpcap packet capture library called pcapy. It is simple to use, and thread-safe — and easy to integrate into a Twisted-based project.

I put together a short sample (see below) which shows how to capture raw packets alongside the main Twisted event loop. It would be trivial to extend this example to also write to a raw socket (using an ordinary Python socket). This example can also be downloaded here.

# This sample shows how to run a libpcap-based packet sniffer concurrently with
# the Twisted framework.  The Twisted component is an "Echo" TCP server
# (listening on port 9999) which prints everything it receives.  When a client
# connects, it starts the pcap thread.  When the pcap thread receives a packet,
# it sends a message to the client telling it the size of the received packet.
# Finally, when the client disconnects the program is terminated.
 
# To try this contrived example out, run this script as root (so that it can use
# pcap) and then connect to the echo server (e.g., telnet localhost 9999).  Note
# that the pcap parameters are hard-coded.  This code uses twisted 8.0.2 and
# pcapy-0.10.4.
 
import os
 
from pcapy import open_live
from twisted.internet.protocol import Protocol, Factory
from twisted.internet import reactor
 
# pcap settings
DEV          = 'eth0'  # interface to listen on
MAX_LEN      = 1514    # max size of packet to capture
PROMISCUOUS  = 1       # promiscuous mode?
READ_TIMEOUT = 100     # in milliseconds
PCAP_FILTER  = ''      # empty => get everything (or we could use a BPF filter)
MAX_PKTS     = -1      # number of packets to capture; -1 => no limit
 
def run_pcap(f):
    # the method which will be called when a packet is captured
    def ph(hdr, data):
        print 'pcap heard: when=%s sz=%dB' % (hdr.getts(), len(data))
        # thread safety: call from the main twisted event loop
        reactor.callFromThread(f, len(data))
 
    # start the packet capture
    p = open_live(DEV, MAX_LEN, PROMISCUOUS, READ_TIMEOUT)
    p.setfilter(PCAP_FILTER)
    print "Listening on %s: net=%s, mask=%s" % (DEV, p.getnet(), p.getmask())
    p.loop(MAX_PKTS, ph)
 
# a silly echo server which prints what it receives and sends info about the
# size of each packet captured on DEV
class Echo(Protocol):
    def connectionLost(self, reason):
        os._exit(0) # kill the whole process
 
    def connectionMade(self):
        # run pcap in another thread (it will run forever)
        reactor.callInThread(run_pcap, self.pcapDataReceived)
 
    def dataReceived(self, data):
        print 'echo got: %s' % data
 
    def pcapDataReceived(self, sz):
        self.transport.write('pcap got: %uB\n' % sz)
 
# starts the silly echo server on port 9999
def main():
    factory = Factory()
    factory.protocol = Echo
    reactor.listenTCP(9999, factory)
    reactor.run()
 
if __name__ == "__main__":
    main()

David Underhill Coding, Python pcap, pcapy, Python, raw socket, sniffer, twisted

It seems like I often have a need to work with a simple TCP protocol whose messages have a header which starts with the length of the message and an integer representing the message type (OpenFlow is one of many such protocols). To save myself the trouble of creating and debugging a very similar custom implementation each time I have this need, I decided to package it as a simple Python framework which does this for me. It is based on the event-driven Twised networking engine. Using this simple extension on top of Twisted has a number of benefits:

1. Automatic handling of the length and type fields when sending and receiving messages.
2. Automatic unpacking of messages based on type.
3. Client automatically tries to reconnect if the connection is lost.
4. Server can handle any number of clients simultaneously.

You can view the official package on the PyPi website here. My local page for the package is here — please view it for an example on how to use this package.

David Underhill Coding, Python, WordPress client, protocol, Python, server, tcp, twisted