[sqlalchemy] Re: Access to AS/400 data
On Fri, May 16, 2008 at 3:18 PM, Carlos Hanson [EMAIL PROTECTED] wrote: On Fri, May 16, 2008 at 10:49 AM, Jim Steil [EMAIL PROTECTED] wrote: Carlos Hanson wrote: On Fri, May 16, 2008 at 8:14 AM, Michael Bayer [EMAIL PROTECTED] wrote: On May 16, 2008, at 10:55 AM, Carlos Hanson wrote: On Fri, May 16, 2008 at 6:13 AM, Jim Steil [EMAIL PROTECTED] wrote: Hi: Can anyone tell me if it is possible to access data on an AS/400 through SQLAlchemy? -Jim It's possible if you use db2 connect to make your connection to the iSeries. See http://code.google.com/p/ibm-db/. The support group is [EMAIL PROTECTED] Right now, the as/400 is listed as a Future supported database [1], but there have been some reports from people getting it working. You might want to make your needs known on the support list also follow up with your IBM rep. [1] http://code.google.com/p/ibm-db/wiki/README I'm connecting to an AS/400 using pyodbc, so I am sure that I can do it through SQLAlchemy. If I have a chances to test it, I'll post my success. But if you get an ODBC connection set up, the re should be no problem. well, connecting is just the beginning. to take advantage of SQLA, you would also want an AS/400 dialect that knows how to render SQL in the way an AS/400 likes. Im not familiar with anyone working on an AS/ 400 dialect at the moment. I only know of the DB2 dialect which is a separate project (but maybe ask on their list since they work for IBM). This is a good point. I have to create aliases to a file/member combination to select data. I guess I wouldn't expect SQLAlchemy to implement that by default, since most every other database uses tables. Right... but there's no reason you can't drop down to the db-api level for one-off things like partitioning data with members. Once the alias is created, it can be treated like any other table. -ken --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Access to AS/400 data
On Fri, May 16, 2008 at 6:13 AM, Jim Steil [EMAIL PROTECTED] wrote: Hi: Can anyone tell me if it is possible to access data on an AS/400 through SQLAlchemy? -Jim I'm connecting to an AS/400 using pyodbc, so I am sure that I can do it through SQLAlchemy. If I have a chances to test it, I'll post my success. But if you get an ODBC connection set up, the re should be no problem. -- Carlos Hanson --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Access to AS/400 data
On May 16, 2008, at 10:55 AM, Carlos Hanson wrote: On Fri, May 16, 2008 at 6:13 AM, Jim Steil [EMAIL PROTECTED] wrote: Hi: Can anyone tell me if it is possible to access data on an AS/400 through SQLAlchemy? -Jim I'm connecting to an AS/400 using pyodbc, so I am sure that I can do it through SQLAlchemy. If I have a chances to test it, I'll post my success. But if you get an ODBC connection set up, the re should be no problem. well, connecting is just the beginning. to take advantage of SQLA, you would also want an AS/400 dialect that knows how to render SQL in the way an AS/400 likes. Im not familiar with anyone working on an AS/ 400 dialect at the moment. I only know of the DB2 dialect which is a separate project (but maybe ask on their list since they work for IBM). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Access to AS/400 data
Carlos Hanson wrote: On Fri, May 16, 2008 at 6:13 AM, Jim Steil [EMAIL PROTECTED] wrote: Hi: Can anyone tell me if it is possible to access data on an AS/400 through SQLAlchemy? -Jim I'm connecting to an AS/400 using pyodbc, so I am sure that I can do it through SQLAlchemy. If I have a chances to test it, I'll post my success. But if you get an ODBC connection set up, the re should be no problem. I use pyodbc now to connect to the 400 as well. But, have not dove in to SQLAlchemy yet as I thought I read somewhere that access to the AS/400 was not supported. I currently use SQLObject for my data access for non-AS/400 work, but I don't believe there is any intention of supporting the 400 there. I guess I'm looking for a compelling reason to move in that direction but wanted to know that it works before I start trying it out. I'm curious to see whether or not it works for you. Thanks. -Jim --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Access to AS/400 data
On Fri, May 16, 2008 at 8:14 AM, Michael Bayer [EMAIL PROTECTED] wrote: On May 16, 2008, at 10:55 AM, Carlos Hanson wrote: On Fri, May 16, 2008 at 6:13 AM, Jim Steil [EMAIL PROTECTED] wrote: Hi: Can anyone tell me if it is possible to access data on an AS/400 through SQLAlchemy? -Jim I'm connecting to an AS/400 using pyodbc, so I am sure that I can do it through SQLAlchemy. If I have a chances to test it, I'll post my success. But if you get an ODBC connection set up, the re should be no problem. well, connecting is just the beginning. to take advantage of SQLA, you would also want an AS/400 dialect that knows how to render SQL in the way an AS/400 likes. Im not familiar with anyone working on an AS/ 400 dialect at the moment. I only know of the DB2 dialect which is a separate project (but maybe ask on their list since they work for IBM). This is a good point. I have to create aliases to a file/member combination to select data. I guess I wouldn't expect SQLAlchemy to implement that by default, since most every other database uses tables. -- Carlos Hanson --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Access to AS/400 data
On Fri, May 16, 2008 at 12:03 PM, Carlos Hanson [EMAIL PROTECTED] wrote: On Fri, May 16, 2008 at 8:14 AM, Michael Bayer [EMAIL PROTECTED] wrote: On May 16, 2008, at 10:55 AM, Carlos Hanson wrote: On Fri, May 16, 2008 at 6:13 AM, Jim Steil [EMAIL PROTECTED] wrote: Hi: Can anyone tell me if it is possible to access data on an AS/400 through SQLAlchemy? -Jim I'm connecting to an AS/400 using pyodbc, so I am sure that I can do it through SQLAlchemy. If I have a chances to test it, I'll post my success. But if you get an ODBC connection set up, the re should be no problem. well, connecting is just the beginning. to take advantage of SQLA, you would also want an AS/400 dialect that knows how to render SQL in the way an AS/400 likes. Im not familiar with anyone working on an AS/ 400 dialect at the moment. I only know of the DB2 dialect which is a separate project (but maybe ask on their list since they work for IBM). This is a good point. I have to create aliases to a file/member combination to select data. I guess I wouldn't expect SQLAlchemy to implement that by default, since most every other database uses tables. What is your connection string in pyodbc for as400? When you create aliases then select statements work as they should for you? Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Access to AS/400 data
Carlos Hanson wrote: On Fri, May 16, 2008 at 8:14 AM, Michael Bayer [EMAIL PROTECTED] wrote: On May 16, 2008, at 10:55 AM, Carlos Hanson wrote: On Fri, May 16, 2008 at 6:13 AM, Jim Steil [EMAIL PROTECTED] wrote: Hi: Can anyone tell me if it is possible to access data on an AS/400 through SQLAlchemy? -Jim I'm connecting to an AS/400 using pyodbc, so I am sure that I can do it through SQLAlchemy. If I have a chances to test it, I'll post my success. But if you get an ODBC connection set up, the re should be no problem. well, connecting is just the beginning. to take advantage of SQLA, you would also want an AS/400 dialect that knows how to render SQL in the way an AS/400 likes. Im not familiar with anyone working on an AS/ 400 dialect at the moment. I only know of the DB2 dialect which is a separate project (but maybe ask on their list since they work for IBM). This is a good point. I have to create aliases to a file/member combination to select data. I guess I wouldn't expect SQLAlchemy to implement that by default, since most every other database uses tables. Are you saying that you had to create aliases to make them work with pyodbc? I don't have to create aliases but it works fine for me. I do have to qualify with a library name, but that isn't too painful. -Jim --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Access to AS/400 data
On Fri, May 16, 2008 at 10:49 AM, Jim Steil [EMAIL PROTECTED] wrote:
Carlos Hanson wrote:
On Fri, May 16, 2008 at 8:14 AM, Michael Bayer [EMAIL PROTECTED]
wrote:
On May 16, 2008, at 10:55 AM, Carlos Hanson wrote:
On Fri, May 16, 2008 at 6:13 AM, Jim Steil [EMAIL PROTECTED] wrote:
Hi:
Can anyone tell me if it is possible to access data on an AS/400
through
SQLAlchemy?
-Jim
I'm connecting to an AS/400 using pyodbc, so I am sure that I can do
it through SQLAlchemy. If I have a chances to test it, I'll post my
success. But if you get an ODBC connection set up, the re should be
no problem.
well, connecting is just the beginning. to take advantage of SQLA,
you would also want an AS/400 dialect that knows how to render SQL in
the way an AS/400 likes. Im not familiar with anyone working on an AS/
400 dialect at the moment. I only know of the DB2 dialect which is a
separate project (but maybe ask on their list since they work for IBM).
This is a good point. I have to create aliases to a file/member
combination to select data. I guess I wouldn't expect SQLAlchemy to
implement that by default, since most every other database uses
tables.
Are you saying that you had to create aliases to make them work with
pyodbc? I don't have to create aliases but it works fine for me. I do have
to qualify with a library name, but that isn't too painful.
-Jim
If you only have one member in a file, or you are only interested in
the first member, then you do not need an alias. The application
running on our AS/400 was designed for multiple groups, each group
with its own member of a file, so I need the alias. There may be some
cases where I don't actually need the alias, but it is too much effort
to make the determination. It's easy to create and drop aliases.
Here is a brief example of what I'm doing:
alias_map = {'library': 'FASFILES', 'file': 'LFAS310C', 'member': 'TSD2300'}
sql_alias = 'CREATE ALIAS X%(file)s FOR %(library)s.%(file)s (%(member)s)'
alias = sql_alias % alias_map
alias
'CREATE ALIAS XLFAS310C FOR FASFILES.LFAS310C (TSD2300)'
drop_alias = 'DROP ALIAS X%(file)s' % alias_map
drop_alias
'DROP ALIAS XLFAS310C'
query = 'select FA# from X%(file)s where FA# = 37250' % alias_map
query
'select FA# from XLFAS310C where FA# = 37250'
import pyodbc
connection = pyodbc.connect('DSN=as400_64;UID=username;PWD=password;')
cursor = connection.cursor()
cursor.execute(alias)
0
cursor.execute(query)
pyodbc.Cursor object at 0x2b5194aef690
for row in cursor:
... print row
...
(Decimal(37250), )
cursor.execute(drop_alias)
0
cursor.close()
connection.close()
--
Carlos Hanson
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
