Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails
hi there - please direct these requests to the IBM list at: https://groups.google.com/forum/#!forum/ibm_db On Tue, Nov 21, 2017 at 2:25 PM, nahumcastrowrote: > Hello all. > > I have the same problem with db2 for as400, seems to be very different from > db2 in windows, linux. > > Here is what I have found: > > this string dont apply for as400 as documented: > ibm_db_sa://user:pass@server:port/database > because when you connect to an as400 there is only one database with a bunch > of schems. > It should be: > ibm_db_sa+pyodbc://user:password@ server:446/*local > *local are the equivalent to a database. > Now the problem is that you need a license to connect to the server or you > will get: > > SQL1598N Ha fallado un intento de conectar con el servidor de bases de > datos debido a un problema de licencia. > The text is in spanish but says: buy a license to connect to the server. > > > > > On Friday, February 12, 2016 at 8:58:39 AM UTC-6, Alex Hall wrote: >> >> Thanks so much for your reply--this really helps! I asked the people >> at work, and was told that my machine does, in fact, have some sort of >> IBM manager installed. (Can you tell I'm new to this technology and >> this job?) Using it, I was able to create a DSN to the test database >> and, it seems, connect. I'm getting an error when I call >> >> dbInspector = inspect(dbEngine) >> >> but at least I'm getting that far. I'll ask about the error in a >> separate thread, since more people are likely to have run across that >> than seem to have experience with the 400 and IBM's wrapper. >> >> On 2/12/16, Michal Petrucha wrote: >> > On Thu, Feb 11, 2016 at 01:16:03PM -0500, Alex Hall wrote: >> >> I've done more research on this topic. There's a lot out there about >> >> using MSSQL with SA, but next to nothing about using ibm_db_sa or >> >> specifying drivers. >> >> >> >> I have pyodbc installed. I downloaded IBM's ODBC zip file, and I've >> >> put db2odbc64.dll in my project folder, but don't know how to point SA >> >> or pyodbc to it. I've tried several versions of >> >> "?driver="db2odbc64.dll"" appended to my connection string, but I keep >> >> getting an error: "data source not found and no default driver >> >> specified". It doesn't even time out anymore, it just errors out >> >> immediately. I've also tried "ibm_db_sa+pyodbc://" to start the >> >> string, but that fails too. >> >> >> >> This *must* be a simple thing, but I can't work out what to do, and >> >> Google is failing me. If anyone has any ideas, I'd greatly appreciate >> >> hearing them. Thanks, and sorry to keep bugging the list about this. I >> >> just have no other options at the moment and I need to get this >> >> working soon. >> > >> > Hi Alex, >> > >> > Unfortunately, I can't offer you any specific help with IBM DB, but >> > judging by the number of replies, it seems nobody on this list can, so >> > I only have some stab-in-the-dark suggestions. >> > >> > In my experience with enterprise software, *nothing* is ever a simple >> > thing, not even seemingly trivial operations, such as connecting to a >> > database. >> > >> > You can try using either pyodbc, or the ibm_db driver – in both cases, >> > those are just the Python DBAPI drivers which take in textual SQL >> > statements, send them to the database in the low-level network >> > protocol, and present the results as dumb Python objects. SQLAlchemy >> > is a layer on top of them. That means, the first step would be to get >> > your Python runtime to open a raw pyodbc, or ibm_db connection to the >> > server, and be able to execute raw SQL statements there. Only after >> > you confirm this works you can move on to getting SQLAlchemy to work >> > with the DBAPI driver. >> > >> > >> > In my understanding, pyodbc is a wrapper around the library unixodbc. >> > I'm not sure how it's implemented on Windows – whether it's a port of >> > unixodbc, or it uses a different ODBC implementation there. Whatever >> > the case, though, on Linux with unixodbc, when I wanted to connect to >> > MS SQL, I had to register a low-level driver with the unixodbc >> > library. I had to edit a system-wide configuration file >> > (/etc/unixODBC/odbcinst.ini), and create a new driver definition in >> > there to make unixodbc recognize the FreeTDS driver I'm using as the >> > low-level protocol implementation. >> > >> > I have no idea what low-level ODBC driver is required to connect to >> > IBM DB, I'm afraid you'll have to figure that out on your own. The >> > official IBM docs at >> > >> > https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/c0007944.html?cp=SSEPGG_9.7.0%2F4-0-4 >> > seem to imply that IBM provides their own low-level ODBC driver which >> > you'll need to have in place in order to be able to connect to the >> > server using ODBC. >> > >> > In any case, I would expect that the ODBC machinery would expect to >> > have the db2odbc64.dll
Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails
Hello all. I have the same problem with db2 for as400, seems to be very different from db2 in windows, linux. Here is what I have found: this string dont apply for as400 as documented: ibm_db_sa://user:pass@server:port/database because when you connect to an as400 there is only one database with a bunch of schems. It should be: ibm_db_sa+pyodbc://user:password@ server:446/*local *local are the equivalent to a database. Now the problem is that you need a license to connect to the server or you will get: SQL1598N Ha fallado un intento de conectar con el servidor de bases de datos debido a un problema de licencia. The text is in spanish but says: buy a license to connect to the server. On Friday, February 12, 2016 at 8:58:39 AM UTC-6, Alex Hall wrote: > > Thanks so much for your reply--this really helps! I asked the people > at work, and was told that my machine does, in fact, have some sort of > IBM manager installed. (Can you tell I'm new to this technology and > this job?) Using it, I was able to create a DSN to the test database > and, it seems, connect. I'm getting an error when I call > > dbInspector = inspect(dbEngine) > > but at least I'm getting that far. I'll ask about the error in a > separate thread, since more people are likely to have run across that > than seem to have experience with the 400 and IBM's wrapper. > > On 2/12/16, Michal Petrucha> wrote: > > On Thu, Feb 11, 2016 at 01:16:03PM -0500, Alex Hall wrote: > >> I've done more research on this topic. There's a lot out there about > >> using MSSQL with SA, but next to nothing about using ibm_db_sa or > >> specifying drivers. > >> > >> I have pyodbc installed. I downloaded IBM's ODBC zip file, and I've > >> put db2odbc64.dll in my project folder, but don't know how to point SA > >> or pyodbc to it. I've tried several versions of > >> "?driver="db2odbc64.dll"" appended to my connection string, but I keep > >> getting an error: "data source not found and no default driver > >> specified". It doesn't even time out anymore, it just errors out > >> immediately. I've also tried "ibm_db_sa+pyodbc://" to start the > >> string, but that fails too. > >> > >> This *must* be a simple thing, but I can't work out what to do, and > >> Google is failing me. If anyone has any ideas, I'd greatly appreciate > >> hearing them. Thanks, and sorry to keep bugging the list about this. I > >> just have no other options at the moment and I need to get this > >> working soon. > > > > Hi Alex, > > > > Unfortunately, I can't offer you any specific help with IBM DB, but > > judging by the number of replies, it seems nobody on this list can, so > > I only have some stab-in-the-dark suggestions. > > > > In my experience with enterprise software, *nothing* is ever a simple > > thing, not even seemingly trivial operations, such as connecting to a > > database. > > > > You can try using either pyodbc, or the ibm_db driver – in both cases, > > those are just the Python DBAPI drivers which take in textual SQL > > statements, send them to the database in the low-level network > > protocol, and present the results as dumb Python objects. SQLAlchemy > > is a layer on top of them. That means, the first step would be to get > > your Python runtime to open a raw pyodbc, or ibm_db connection to the > > server, and be able to execute raw SQL statements there. Only after > > you confirm this works you can move on to getting SQLAlchemy to work > > with the DBAPI driver. > > > > > > In my understanding, pyodbc is a wrapper around the library unixodbc. > > I'm not sure how it's implemented on Windows – whether it's a port of > > unixodbc, or it uses a different ODBC implementation there. Whatever > > the case, though, on Linux with unixodbc, when I wanted to connect to > > MS SQL, I had to register a low-level driver with the unixodbc > > library. I had to edit a system-wide configuration file > > (/etc/unixODBC/odbcinst.ini), and create a new driver definition in > > there to make unixodbc recognize the FreeTDS driver I'm using as the > > low-level protocol implementation. > > > > I have no idea what low-level ODBC driver is required to connect to > > IBM DB, I'm afraid you'll have to figure that out on your own. The > > official IBM docs at > > > https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/c0007944.html?cp=SSEPGG_9.7.0%2F4-0-4 > > > seem to imply that IBM provides their own low-level ODBC driver which > > you'll need to have in place in order to be able to connect to the > > server using ODBC. > > > > In any case, I would expect that the ODBC machinery would expect to > > have the db2odbc64.dll registered somehow with a symbolic name in some > > configuration file, registry, or whatever, and that would be the > > string you're expected to pass as the driver name in the ODBC > > connection string. > > > > Actually, I
Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails
On Thu, Feb 11, 2016 at 01:16:03PM -0500, Alex Hall wrote: > I've done more research on this topic. There's a lot out there about > using MSSQL with SA, but next to nothing about using ibm_db_sa or > specifying drivers. > > I have pyodbc installed. I downloaded IBM's ODBC zip file, and I've > put db2odbc64.dll in my project folder, but don't know how to point SA > or pyodbc to it. I've tried several versions of > "?driver="db2odbc64.dll"" appended to my connection string, but I keep > getting an error: "data source not found and no default driver > specified". It doesn't even time out anymore, it just errors out > immediately. I've also tried "ibm_db_sa+pyodbc://" to start the > string, but that fails too. > > This *must* be a simple thing, but I can't work out what to do, and > Google is failing me. If anyone has any ideas, I'd greatly appreciate > hearing them. Thanks, and sorry to keep bugging the list about this. I > just have no other options at the moment and I need to get this > working soon. Hi Alex, Unfortunately, I can't offer you any specific help with IBM DB, but judging by the number of replies, it seems nobody on this list can, so I only have some stab-in-the-dark suggestions. In my experience with enterprise software, *nothing* is ever a simple thing, not even seemingly trivial operations, such as connecting to a database. You can try using either pyodbc, or the ibm_db driver – in both cases, those are just the Python DBAPI drivers which take in textual SQL statements, send them to the database in the low-level network protocol, and present the results as dumb Python objects. SQLAlchemy is a layer on top of them. That means, the first step would be to get your Python runtime to open a raw pyodbc, or ibm_db connection to the server, and be able to execute raw SQL statements there. Only after you confirm this works you can move on to getting SQLAlchemy to work with the DBAPI driver. In my understanding, pyodbc is a wrapper around the library unixodbc. I'm not sure how it's implemented on Windows – whether it's a port of unixodbc, or it uses a different ODBC implementation there. Whatever the case, though, on Linux with unixodbc, when I wanted to connect to MS SQL, I had to register a low-level driver with the unixodbc library. I had to edit a system-wide configuration file (/etc/unixODBC/odbcinst.ini), and create a new driver definition in there to make unixodbc recognize the FreeTDS driver I'm using as the low-level protocol implementation. I have no idea what low-level ODBC driver is required to connect to IBM DB, I'm afraid you'll have to figure that out on your own. The official IBM docs at https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/c0007944.html?cp=SSEPGG_9.7.0%2F4-0-4 seem to imply that IBM provides their own low-level ODBC driver which you'll need to have in place in order to be able to connect to the server using ODBC. In any case, I would expect that the ODBC machinery would expect to have the db2odbc64.dll registered somehow with a symbolic name in some configuration file, registry, or whatever, and that would be the string you're expected to pass as the driver name in the ODBC connection string. Actually, I think with ODBC, you're expected to define all database servers in a system-wide configuration file or some such, give each one of them a nickname (“DSN”), and just use that to connect to the database. The other option is to use the ibm_db Python DBAPI driver. I expect you have already seen the official docs: https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.dbclient.python.doc/doc/c0054366.html Have you tried following the set-up steps in that section there? Try to first get it into a state where you can connect to the database with ``ibm_db.connect()``, and successfully execute SQL statements from the Python shell. Once you get that to work, you can try to install ibm_db_sa, and try to call ``sqlalchemy.create_engine('ibm_db_sa://user:passw...@host.name.com:5/database')`` If that fails, you still have the option to turn to the official IBM support channels – you're using an expensive enterprise database, which should come with a support contract. The ibm_db drivers seem to be officially supported by IBM, which would mean, if it doesn't work for you, you should be able to request help from IBM to fix it. Good luck! Michal -- 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. signature.asc Description: Digital signature
Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails
Thanks so much for your reply--this really helps! I asked the people at work, and was told that my machine does, in fact, have some sort of IBM manager installed. (Can you tell I'm new to this technology and this job?) Using it, I was able to create a DSN to the test database and, it seems, connect. I'm getting an error when I call dbInspector = inspect(dbEngine) but at least I'm getting that far. I'll ask about the error in a separate thread, since more people are likely to have run across that than seem to have experience with the 400 and IBM's wrapper. On 2/12/16, Michal Petruchawrote: > On Thu, Feb 11, 2016 at 01:16:03PM -0500, Alex Hall wrote: >> I've done more research on this topic. There's a lot out there about >> using MSSQL with SA, but next to nothing about using ibm_db_sa or >> specifying drivers. >> >> I have pyodbc installed. I downloaded IBM's ODBC zip file, and I've >> put db2odbc64.dll in my project folder, but don't know how to point SA >> or pyodbc to it. I've tried several versions of >> "?driver="db2odbc64.dll"" appended to my connection string, but I keep >> getting an error: "data source not found and no default driver >> specified". It doesn't even time out anymore, it just errors out >> immediately. I've also tried "ibm_db_sa+pyodbc://" to start the >> string, but that fails too. >> >> This *must* be a simple thing, but I can't work out what to do, and >> Google is failing me. If anyone has any ideas, I'd greatly appreciate >> hearing them. Thanks, and sorry to keep bugging the list about this. I >> just have no other options at the moment and I need to get this >> working soon. > > Hi Alex, > > Unfortunately, I can't offer you any specific help with IBM DB, but > judging by the number of replies, it seems nobody on this list can, so > I only have some stab-in-the-dark suggestions. > > In my experience with enterprise software, *nothing* is ever a simple > thing, not even seemingly trivial operations, such as connecting to a > database. > > You can try using either pyodbc, or the ibm_db driver – in both cases, > those are just the Python DBAPI drivers which take in textual SQL > statements, send them to the database in the low-level network > protocol, and present the results as dumb Python objects. SQLAlchemy > is a layer on top of them. That means, the first step would be to get > your Python runtime to open a raw pyodbc, or ibm_db connection to the > server, and be able to execute raw SQL statements there. Only after > you confirm this works you can move on to getting SQLAlchemy to work > with the DBAPI driver. > > > In my understanding, pyodbc is a wrapper around the library unixodbc. > I'm not sure how it's implemented on Windows – whether it's a port of > unixodbc, or it uses a different ODBC implementation there. Whatever > the case, though, on Linux with unixodbc, when I wanted to connect to > MS SQL, I had to register a low-level driver with the unixodbc > library. I had to edit a system-wide configuration file > (/etc/unixODBC/odbcinst.ini), and create a new driver definition in > there to make unixodbc recognize the FreeTDS driver I'm using as the > low-level protocol implementation. > > I have no idea what low-level ODBC driver is required to connect to > IBM DB, I'm afraid you'll have to figure that out on your own. The > official IBM docs at > https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/c0007944.html?cp=SSEPGG_9.7.0%2F4-0-4 > seem to imply that IBM provides their own low-level ODBC driver which > you'll need to have in place in order to be able to connect to the > server using ODBC. > > In any case, I would expect that the ODBC machinery would expect to > have the db2odbc64.dll registered somehow with a symbolic name in some > configuration file, registry, or whatever, and that would be the > string you're expected to pass as the driver name in the ODBC > connection string. > > Actually, I think with ODBC, you're expected to define all database > servers in a system-wide configuration file or some such, give each > one of them a nickname (“DSN”), and just use that to connect to the > database. > > > The other option is to use the ibm_db Python DBAPI driver. I expect > you have already seen the official docs: > https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.dbclient.python.doc/doc/c0054366.html > Have you tried following the set-up steps in that section there? Try > to first get it into a state where you can connect to the database > with ``ibm_db.connect()``, and successfully execute SQL statements > from the Python shell. > > Once you get that to work, you can try to install ibm_db_sa, and try > to call > ``sqlalchemy.create_engine('ibm_db_sa://user:passw...@host.name.com:5/database')`` > > If that fails, you still have the option to turn to the official IBM > support channels – you're using an expensive enterprise database, > which should come with a
Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails
I think I'm confused. Isn't Pyodbc an alternative to SQLAlchemy? If not, how would the two work together? I just looked through the 'Getting Started' and 'API' docs for Pyodbc, and I don't see any examples. I found some samples online of people using the two together, but I don't quite follow how the process works. Thanks. On 2/10/16, Jaimy Azlewrote: > Connecting to AS400 from native ibm_db_dbi driver would need db2 connect > which is a separated product from IBM. Use the ibm_db_sa pyodbc driver > instead, or jdbc (jython) if you don't have db2 connect installed on your > machine. > > Salam, > > -Jaimy > On Feb 11, 2016 01:50, "Alex Hall" wrote: > >> Hello list, >> I sent this to the ibm_db list yesterday, but no one has responded >> yet. Since it's as much ibm_db as SA, I thought I'd try here as well >> in case any of you have used an AS400 before. I have ibm_db, >> ibm_db_sa, the latest sqlalchemy, and Python 2.7 (latest) installed. I >> can talk to SQLite with no trouble, it's talking to this 400 that >> won't work. Anyway... >> >> I'm finally ready to hook my app to the 400 instead of the local >> SQLite database I've been using for testing. Here's my simple script: >> >> import globals >> import logging >> from sqlalchemy import * >> from sqlalchemy.ext.declarative import declarative_base >> from sqlalchemy.orm import sessionmaker >> >> #set up the sqlalchemy objects >> dbEngine = create_engine('ibm_db_sa:// >> username:passw...@mysite.com:8471/database') >> Session = sessionmaker(bind = dbEngine) #note that's a capital s on >> Session >> session = Session() #lowercase s >> base = declarative_base() >> >> def getAllTables(): >> dbInspector = inspect(dbEngine) >> for table in dbInspector.get_table_names(): >> print table >> >> getAllTables() >> >> When I run that, it waits thirty seconds or so, then tells me there >> was an error. I'll paste the entire traceback below. Sorry in >> advance--it's pretty long. >> >> Microsoft Windows [Version 6.1.7601] >> Copyright (c) 2009 Microsoft Corporation. All rights reserved. >> >> C:\Users\admin\Dropbox\Autodist\jobs>c:\python27\python.exe >> DBInterface2.py >> Traceback (most recent call last): >> File "DBInterface2.py", line 24, in >> getAllTables() >> File "DBInterface2.py", line 18, in getAllTables >> dbInspector = inspect(dbEngine) >> File "c:\python27\lib\site-packages\sqlalchemy\inspection.py", line 63, >> in ins >> pect >> ret = reg(subject) >> File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", >> line 139 >> , in _insp >> return Inspector.from_engine(bind) >> File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", >> line 135 >> , in from_engine >> return Inspector(bind) >> File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", >> line 109 >> , in __init__ >> bind.connect().close() >> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >> 2018, in >> connect >> return self._connection_cls(self, **kwargs) >> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >> 72, >> in __ >> init__ >> if connection is not None else engine.raw_connection() >> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >> 2104, in >> raw_connection >> self.pool.unique_connection, _connection) >> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >> 2078, in >> _wrap_pool_connect >> e, dialect, self) >> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >> 1405, in >> _handle_dbapi_exception_noconnection >> exc_info >> File "c:\python27\lib\site-packages\sqlalchemy\util\compat.py", line >> 200, in r >> aise_from_cause >> reraise(type(exception), exception, tb=exc_tb) >> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >> 2074, in >> _wrap_pool_connect >> return fn() >> File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 318, in >> unique_c >> onnection >> return _ConnectionFairy._checkout(self) >> File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 713, in >> _checkou >> t >> fairy = _ConnectionRecord.checkout(pool) >> File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 480, in >> checkout >> >> rec = pool._do_get() >> File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 1060, in >> _do_get >> >> self._dec_overflow() >> File "c:\python27\lib\site-packages\sqlalchemy\util\langhelpers.py", >> line 60, >> in __exit__ >> compat.reraise(exc_type, exc_value, exc_tb) >> File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 1057, in >> _do_get >> >> return self._create_connection() >> File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 323, in >> _create_ >> connection >> return _ConnectionRecord(self) >> File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 449, in >> __init__ >> >>
Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails
I've done more research on this topic. There's a lot out there about using MSSQL with SA, but next to nothing about using ibm_db_sa or specifying drivers. I have pyodbc installed. I downloaded IBM's ODBC zip file, and I've put db2odbc64.dll in my project folder, but don't know how to point SA or pyodbc to it. I've tried several versions of "?driver="db2odbc64.dll"" appended to my connection string, but I keep getting an error: "data source not found and no default driver specified". It doesn't even time out anymore, it just errors out immediately. I've also tried "ibm_db_sa+pyodbc://" to start the string, but that fails too. This *must* be a simple thing, but I can't work out what to do, and Google is failing me. If anyone has any ideas, I'd greatly appreciate hearing them. Thanks, and sorry to keep bugging the list about this. I just have no other options at the moment and I need to get this working soon. On 2/11/16, Alex Hallwrote: > I think I'm confused. Isn't Pyodbc an alternative to SQLAlchemy? If > not, how would the two work together? I just looked through the > 'Getting Started' and 'API' docs for Pyodbc, and I don't see any > examples. I found some samples online of people using the two > together, but I don't quite follow how the process works. Thanks. > > On 2/10/16, Jaimy Azle wrote: >> Connecting to AS400 from native ibm_db_dbi driver would need db2 connect >> which is a separated product from IBM. Use the ibm_db_sa pyodbc driver >> instead, or jdbc (jython) if you don't have db2 connect installed on your >> machine. >> >> Salam, >> >> -Jaimy >> On Feb 11, 2016 01:50, "Alex Hall" wrote: >> >>> Hello list, >>> I sent this to the ibm_db list yesterday, but no one has responded >>> yet. Since it's as much ibm_db as SA, I thought I'd try here as well >>> in case any of you have used an AS400 before. I have ibm_db, >>> ibm_db_sa, the latest sqlalchemy, and Python 2.7 (latest) installed. I >>> can talk to SQLite with no trouble, it's talking to this 400 that >>> won't work. Anyway... >>> >>> I'm finally ready to hook my app to the 400 instead of the local >>> SQLite database I've been using for testing. Here's my simple script: >>> >>> import globals >>> import logging >>> from sqlalchemy import * >>> from sqlalchemy.ext.declarative import declarative_base >>> from sqlalchemy.orm import sessionmaker >>> >>> #set up the sqlalchemy objects >>> dbEngine = create_engine('ibm_db_sa:// >>> username:passw...@mysite.com:8471/database') >>> Session = sessionmaker(bind = dbEngine) #note that's a capital s on >>> Session >>> session = Session() #lowercase s >>> base = declarative_base() >>> >>> def getAllTables(): >>> dbInspector = inspect(dbEngine) >>> for table in dbInspector.get_table_names(): >>> print table >>> >>> getAllTables() >>> >>> When I run that, it waits thirty seconds or so, then tells me there >>> was an error. I'll paste the entire traceback below. Sorry in >>> advance--it's pretty long. >>> >>> Microsoft Windows [Version 6.1.7601] >>> Copyright (c) 2009 Microsoft Corporation. All rights reserved. >>> >>> C:\Users\admin\Dropbox\Autodist\jobs>c:\python27\python.exe >>> DBInterface2.py >>> Traceback (most recent call last): >>> File "DBInterface2.py", line 24, in >>> getAllTables() >>> File "DBInterface2.py", line 18, in getAllTables >>> dbInspector = inspect(dbEngine) >>> File "c:\python27\lib\site-packages\sqlalchemy\inspection.py", line >>> 63, >>> in ins >>> pect >>> ret = reg(subject) >>> File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", >>> line 139 >>> , in _insp >>> return Inspector.from_engine(bind) >>> File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", >>> line 135 >>> , in from_engine >>> return Inspector(bind) >>> File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", >>> line 109 >>> , in __init__ >>> bind.connect().close() >>> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >>> 2018, in >>> connect >>> return self._connection_cls(self, **kwargs) >>> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >>> 72, >>> in __ >>> init__ >>> if connection is not None else engine.raw_connection() >>> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >>> 2104, in >>> raw_connection >>> self.pool.unique_connection, _connection) >>> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >>> 2078, in >>> _wrap_pool_connect >>> e, dialect, self) >>> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >>> 1405, in >>> _handle_dbapi_exception_noconnection >>> exc_info >>> File "c:\python27\lib\site-packages\sqlalchemy\util\compat.py", line >>> 200, in r >>> aise_from_cause >>> reraise(type(exception), exception, tb=exc_tb) >>> File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line >>> 2074, in >>>
[sqlalchemy] Connecting to AS400 with SQLAlchemy fails
Hello list, I sent this to the ibm_db list yesterday, but no one has responded yet. Since it's as much ibm_db as SA, I thought I'd try here as well in case any of you have used an AS400 before. I have ibm_db, ibm_db_sa, the latest sqlalchemy, and Python 2.7 (latest) installed. I can talk to SQLite with no trouble, it's talking to this 400 that won't work. Anyway... I'm finally ready to hook my app to the 400 instead of the local SQLite database I've been using for testing. Here's my simple script: import globals import logging from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker #set up the sqlalchemy objects dbEngine = create_engine('ibm_db_sa://username:passw...@mysite.com:8471/database') Session = sessionmaker(bind = dbEngine) #note that's a capital s on Session session = Session() #lowercase s base = declarative_base() def getAllTables(): dbInspector = inspect(dbEngine) for table in dbInspector.get_table_names(): print table getAllTables() When I run that, it waits thirty seconds or so, then tells me there was an error. I'll paste the entire traceback below. Sorry in advance--it's pretty long. Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:\Users\admin\Dropbox\Autodist\jobs>c:\python27\python.exe DBInterface2.py Traceback (most recent call last): File "DBInterface2.py", line 24, in getAllTables() File "DBInterface2.py", line 18, in getAllTables dbInspector = inspect(dbEngine) File "c:\python27\lib\site-packages\sqlalchemy\inspection.py", line 63, in ins pect ret = reg(subject) File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 139 , in _insp return Inspector.from_engine(bind) File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 135 , in from_engine return Inspector(bind) File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 109 , in __init__ bind.connect().close() File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2018, in connect return self._connection_cls(self, **kwargs) File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 72, in __ init__ if connection is not None else engine.raw_connection() File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2104, in raw_connection self.pool.unique_connection, _connection) File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2078, in _wrap_pool_connect e, dialect, self) File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1405, in _handle_dbapi_exception_noconnection exc_info File "c:\python27\lib\site-packages\sqlalchemy\util\compat.py", line 200, in r aise_from_cause reraise(type(exception), exception, tb=exc_tb) File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2074, in _wrap_pool_connect return fn() File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 318, in unique_c onnection return _ConnectionFairy._checkout(self) File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 713, in _checkou t fairy = _ConnectionRecord.checkout(pool) File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 480, in checkout rec = pool._do_get() File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 1060, in _do_get self._dec_overflow() File "c:\python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 1057, in _do_get return self._create_connection() File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 323, in _create_ connection return _ConnectionRecord(self) File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 449, in __init__ self.connection = self.__connect() File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 607, in __connec t connection = self.__pool._invoke_creator(self) File "c:\python27\lib\site-packages\sqlalchemy\engine\strategies.py", line 97, in connect return dialect.connect(*cargs, **cparams) File "c:\python27\lib\site-packages\sqlalchemy\engine\default.py", line 385, i n connect return self.dbapi.connect(*cargs, **cparams) File "c:\python27\lib\site-packages\ibm_db-2.0.6-py2.7.egg\ibm_db_dbi.py", lin e 588, in connect raise _get_exception(inst) sqlalchemy.exc.OperationalError: (ibm_db_dbi.OperationalError) ibm_db_dbi::Opera tionalError: [IBM][CLI Driver] SQL30081N A communication error has been detecte d. Communication protocol being used: "TCP/IP". Communication API being used: " SOCKETS". Location where the error was detected: "[IP of the 400 server]". Communicatio n function detecting the error: "connect". Protocol specific error code(s): "10 060", "*", "*". SQLSTATE=08001\r SQLCODE=-30081 (I took out the actual IP).
Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails
Connecting to AS400 from native ibm_db_dbi driver would need db2 connect which is a separated product from IBM. Use the ibm_db_sa pyodbc driver instead, or jdbc (jython) if you don't have db2 connect installed on your machine. Salam, -Jaimy On Feb 11, 2016 01:50, "Alex Hall"wrote: > Hello list, > I sent this to the ibm_db list yesterday, but no one has responded > yet. Since it's as much ibm_db as SA, I thought I'd try here as well > in case any of you have used an AS400 before. I have ibm_db, > ibm_db_sa, the latest sqlalchemy, and Python 2.7 (latest) installed. I > can talk to SQLite with no trouble, it's talking to this 400 that > won't work. Anyway... > > I'm finally ready to hook my app to the 400 instead of the local > SQLite database I've been using for testing. Here's my simple script: > > import globals > import logging > from sqlalchemy import * > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm import sessionmaker > > #set up the sqlalchemy objects > dbEngine = create_engine('ibm_db_sa:// > username:passw...@mysite.com:8471/database') > Session = sessionmaker(bind = dbEngine) #note that's a capital s on Session > session = Session() #lowercase s > base = declarative_base() > > def getAllTables(): > dbInspector = inspect(dbEngine) > for table in dbInspector.get_table_names(): > print table > > getAllTables() > > When I run that, it waits thirty seconds or so, then tells me there > was an error. I'll paste the entire traceback below. Sorry in > advance--it's pretty long. > > Microsoft Windows [Version 6.1.7601] > Copyright (c) 2009 Microsoft Corporation. All rights reserved. > > C:\Users\admin\Dropbox\Autodist\jobs>c:\python27\python.exe DBInterface2.py > Traceback (most recent call last): > File "DBInterface2.py", line 24, in > getAllTables() > File "DBInterface2.py", line 18, in getAllTables > dbInspector = inspect(dbEngine) > File "c:\python27\lib\site-packages\sqlalchemy\inspection.py", line 63, > in ins > pect > ret = reg(subject) > File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", > line 139 > , in _insp > return Inspector.from_engine(bind) > File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", > line 135 > , in from_engine > return Inspector(bind) > File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", > line 109 > , in __init__ > bind.connect().close() > File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line > 2018, in > connect > return self._connection_cls(self, **kwargs) > File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 72, > in __ > init__ > if connection is not None else engine.raw_connection() > File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line > 2104, in > raw_connection > self.pool.unique_connection, _connection) > File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line > 2078, in > _wrap_pool_connect > e, dialect, self) > File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line > 1405, in > _handle_dbapi_exception_noconnection > exc_info > File "c:\python27\lib\site-packages\sqlalchemy\util\compat.py", line > 200, in r > aise_from_cause > reraise(type(exception), exception, tb=exc_tb) > File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line > 2074, in > _wrap_pool_connect > return fn() > File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 318, in > unique_c > onnection > return _ConnectionFairy._checkout(self) > File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 713, in > _checkou > t > fairy = _ConnectionRecord.checkout(pool) > File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 480, in > checkout > > rec = pool._do_get() > File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 1060, in > _do_get > > self._dec_overflow() > File "c:\python27\lib\site-packages\sqlalchemy\util\langhelpers.py", > line 60, > in __exit__ > compat.reraise(exc_type, exc_value, exc_tb) > File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 1057, in > _do_get > > return self._create_connection() > File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 323, in > _create_ > connection > return _ConnectionRecord(self) > File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 449, in > __init__ > > self.connection = self.__connect() > File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 607, in > __connec > t > connection = self.__pool._invoke_creator(self) > File "c:\python27\lib\site-packages\sqlalchemy\engine\strategies.py", > line 97, > in connect > return dialect.connect(*cargs, **cparams) > File "c:\python27\lib\site-packages\sqlalchemy\engine\default.py", line > 385, i > n connect > return self.dbapi.connect(*cargs, **cparams) > File >