Pyodbc to SQLAlchemy connection string for Sage 50

Question

I am trying to switch a pyodbc connection to sqlalchemy engine. My working pyodbc connection is:

con = pyodbc.connect('DSN=SageLine50v23;UID=#####;PWD=#####;')

This is what I've tried.

con = create_engine('pyodbc://'+username+':'+password+'@'+url+'/'+db_name+'?driver=SageLine50v23')

I am trying to connect to my Sage 50 accounting data but just can't work out how to build the connection string. This is where I downloaded the odbc driver https://my.sage.co.uk/public/help/askarticle.aspx?articleid=19136.

I got some orginal help for the pyodbc connection using this website (which is working) https://www.cdata.com/kb/tech/sageuk-odbc-python-linux.rst but would like to use SQLAlchemy for it connection with pandas. Any ideas? Assume the issue is with this part pyodbc://

Answer 1

According to this thread Sage 50 uses MySQL to store its data. However, Sage also provides its own ODBC driver which may or may not use the same SQL dialect as MySQL itself.

SQLAlchemy needs to know which SQL dialect to use, so you could try using the mysql+pyodbc://... prefix for your connection URI. If that doesn't work (presumably because "Sage SQL" is too different from "MySQL SQL") then you may want to ask Sage support if they know of a SQLAlchemy dialect for their product.