python | Nicolas Cadou's Blog

Hi there. Long time no post.

I just spent a couple hours debugging a query with a DISTINCT ON clause. The DISTINCT ON clause is a PostgreSQL-specific extension to the standard DISTINCT clause, which lets you specify the columns on which you want PostgreSQL to operate for filtering out duplicate rows.

With the SQLAlchemy ORM (using the declarative extension,) here is one way to write a query to retrieve all unique names from a name table:

>>> q = DBSession.query(model.Name).distinct(model.Name.value) \
                 .order_by(model.Name.value)

Now, if you’d want to see what kind of SQL query the ORM will generate you’d print it like so:

>>> print str(q)
SELECT DISTINCT name.id AS name_id, name.value AS name_value
FROM name ORDER BY name.value

But as you can see, there’s no DISTINCT ON here. Dang. Continue reading Printing actual SQLAlchemy queries →

Sometimes things are simple

So we’ve got that awesome Hudson server, checking out our projects at each commit, running all the tests and reporting on the outcome. That way we always know when something broke, what, and which committer just fell out of his Ballmer peak.

On that server we install as little stuff as possible and any non-Python dependency required for compiling python packages at install time is handled by buildout. That way we can keep our Hudson environmentÂ as clean as possible, preventing version conflicts and cruft accumulation.

For instance, the buildout for a project depending on GeoIP-Python contains the following snippet. It downloads and compiles GeoIP in a directory private to the project, then uses it when installing GeoIP-Python:

[geoip-python]
recipe = zc.recipe.egg:custom
eggs = GeoIP-Python
include-dirs = parts/geoip/include
library-dirs = parts/geoip/lib

[geoip]
recipe = zc.recipe.cmmi
url = http://geolite.maxmind.com/download/geoip/api/c/GeoIP-1.4.6.tar.gz

Sometimes not

Enter pyzmq.

You would think that the following would work, but it doesn’t:

[pyzmq]
recipe = zc.recipe.egg:custom
egg = pyzmq
include-dirs = parts/zeromq/include
library-dirs = parts/zeromq/lib

[zeromq]
recipe = zc.recipe.cmmi
url = http://download.zeromq.org/zeromq-2.1.6.tar.gz

The solution

The pyzmq build scripts do things differently, and they override the provided directories for various reasons. This is kind of annoying, but I can live with it, as we can get the desired result by settings a few environment variables:

[pyzmq-env]
ZMQ_DIR = ${buildout:parts-directory}/zeromq
LIBRARY_PATH = ${buildout:parts-directory}/zeromq/lib
LD_LIBRARY_PATH = ${buildout:parts-directory}/zeromq/lib

[pyzmq]
recipe = zc.recipe.egg:custom
egg = pyzmq
environment = pyzmq-env

[zeromq]
recipe = zc.recipe.cmmi
url = http://download.zeromq.org/zeromq-2.1.6.tar.gz

The catch

Beware that in either case, the declaration of buildout:parts needs to have the above parts at the beginning. Order matters here, as it obviously does no good to install pyzmq (which could very well happen when buildout runs setup.py develop on the project) before zeromq is compiled.

This is mainly annoying because the project has a hudson-specific buildout file that contains these compiling parts, as developers use another buildout so they can use their local system libraries and not have to compile everything. This means that the bulk of buildout:parts has to be duplicated in both files as the += operator would otherwise add the additional parts at the end, thus breaking the build.

I’ve not yet found a way to insert parts at the beginning.

That’s it, happy testing!

Nicolas Cadou's Blog

Stuff. Sometimes.

Tag Archives: python

Printing actual SQLAlchemy queries

Continuous integration on a zc.buildout-managed project with pyzmq

Sometimes things are simple

Sometimes not

The solution

The catch

On taking action