0

I'm apparently struggling to establish a connection to a paradox database with sqlalchemy, since the dialect seems not to be featured... Yeah I know paradox is outdated, but I need to get it working since my boss runs a own petrol station which is paradox backed. I got it to work with pypyodbc, which wasn't that much of a struggle since I was into VBA for a couple of years now and things were not that strange to start with. Switching to Python made my life much easier with etl pipelines...

At this point I'm trying to source data from multiple source for business reporting, where I can apply one module only for etl purposes. Hopefully some of you guys can reach out with some useful information concerning this matter.

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
OctoBuzz
  • 1
  • Why would you be able to use only one module? – Alan Feb 06 '18 at 12:22
  • By that I meant that I would like to use for example sqlalchemy only instead of combining it with pypyodbc or dialect-specific modules (or libraries) :) – OctoBuzz Feb 06 '18 at 13:30
  • So it's a preference? I'm sorry to say that you're out of luck. SQLalchemy doesn't support paradox. There is an easier library than pypyodbc (https://pypi.python.org/pypi/pypxlib/1.6). You'll need to create a staging environment to merge your datasets together. For example, copy each data source into a sqlite database and then you can play around with one source of data. – Alan Feb 06 '18 at 13:47
  • Thought it would be possible to customize or even bypass with odbc... well then I'll have to take the long way. Thanks @Alan – OctoBuzz Feb 06 '18 at 19:42

1 Answers1

0

You might be able to do it. One method of accessing SQL Server is through PyODBC, since you have an ODBC driver for Paradox the same process might work. It's been over a decade since I've used Paradox, but IIRC the ODBC drivers work the same. It might be more work than you want to do though.

You would need to extend SQLalchemy with a new engine. This is the details of the PyODBC driver for SQL Server; you'd need to change the appropriate details, so instead of "mssql+pyodbc you'd have paradox+pyodbc.

This is a similar StackOverflow question which is a good starting point.

If you're passing through ODBC strings, this is an example of an ODBC string for SQL Server.

params = urllib.quote_plus("DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password")

and an example of a Paradox connection string (apparently not entirely correct but it explains how to adapt it):

cnxn = pyodbc.connect(r"Driver={{Microsoft Paradox Driver (*.db )}};Fil=Paradox 5.X;DefaultDir={0};Dbq={0}; CollatingSequence=ASCII;")

I no longer have a Paradox database to hand to test this on, so I can't say for sure how much work it would take to get going. If you already have a working ODBC connection then you have half of what you need.

Alan
  • 2,914
  • 2
  • 14
  • 26