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
-~----------~----~----~----~------~----~------~--~---
