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?
On Friday, June 3, 2016 at 10:21:18 AM UTC-5, Mike Bayer wrote:
>
>
>
> 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?
>
>
>
>
>
> >
> > 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()
> > >>> 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\pool.py",
> > line 285, in <lambda>
> > return lambda crec: creator()
> > File "<stdin>", line 2, in creator
> > 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\pool.py",
> > line 285, in <lambda>
> > return lambda crec: creator()
> > File "<stdin>", line 2, in creator
> > sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('IM002', '[IM002]
> > [Microsoft][ODBC Driver Manager] Data source name not found and no
> defa
> > ult driver specified (0) (SQLDriverConnect)')
> > >>>
> >
> >
> >
> > On Friday, June 3, 2016 at 9:23:48 AM UTC-5, Mike Bayer wrote:
> >
> >
> > 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
> > <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://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>
> > >
> > <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>
> > >
> > <http://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)')
> > > >>>
> > >
> > > --
> > > 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+unsubscr...@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>.
> > > 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 <javascript:>
> > <mailto:sqlalchemy+unsubscr...@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.
> > For more options, visit 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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
