Accessing Paradox Databases in Python

For my day-job, I get to interface with all sorts of different systems, some of which employ legacy database technologies. Two systems I’ve recently worked with are Powergold Music Scheduling and Tecom – and they both happen to use the Paradox Database.

If you haven’t heard of Paradox before, consider yourself lucky! It’s a pretty old piece of technology, released for DOS in 1985 and Windows in 1993. It is characterised by file extensions such as .DB, .PX, .VAL, .X02, and .Y02. For much of it’s popular life, it was sold by Borland. Microsoft Access pretty much killed it off. If you’re interested in the format, you can read an unofficial spec document (it’s not that complicated by today’s standards). These days you don’t see many new programs built with Paradox, but there’s quite a few older products that still rely on it.

I’ve had some instances where I need to programatically pull data out of these Paradox databases. This is generally because we want to continue using the existing software, but extend the functionality in some way. For example, I’ve got some custom software which extracts the Categories and other metadata from Powergold and sends it to OnAirHope and Zetta.

Generally, I only consider it safe to read data from these systems – not write. This is because I have no way of ensuring data integrity and consistency if different systems are all writing to the one database.

Browsing and Exporting Paradox Databases on Windows

If you just want to do a once-off data dump from a Paradox Database, consider using the excellent Paradox dBase Reader from Sportamok Software. This free Windows program will allow you to read pretty much any Paradox database, and doesn’t rely on external drivers or libraries (so there’s no need to get the Borland Database Engine – BDE – working on your system).

This Sportamok program also has a command-line version which allows you to export data automatically. I can imagine it’s not hard to use this to automatically export the data to a CSV, and perhaps use this CSV as a datasource in Microsoft Access (where it’s easy to query offline). You could also easily convert it to SQLite, MySQL, or other modern databases.

Another option for scriptable access is to use Bertrand Bordage’s Python implementation, which is simple and performs well. However, I’ve found this implementation doesn’t pull all records from the specific versions of Paradox that I was using.

ODBC Driver and Paradox

For my particular needs, I’ve settled on another method to query these Paradox databases – using the good ol’ ODBC drivers in Windows. It’s quite a simple solution, really!

I’ve found using the official Borland ODBC drivers bundled with your software is the best route. Windows contains its own Paradox drivers, but I’ve found these to be a little buggy.

If you have the Borland Database Engine installed already on your PC, then you probably have the correct drivers. The installer seems to overwrite the Microsoft-supplied drivers.

If you don’t have the Borland Database Engine, you’re going to need to find it. There are some copies floating around the internet, but I can’t vouch for them.

Paradox, ODBC and Python

For my needs, I’ve created a simple script which:

  1. Copies the Paradox database to a temporary directory
  2. Removes all existing lock files
  3. Creates a connection using PyPyODBC
  4. Allows you to run arbitrary SQL Queries on this copy of the database

The script is freely available on GitHub as a simple Gist:

There’s no need to setup an ODBC Data Source on your Windows Computer – all the necessary parameters are included in the ODBC Connection String.

This code can be used as a starting-point for querying your Paradox databases. Obviously you’ll need to modify this to contain your own SQL commands.

A word about UAC…

Depending on your configuration of User Account Control (UAC), and your configured location of the PDOXUSRS.NET file, you may not be able to run this script. This is because Paradox needs to write a lock file, and the default location is C:\ (now unwritable by non-privilaged users).

The location of your PDOXUSRS.NET file can be changed in the BDE Administrator tool. Change the “Net Dir” folder to somewhere you are able to write unencumbered.

Or just elevate this Python script to Administrator via UAC…

Get the Broadcast Technology Newsletter

Sign up for the email newsletter about media and technology. Sent irregularly. No spam.

I'm Anthony Eden, and I'm a IT Professional, Broadcast Technician, Software Developer, and Solutions Engineer. I've been working in broadcast media since 2008, and developing software and websites for just as long. Right now, I provide freelance services through Media Realm - in particular, to the media and not-for-profit industries.

Follow Anthony on Twitter: @anthony_eden