On Jun 7, 2014, at 8:27 PM, Cory Lutton <[email protected]> wrote:
> I have been looking at using sqlalchemy in an internal company cherrypy
> application I am working on. It will need to interface with my companies
> iSeries server in order to use ERP data. I have been using pyodbc so far and
> everything works great. I am thinking of adding access to another database
> that is postgres. Rather than write that stuff again, I was thinking about
> trying to use sqlalchemy. If I use that I would want to use it for
> both....one for the iSeries (DB2) and one for postgres......
>
> So, I started writing a "dialect" for iseries+pyodbc and want to make sure I
> am headed down the right path. It seems to be working so far....
> import sqlalchemy as sa
> import sqlalchemy_iseries
> from urllib.parse import quote
>
> engine = sa.create_engine(
> "iseries+pyodbc:///?odbc_connect={connect}".format(
> connect=quote(connect)), pool_size=1)
> con = engine.connect()
>
> # Only using like a pyodbc cursor, executing specifically created statements.
> rows = con.execute("SELECT * FROM alpha.r50all.lbmx")
>
> # Access via name like a dictionary rather than row.LBID
> for row in rows:
> print(row['LBID'])
>
> con.close()
>
> Being new to sqlalchemy I am hoping to get some advice on whether what I am
> doing below is basically going in the right direction or point me in the
> right direction if I am headed the wrong way (or reinventing something) .....
>
> Here is what I have so far...
>
> __init__.py:
> from sqlalchemy.dialects import registry
> from . import pyodbc
>
> dialect = pyodbc.dialect
>
> registry.register("iseries.pyodbc", "sqlalchemy_iseries", "dialect")
>
> base.py:
> from sqlalchemy.engine import default
>
> class ISeriesDialect(default.DefaultDialect):
> name = 'iseries'
> max_identifier_length = 128
> schema_name = "qgpl"
>
>
> pyodbc.py:
> from .base import ISeriesDialect
> from sqlalchemy.connectors.pyodbc import PyODBCConnector
>
> class ISeriesDialect_pyodbc(PyODBCConnector, ISeriesDialect):
> pyodbc_driver_name = 'iSeries Access ODBC Driver'
>
> def _check_unicode_returns(self, connection):
> return False
>
> dialect = ISeriesDialect_pyodbc
looks great. if you want examples of the full format, take a look at some of
the existing external dialects at
http://docs.sqlalchemy.org/en/rel_0_9/dialects/index.html#external-dialects.
Are you sure that the IBM DB SA dialect doesn't cover this backend already?
They have support for pyodbc + DB2, but I'm not really sure how "iSeries"
differs. https://code.google.com/p/ibm-db/
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
