SQLALCHEMY conncection to Sybase Adaptive Server Anywhere Version 7 via TCI/IP

Trainer Go

unread,

Apr 12, 2022, 11:10:40 AM4/12/22

to sqlalchemy

im a bit lost and need some help.

im trying to set up a database connection with sqlalchemy to a Sybase Adaptive Server Anywhere Version 7 and i dont know how.

I would be really happy if somebody could help me.

Thanks in advace.

Greetings Mae

Jonathan Vanasco

unread,

Apr 13, 2022, 6:07:06 PM4/13/22

to sqlalchemy

The Sybase dialect was deprecated from first-party support by SQLAlchemy and is currently unsupported.

Gord Thompson, who is a frequent contributor to the core SQLAlchemy project, and has generously taken over responsibility for the original dialect as a third-party dialect::

https://github.com/gordthompson/sqlalchemy-sybase

In addition to offering some excellent code, his repo offers a wiki and some documentation - both of which should help.

Trainer Go

unread,

Apr 14, 2022, 3:15:36 AM4/14/22

to sqlalchemy

I will try it today.

Thank you very much for your help Jonathan Vanasco

Trainer Go

unread,

Apr 14, 2022, 5:35:52 AM4/14/22

to sqlalchemy

i tried to connect my database but im getting an InterfaceError and i dont know how so solve it.

connection_string = (
"DRIVER=Adaptive Server Anywhere 7.0;"
"SERVER=IP;"
"PORT=Port;"
"UID=ID;PWD=PASSWORD;"
"DATABASE=NameOfDatabase;"
"charset=utf8;"
)
connection_url = URL.create(
"sybase+pyodbc",
query={"odbc_connect": connection_string}
)
engine = create_engine(connection_url)

conn = engine.connect()

InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben (0) (SQLDriverConnect)')

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/14/rvf5)

i have installed the driver on my computer and its called Adaptive Server Anywhere 7.0 so i dont know where the problem is...

Jonathan Vanasco schrieb am Donnerstag, 14. April 2022 um 00:07:06 UTC+2:

Gord Thompson

unread,

Apr 14, 2022, 12:30:44 PM4/14/22

to sqlalchemy

> Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben

"The data source name was not found and no default driver was specified"

Use

import pyodbc

print(pyodbc.drivers())

to view the names of the ODBC drivers that are available to your application.

Jonathan Vanasco

unread,

Apr 14, 2022, 2:06:41 PM4/14/22

to sqlalchemy

thanks, gord!

Trainer Go

unread,

Apr 20, 2022, 4:21:32 AM4/20/22

to sqlalchemy

Hi Gord Thompson,

with print pyodbc.drivers() result = ['SQL Server', 'Microsoft Access Driver (*.mdb, *.accdb)', 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)', 'Microsoft Access Text Driver (*.txt, *.csv)']

but i think i know why i have problems with the connection. Cause i tried to connect with my 64 Bit Python programm a 32 Bit driver and the 32 Bit driver isnt listed in my pyodbc print 64 Bit

Gord Thompson

unread,

Apr 20, 2022, 11:18:08 AM4/20/22

to sqlalchemy

Yes, that's correct. If you are running 64-bit Python then you need to have a 64-bit version of the ODBC driver installed. 64-bit applications cannot use 32-bit drivers.

Trainer Go

unread,

Apr 21, 2022, 4:21:36 AM4/21/22

to sqlalchemy

Now im getting this error:

pyodbc.OperationalError: ('08001', '[08001] [Sybase][ODBC Driver][Adaptive Server Anywhere]Verbindung mit dem Datenbankserver unmöglich: Datenbankserver läuft nicht (-100) (SQLDriverConnect); [08001] [Sybase][ODBC Driver]Ungültiges Attribut für Verbindungszeichenfolge (0); [08001] [Sybase][ODBC Driver]Ungültiges Attribut für Verbindungszeichenfolge (0); [08001] [Sybase][ODBC Driver]Ungültiges Attribut für Verbindungszeichenfolge (0); [08001] [Sybase][ODBC Driver]Ungültiges Attribut für Verbindungszeichenfolge (0); [08001] [Sybase][ODBC Driver]Ungültiges Attribut für Verbindungszeichenfolge (0)')

Maybe a problem with my Connection String?

connection_string = (
"DRIVER=Adaptive Server Anywhere 7.0;"
"SERVER=IP;"
"PORT=Port;"

"UID=ID;PWD=password;"
"DATABASE=NameOfDB;"

"charset=utf8;"
)
connection_url = URL.create(
"sybase+pyodbc",
query={"odbc_connect": connection_string}
)
engine = create_engine(connection_url)

Gord Thompson

unread,

Apr 21, 2022, 8:29:19 AM4/21/22

to sqlalchemy

> Verbindung mit dem Datenbankserver unmöglich: Datenbankserver läuft nicht

"Unable to connect to database server: Database server is not running"

> Ungültiges Attribut für Verbindungszeichenfolge

"Invalid connection string attribute"

Check the documentation for your ODBC driver to verify that you are using the correct attribute names in your connection string.

Gord Thompson

unread,

Apr 21, 2022, 8:37:00 AM4/21/22

to sqlalchemy

Perhaps this might help:

https://www.connectionstrings.com/asa-odbc/

Note also that the sqlalchemy-sybase dialect has not been tested with the "Adaptive Server Anywhere" driver. If its behaviour differs significantly from the "SAP ASE ODBC driver" then you may have issues with that.

Trainer Go

unread,

Apr 21, 2022, 9:04:18 AM4/21/22

to sqlalchemy

Hello Gord Thompson,

Yeah i found this dialect documentation and its working with the pyodbc connection. and my query works with pd.read_sql() but i want to test it with sqlalchemy cause im getting an warning that i should use sqlalchemy. But thats what i wanted to do.

cnxn = pyodbc.connect('DRIVER={Adaptive Server Anywhere 7.0};Commlinks=tcpip{ip=MyIpAdresse;Port=MyPort};ENG=MyDBName;UID=MyUserID;PWD=MyPassword')

this works fine but atm cant handle to connect my db with sqlalchemy:

engine = create_engine('DRIVER={Adaptive Server Anywhere 7.0};Commlinks=tcpip{ip=IP;Port=Port};ENG=Database;UID=ID;PWD=Password')
cnxn = engine.connect()

Error:

Traceback (most recent call last):

File "<string>", line 2, in create_engine
File "C:\Users\User\AppData\Local\Programs\Python\Python39-32\lib\site-packages\sqlalchemy\util\deprecations.py", line 309, in warned
return fn(*args, **kwargs)
File "C:\Users\User\AppData\Local\Programs\Python\Python39-32\lib\site-packages\sqlalchemy\engine\create.py", line 530, in create_engine
u = _url.make_url(url)
File "C:\Users\User\AppData\Local\Programs\Python\Python39-32\lib\site-packages\sqlalchemy\engine\url.py", line 731, in make_url
return _parse_rfc1738_args(name_or_url)
File "C:\Users\User\AppData\Local\Programs\Python\Python39-32\lib\site-packages\sqlalchemy\engine\url.py", line 792, in _parse_rfc1738_args
raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Could not parse rfc1738 URL from string 'DRIVER={Adaptive Server Anywhere 7.0};Commlinks=tcpip{ip=IP;Port=Port};ENG=Database;UID=ID;PWD=Password'

Thanks in advance

Gord Thompson

unread,

Apr 21, 2022, 7:20:46 PM4/21/22

to sqlalchemy

create_engine() does not directly accept a raw ODBC connection string. You need to use the URL.create(…, query={"odbc_connect": …) approach that you were using in your earlier examples, except that now you will be supplying a valid connection string for the ODBC driver that you are using.

Trainer Go

unread,

Apr 22, 2022, 4:22:46 AM4/22/22

to sqlalchemy

Thank you Gord Thompson,

its working. Now i have to check how i can make a select with better performance.

cause the select im using in my python programm needs i think 80 seconds and in the database its self only 13 seconds but it tested it with pd.read_sql() . Maybe the performance with sqlalchemy is much better.