Re: [sqlalchemy] sqlalchemy + pyodbc + sybase issue with automap_base().prepare()

[Mike Bayer]

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:>>.
    > 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+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
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.