"No module named pyodbc" for custom Dialect

[Lycovian]

I'm attempting to code up a simple Teradata dialect for SQLAlchemy but am getting a curious error complaining of "No module named pyodbc" when I attempt to do a create_engine on my Windows box.  I'm running on Windows 7 (32-bit), with stock Python 2.7 from the Python website and SQLA 0.9.8 and pyODBC 3.0.7.

PyODBC otherwise works fine otherwise in other tests from Python, but fails when SQLAlchemy attempts to __import__ it for some reason.  I've found several posts regarding this issue for IronPython and other non-stock Windows Pythons but nothing that is glaringly obvious as to what is wrong with my relatively standard setup.

 I'll include the custom dialect files if anyone wants to try it themselves.  The nose tests also appear to fail with this error.

<snip>

# both libs appear to be local to each other's Python site-packages dir

In [10]: import sqlalchemy

In [11]: print sqlalchemy.__file__

C:\Python27\lib\site-packages\sqlalchemy\__init__.pyc

In [12]: import pyodbc

In [13]: print pyodbc.__file__

C:\Python27\lib\site-packages\pyodbc.pyd

# pyodbc appears to work

In [15]: conn = pyodbc.connect('dsn=td_tms_user')

In [16]: conn.execute('select current_timestamp').fetchone()

Out[16]: (datetime.datetime(2015, 1, 3, 21, 12, 21, 100000), )

# loading my custom dialect fails though

In [17]: from sqlalchemy.dialects import registry

In [18]: registry.register("teradata.pyodbc", "sqlalchemy_teradata.pyodbc", "teradataDialect_pyodbc")

In [19]: engine = create_engine('teradata+pyodbc://tms_user:password@td_tms_user')

---------------------------------------------------------------------------

ImportError                               Traceback (most recent call last)

<ipython-input-19-36d09e5c6713> in <module>()

----> 1 engine = create_engine('teradata+pyodbc://tms_user:password@td_tms_user')

C:\Python27\lib\site-packages\sqlalchemy\engine\__init__.pyc in create_engine(*args, **kwargs)

    360     strategy = kwargs.pop('strategy', default_strategy)

    361     strategy = strategies.strategies[strategy]

--> 362     return strategy.create(*args, **kwargs)

    363

    364

C:\Python27\lib\site-packages\sqlalchemy\engine\strategies.pyc in create(self, name_or_url, **kwargs)

     49         u = url.make_url(name_or_url)

     50

---> 51         dialect_cls = u.get_dialect()

     52

     53         if kwargs.pop('_coerce_config', False):

C:\Python27\lib\site-packages\sqlalchemy\engine\url.pyc in get_dialect(self)

    127         else:

    128             name = self.drivername.replace('+', '.')

--> 129         cls = registry.load(name)

    130         # check for legacy dialects that

    131         # would return a module with 'dialect' as the

C:\Python27\lib\site-packages\sqlalchemy\util\langhelpers.pyc in load(self, name)

    172     def load(self, name):

    173         if name in self.impls:

--> 174             return self.impls[name]()

    175

    176         if self.auto_fn:

C:\Python27\lib\site-packages\sqlalchemy\util\langhelpers.pyc in load()

    196     def register(self, name, modulepath, objname):

    197         def load():

--> 198             mod = compat.import_(modulepath)

    199             for token in modulepath.split(".")[1:]:

    200                 mod = getattr(mod, token)

C:\Python27\lib\site-packages\sqlalchemy\util\compat.pyc in import_(*args)

    138         if len(args) == 4:

    139             args = args[0:3] + ([str(arg) for arg in args[3]],)

--> 140         return __import__(*args)

    141

    142     callable = callable

ImportError: No module named pyodbc

</snip>

[Lycovian]

Never mind.  I had used the Bitbucket sqlalchemy-access as a template and forgot to include the pyodbc.py file from the example in my code.  As it was named pyodbc.py it confused the issue.

Please disregard question.

[Richard Baxter]

Hi Lycovian

I'm trying to work with Teradata via SQL Alchemy and googling around has lead mainly to your posts.  Were you able to get the two to work together well?  Or did you have to spend a considerable time writing the custom dialect?

Many thanks, Rich 

[Mike Wilson]

I had to write a custom dialect to get any sort of functionality.  As I eventually got pyodbc to work though I've primarily been using that.  I'm working on getting the company I work for off of Teradata so all I really need is connectivity of some sort.  Besides that the development for the custom dialect has flagged but out of curiosity I might working on it again soon.

BTW, if you too are trying looking for ways to export data from TD I can't recommend this repo enough:

https://github.com/xlfe/dwhwrapper

It's a python exporter that wraps fexp (TD's export utility) and can produce CSV files!  I've extended it in a few minor ways for my usage but if I get approval from my company I may fork the original source and post my changes.  Definitely worth a look if you are trying to export large amounts of data from TD to non-TD databases.

Cheers.

Mike

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/-ONODKBWHXY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Rich]

Hi Mike

Thanks for the reply.  I've broadly just settled on pyodbc too so I feel reassured to read you've gone this way.  I'm quite new to Python so keeping things simple is probably wise.  Unfortunately, my employers have just got Teradata in - so it's going to be a long time before we see the back of it...

Thanks for the hat tip on the export repo.

Thanks again, Rich

 

[Chuanlong Du]

Hi Mike,

Can you share your code of the custom dialect?

Best,