sqlalchemy + pyodbc + sybase issue with automap

Brad Hudson

unread,

Jun 3, 2016, 9:19:18 AM6/3/16

to sqlalchemy

Can anyone help with forming a proper connection to Sybase ASE 15.7? I have tried variations of the following unsuccessfully using examples from the following URLs:

Code:

Python 3.5.1 |Continuum Analytics, Inc.| (default, Feb 16 2016, 09:49:46) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> from sqlalchemy.ext.automap import automap_base
>>> from sqlalchemy.orm import Session
>>> from sqlalchemy import create_engine
>>> Base = automap_base()
>>> cxnstr = 'Driver={Adaptive Server Enterprise};server=myserver.url.com;port=8985;db=MyDB;uid=sa;pwd=restriced'
>>> engine = create_engine('sybase+pyodbc:///?odbc_connect={}'.format(cxnstr))
>>> Base.prepare(engine, reflect=True)
Traceback (most recent call last):
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 1044, in _do_get
return self._pool.get(wait, self._timeout)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\util\queue.py", line 145, in get
raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\engine\base.py", line 2074, in _wrap_pool_connect
return fn()
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 318, in unique_connection
return _ConnectionFairy._checkout(self)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 713, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 480, in checkout
rec = pool._do_get()
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 1060, in _do_get
self._dec_overflow()
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
raise value
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 1057, in _do_get
return self._create_connection()
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 323, in _create_connection
return _ConnectionRecord(self)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 449, in __init__
self.connection = self.__connect()
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 607, in __connect
connection = self.__pool._invoke_creator(self)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\engine\strategies.py", line 97, in connect
return dialect.connect(*cargs, **cparams)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\engine\default.py", line 385, in connect
return self.dbapi.connect(*cargs, **cparams)
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQ
LDriverConnect)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\ext\automap.py", line 743, in prepare
autoload_replace=False
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\sql\schema.py", line 3602, in reflect
with bind.connect() as conn:
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\engine\base.py", line 2018, in connect
return self._connection_cls(self, **kwargs)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\engine\base.py", line 72, in __init__
if connection is not None else engine.raw_connection()
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\engine\base.py", line 2104, in raw_connection
self.pool.unique_connection, _connection)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\engine\base.py", line 2078, in _wrap_pool_connect
e, dialect, self)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\engine\base.py", line 1405, in _handle_dbapi_exception_noconnectio
n
exc_info
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\util\compat.py", line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\util\compat.py", line 185, in reraise
raise value.with_traceback(tb)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\engine\base.py", line 2074, in _wrap_pool_connect
return fn()
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 318, in unique_connection
return _ConnectionFairy._checkout(self)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 713, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 480, in checkout
rec = pool._do_get()
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 1060, in _do_get
self._dec_overflow()
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
raise value
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 1057, in _do_get
return self._create_connection()
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 323, in _create_connection
return _ConnectionRecord(self)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 449, in __init__
self.connection = self.__connect()
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 607, in __connect
connection = self.__pool._invoke_creator(self)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\engine\strategies.py", line 97, in connect
return dialect.connect(*cargs, **cparams)
File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\engine\default.py", line 385, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no defa
ult driver specified (0) (SQLDriverConnect)')
>>>

Mike Bayer

unread,

Jun 3, 2016, 10:23:48 AM6/3/16

to sqlal...@googlegroups.com

this is supported by just passing all key/value pairs:

>>> from sqlalchemy.dialects.sybase import pyodbc
>>> dialect = pyodbc.SybaseDialect_pyodbc()
>>> from sqlalchemy.engine import url
>>> u =
url.make_url("sybase+pyodbc:///?user=myuser&password=mypwd&host=my.db.server&database=mydb&driver=FreeTDS&port=1433&TDS_Version=8.0")
>>> dialect.create_connect_args(u)
[['DRIVER={FreeTDS};Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd;TDS_Version=8.0;port=1433'],
{}]

also, you can always bypass the URL process entirely using creator:

import pyodbc

def creator():
return pyodbc.connect(<any parameters you want>)

engine = create_engine("sybase+pyodbc://", creator=creator)

finally, ODBC is intended to be used with data source names (DSN)s so
the best approach would be to use one, FreeTDS supports these directly:

http://www.freetds.org/userguide/odbcinionly.htm

when you make an ODBC DSN, now you can use tsql to test your DSNs.

On 06/03/2016 09:19 AM, Brad Hudson wrote:
> Can anyone help with forming a proper connection to Sybase ASE 15.7? I
> have tried variations of the following unsuccessfully using examples
> from the following URLs:
>

> * http://stackoverflow.com/questions/4493614/sqlalchemy-equivalent-of-pyodbc-connect-string-using-freetds
> <http://stackoverflow.com/questions/4493614/sqlalchemy-equivalent-of-pyodbc-connect-string-using-freetds>
> * http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html

> <http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html> (Basic
> Use)
>
> Code:
>
> Python 3.5.1 |Continuum Analytics, Inc.| (default, Feb 16 2016,
> 09:49:46) [MSC v.1900 64 bit (AMD64)] on win32
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import pyodbc
> >>> from sqlalchemy.ext.automap import automap_base
> >>> from sqlalchemy.orm import Session
> >>> from sqlalchemy import create_engine
> >>> Base = automap_base()
> >>> cxnstr = 'Driver={Adaptive Server
> Enterprise};server=myserver.url.com

> <http://myserver.url.com/>;port=8985;db=MyDB;uid=sa;pwd=restriced'

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

Brad Hudson

unread,

Jun 3, 2016, 11:15:02 AM6/3/16

to sqlalchemy

Mike:

First, thank you very much for the response. However, it doesn't appear to be an issue with the connection string, but with the automap_base() function itself. Please see my modified results below:

Python 3.5.1 |Continuum Analytics, Inc.| (default, Feb 16 2016, 09:49:46) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> from sqlalchemy.ext.automap import automap_base
>>> from sqlalchemy.orm import Session
>>> from sqlalchemy import create_engine

>>> def creator():
... return pyodbc.connect('driver={Adaptive Server Enterprise};server=myurl.url.com;port=8985;database=MyDB;user=sa;password=pass')
...

>>> engine = create_engine("sybase+pyodbc://", creator=creator)

>>> Base = automap_base()

File "C:\brad\anaconda\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 285, in <lambda>
return lambda crec: creator()
File "<stdin>", line 2, in creator

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

Mike Bayer

unread,

Jun 3, 2016, 11:21:18 AM6/3/16

to sqlal...@googlegroups.com

On 06/03/2016 11:15 AM, Brad Hudson wrote:
> Mike:
>
> First, thank you very much for the response. However, it doesn't appear
> to be an issue with the connection string, but with the automap_base()
> function itself. Please see my modified results below:

this is a simple connection failure. AutomapBase has no connectivity
features within it and it is merely calling upon the Engine being passed
for connectivity.

create your engine, then do this:

conn = engine.connect()

conn.execute("SELECT 1")

that succeeds ?

From the error message, it looks like the pyodbc connection just isn't
finding a host. Confirm your pyodbc.connect() by itself also succeeds?

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com

> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.

> > Visit this group at https://groups.google.com/group/sqlalchemy

> <https://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.

Brad Hudson

unread,

Jun 3, 2016, 2:40:49 PM6/3/16

to sqlalchemy

Thanks for that, it definitely fails on the connect, but I don't understand why it's failing and complaining about a

[Microsoft][ODBC Driver Manager]

issue when I've tried both FreeTDS and Adaptive Server Enterprise?

> > <mailto:sqlalchemy+unsub...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

Message has been deleted

Brad Hudson

unread,

Jun 3, 2016, 4:49:43 PM6/3/16

to sqlalchemy

I'm thoroughly confused. It seems that the connection is complaining about a "[Microsoft][ODBC Driver Manager]" issue when my DSN is clearly using the "Adaptive Server Enterprise" driver.

Code:

Python 3.5.1 |Continuum Analytics, Inc.| (default, Feb 16 2016, 09:49:46) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc

>>> import sqlalchemy
>>> def odbc_conn():
... return pyodbc.connect('DSN=MyDSN;UID=my_user;PWD=my_pwd')
...
>>> eng = sqlalchemy.create_engine('sybase+pyodbc://', creator=odbc_conn(), echo=True)

Traceback (most recent call last):
File "<stdin>", line 1, in <module>

File "<stdin>", line 2, in odbc_conn
pyodbc.Error: ('IM014', '[IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Dr
iver and Application (0) (SQLDriverConnect)')
>>>

> > <mailto:sqlalchemy+unsub...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

Mike Bayer

unread,

Jun 3, 2016, 5:09:50 PM6/3/16

to sqlal...@googlegroups.com

you have a FreeTDS problem at this point and you need to contact them at
http://lists.ibiblio.org/mailman/listinfo/freetds for further help.

On 06/03/2016 04:49 PM, Brad Hudson wrote:
> I'm thoroughly confused. It seems that the connection is complaining
> about a "[Microsoft][ODBC Driver Manager]" issue when my DSN is clearly
> using the "Adaptive Server Enterprise" driver.
>

> <https://lh3.googleusercontent.com/-eZTUkmqUiFY/V1HtJfYD8vI/AAAAAAAAAq4/5jcXsHAOaE0FJIT-RXrHjik_J5_1vXvTwCLcB/s1600/driver.jpg>

> <http://myurl.url.com>;port=8985;database=MyDB;user=sa;password=pass')

> <http://myserver.url.com> <http://myserver.url.com>

> > > <mailto:sqlalchemy+...@googlegroups.com
> <javascript:> <javascript:>>.

> > > To post to this group, send email to sqlal...@googlegroups.com
> > <javascript:>
> > > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > > Visit this group at
> https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>
> > <https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>>.
> > > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>
> > <https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>>.
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it,
> send

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.

Brad Hudson

unread,

Jun 7, 2016, 1:34:04 PM6/7/16

to sqlalchemy

I have fixed my original issue which turned out to be that python (64-bit) was trying to call a non-existant 64-bit driver and choking on the 32-bit version. I re-installed anaconda 3.4 (32-bit) and now have a different issue. Can someone help with this one?

Python 3.5.1 |Continuum Analytics, Inc.| (default, Mar 4 2016, 15:28:01) [MSC v.1900 32 bit (Intel)] on win32

Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> from sqlalchemy.ext.automap import automap_base
>>> from sqlalchemy.orm import Session
>>> from sqlalchemy import create_engine
>>>
>>> Base = automap_base()

>>> def creator():
... return pyodbc.connect('driver={Adaptive Server Enterprise};dsn=SYBDSN;uid=user;pwd=passw;server=mhost.domain.com;port=8001')
...
>>> eng = create_engine('sybase+pyodbc://', creator=creator())
>>> print(eng.echo)
None
>>> eng.echo = True
>>> print(eng.echo)
True
>>> Base.prepare(eng, reflect=True)

Traceback (most recent call last):

File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 1044, in _do_get
return self._pool.get(wait, self._timeout)
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\util\queue.py", line 145, in get

raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

File "<stdin>", line 1, in <module>

File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\ext\automap.py", line 743, in prepare
autoload_replace=False
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\sql\schema.py", line 3602, in reflect
with bind.connect() as conn:
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\engine\base.py", line 2018, in connect
return self._connection_cls(self, **kwargs)
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\engine\base.py", line 72, in __init__

if connection is not None else engine.raw_connection()

File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\engine\base.py", line 2104, in raw_connection
self.pool.unique_connection, _connection)
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\engine\base.py", line 2074, in _wrap_pool_connect
return fn()
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 318, in unique_connection
return _ConnectionFairy._checkout(self)
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 713, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 480, in checkout
rec = pool._do_get()
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 1060, in _do_get
self._dec_overflow()
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
raise value
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 1057, in _do_get
return self._create_connection()
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 323, in _create_connection
return _ConnectionRecord(self)
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 449, in __init__
self.connection = self.__connect()
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 607, in __connect
connection = self.__pool._invoke_creator(self)
File "C:\brad\Anaconda3\envs\db-app\lib\site-packages\sqlalchemy\pool.py", line 270, in <lambda>
return lambda crec: creator()
TypeError: 'pyodbc.Connection' object is not callable
>>>

Mike Bayer

unread,

Jun 7, 2016, 1:57:37 PM6/7/16

to sqlal...@googlegroups.com

yes, don't call creator(), just pass the callable

create_engine("...", creator=creator)

Reply all

Reply to author

Forward

sqlalchemy + pyodbc + sybase issue with automap_base().prepare()

Brad Hudson

Mike Bayer

Brad Hudson

Mike Bayer

Brad Hudson

Brad Hudson

Mike Bayer

Brad Hudson

Mike Bayer