Tag Archives: tox

Testing Python and PostgreSQL on Windows, Part 6

Alliterative locales, languages, collations.

A tox on all your houses (test combinations).

The last item to fix in the Pyrseas unit tests so that they run on Windows is related to the PostgreSQL 9.1 COLLATION feature. When creating the tests, I was influenced by the examples in the documentation, i.e., I created a collation with ‘fr_FR.utf8′ LC_COLLATE and LC_CTYPE. On Linux, it’s fairly straightforward to add such a locale to your system (although perhaps Windows users may disagree :-)), so the tests worked as expected.

On Windows, however, most collation tests failed with

DataError: could not create locale "fr_FR.utf8": No error

Unfortunately, the PG documentation doesn’t seem to provide any hints on what is the Windows equivalent of ‘fr_FR.utf8′ (or similar Linux locales). Eventually I figured it out by looking at the output of \l (list databases) in psql. This showed the Collation and Ctype (in my case) where ‘English_United States.1252′ so I assumed what was needed was ‘French.France.1252′. Here is the procedure to set that up:

Open the Control Panel, select Date, Time, Language, and Regional Options, then Regional and Language Options (or Add other languages), click on the Advanced tab in the dialog and then choose “French (France)” from the dropdown. Finally, click OK and respond to any subsequent prompts to install the locale, including rebooting the machine.

Aside: For comparison, on Debian Linux, the equivalent procedure involves running sudo dpkg-reconfigure locales, selecting fr_FR.UTF-8 UTF-8 from a list, accepting the default locale and waiting for the locales to be generated (no reboot necessary). Second aside: On Linux, you can deselect a locale to remove it from your system, but Windows doesn’t appear to allow for language removals.

To test, make sure you have the latest Pyrseas code from GitHub, which includes a change to fix the COLLATION tests to run on Windows.

Finally, we’re ready to install Tox and run all the unit tests with a single command. First, run pip install tox under both Python 2.7 and 3.2. Next, define (set) the environment variables PG84_PORT, PG90_PORT, PG91_PORT, and PG92_PORT to point to the corresponding PostgreSQL ports.

Then simply invoke tox from the Python 2.7 environment. Thanks to the Pyrseas tox.ini, this will install Python 2.7 and 3.2 virtualenvs, under a .tox subdirectory in the Pyrseas tree, install Psycopg2, PyYAML and Pyrseas into each virtualenv and run the unit tests eight times, once for each combination of Python and PostgreSQL.

If you have been following along, the only test failure should be in test_extension.py, in test_map_lang_extension, when attempting to CREATE EXTENSION plperl, due to the missing PERL514.DLL (see previous post). The error will only occur under PG 9.1 and 9.2.

The only problem I noticed with tox is that when there are errors it may get confused in its summary report.

___________________________________ summary ___________________________________
  py27pg90: commands succeeded
  py27pg91: commands succeeded
  py27pg92: commands succeeded
ERROR:   py32pg91: commands failed
  py27pg84: commands succeeded
ERROR:   py32pg84: commands failed
ERROR:   py32pg90: commands failed
ERROR:   py32pg92: commands failed

The errors actually occurred in the *pg91 and *pg92 environments but tox reports that all py32* tests failed, which was not the case. This is a minor issue considering all that tox accomplishes, with very little setup or configuration.

Testing Python and PostgreSQL on Windows, Part 3

As a commenter mentioned in response to Part 2, an alternative to using pip install psycopg2, which requires that you first install VC++ 2008 Express, is to download and install the Windows port, aka win-psycopg. Jason Erickson makes these builds available for several versions of Python for both 32- and 64-bit Windows.

If you’re not planning to use virtualenvs or tox (which creates virtualenvs for you), then the win-psycopg installer is the easiest way to satisfy the psycopg2 dependency (in fact, that’s how I started after the initial failure with pip). Simply download the Python 2.7 and 3.2 installers, run them and you’re done. However, the installers don’t work in a virtualenv (there is a workaround to extract the files, but I didn’t explore it because I wanted to use tox).

Another option is to build psycopg2 with the MinGW compiler. Daniele Varrazzo has a post describing this. Daniele used a special MinGW package, but I chose to install the latest (mingw-get-inst-20120426.exe) from MinGW.org (click on the Navigation – Downloads link which will take you to SourceForge).

To get the psycopg2 sources, you can download the .tar.gz package, or, since you have Git, do this from Git Bash:

git clone git://luna.dndg.it/public/psycopg2.git
cd psycopg2
git checkout 2_4_5  # or latest tag

Create a pydistutils.cfg file in your home directory (%USERPROFILE%) with the following (an alternative is to use the --compiler option to the python setup.py command below):


Make sure MinGW, Python, and PostgreSQL are in your PATH, e.g., set PATH=C:\MinGW\bin;C:\Python27;C:\Program Files\PostgreSQL\8.4\bin;%PATH%, and then run:

python setup.py build_ext build

With the latest MinGW and unless Python bug 12641 has been resolved, you’ll see the following error message:

cc1.exe: error: unrecognized command line option '-mno-cygwin'

The workaround is to edit the cygwincompiler.py file in your Python Lib\distutils directory and remove all instances of -mno-cygwin. Hopefully after that, the python setup.py above will work and then you can run the following to install it:

python setup.py install

Rinse and repeat for the Python 3.2 version and you should be ready to test connecting from both Python versions to PostgreSQL.

Aside: To remove the pip-installed psycopg2, you run pip uninstall psycopg2 from the corresponding Python environment. To remove win-psycopg, you use Control Panel’s Add or Remove Programs and click Remove on the desired version. To remove the versions installed with MinGW, I’m afraid you’ll have to resort to deleting the Lib\site-packages\psycopg2 directories and the related psycopg2-*.egg-info files.

Testing Python and PostgreSQL on Multiple Platforms

I’m working on making the Pyrseas functional tests portable enough so that they can be submitted to the repository.

Until now, these tests —which exercise dbtoyaml and yamltodb directly— existed as Linux shell scripts. Briefly, each test runs both dbtoyaml and pg_dump -s on a source database creating YAML and SQL dump outputs, respectively. Then it runs yamltodb on a second dabase to recreate the source tables, etc., and finishes by comparing the first pg_dump ouput to that from the target database to verify that all database objects are present and identical.

The Pyrseas unit tests now use tox which makes it fairly easy to add new platforms. For example, on the Python side, the tox.ini configuration includes 2.7 and 3.2, using a single virtualenv for each version.  It would be easy to add 2.6 or 3.3 (when that is released or from a 3.3.0 rc1 install). To test against Postgres 8.4, 9.0, 9.1 and recently 9.2rc1, the only requirement is to define environment variables PG(84|90|91|92)_PORT with the port numbers used for those Postgres installations. Then tox takes care of running the tests eight times, using each Python/Postgres combination.

Although I have not yet personally run the unit tests on Windows or Linux/Unix variants other than Debian, I believe the tox setup should be quite portable (assuming multiple Postgres installations can be present on a given platform), since the tests only depend on Python and psycopg2 being able to connect to Postgres, i.e., they do not depend on running any PG utilities from the command line.

For the functional tests, running the Pyrseas utilities can be done in a fairly portable way thanks to the os.path, tempfile and subprocess modules. Even the diffing of the pg_dump output can be implemented without having to worry about the presence of a diff command, e.g., on Windows.

However, executing pg_dump against multiple Postgres clusters is not so easy. On Debian (and presumably Ubuntu and all other Debian derivatives), if installed from Debian packages, Postgres utilities can be invoked, for example, as

$ pg_dump --cluster 9.1/main -s pyrseas_testdb

The --cluster option causes the correct executable, e.g., /usr/lib/postgresql/9.1/bin/pg_dump to be run and using the correct port. This translates to Python as:

subprocess.call(['pg_dump', '--cluster 9.1/main', '-s',
                targdump, TEST_DBNAME])

The second element in the list could be provided programmatically to run the tests against various Postgres versions, but it would only work on Debian, Ubuntu, etc. (it also assumes the default cluster installation location).

For Red Hat variants, *BSD or Windows, the only solution I could come up with is requiring the existence of a shell script or .bat file with a set name, e.g., pg_dumpXX, where XX is the PG version number, somewhere along the PATH, to point to the right executable. That is not ideal so I’d appreciate hearing from others who may have dealt with similar issues.