Is there a way to make the old school TopSpeed (.TPS) database work in Python?
Basically, I want to convert a data aggregation C# app that turns various data from AS/400 DB2 and some .TPS files from a mapped drive, into a Python web app. The DB2 part has already been taken care of.
I’ve been thinking that this can be done via PyODBC & SQLalchemy, but I don’t know anything about this database type. What is the connection string? How can connect a mapped drive into a Ubuntu container? Is there a Linux driver for this or does unixODBC work? I have no idea.
Anything can help. Thank you.
Here is the connection string used for the C# app.
Advertisement
Answer
Answering my question for the future me and others.
The TopSpeed Driver is only available on 16-bit and 32-bit Windows. Using the 32bit Python and 32bit Driver should work.
Unfortunately, based on my extensive research, there is no Linux driver available, and the driver is NOT FREE. I will not put the driver’s website here for legal reasons, though a quick Google search should show you the way to buy it.
Furthermore, there is no dialect for sqlalchemy yet, and I don’t think it will.
For my purpose I’m using pyodbc to connect shown below:
TPS_CONNECTION_STRING = (
'Driver={Topspeed ODBC Driver (Read-Only)};'
'DBQ=X:\;'
'Extension=tps;'
)
TPS_DB_CONNECTION =pyodbc.connect(TPS_CONNECTION_STRING, autocommit=True)
Where DBQ is the directory or location of the .tps files. Then connect and execute queries with
TPS_DB_CURSOR = TPS_DB_CONNECTION.cursor()
res = TPS_DB_CURSOR.execute('SELECT * FROM FILE')
TPS_DB_CURSOR.close()
As we are using a pyodbc connection, we can also use the pandas read_sql function to get the results back into a DataFrame immediately. Just pass the connection object; in this example, it will be TPS_DB_CONNECTION