Using SQL Alchemy and pyodbc with IronPython 2.6.1
- by beargle
I'm using IronPython and the clr module to retrieve SQL Server information via SMO. I'd like to retrieve/store this data in a SQL Server database using SQL Alchemy, but am having some trouble loading the pyodbc module.
Here's the setup:
IronPython 2.6.1 (installed at D:\Program Files\IronPython)
CPython 2.6.5 (installed at D:\Python26)
SQL Alchemy 0.6.1 (installed at D:\Python26\Lib\site-packages\sqlalchemy)
pyodbc 2.1.7 (installed at D:\Python26\Lib\site-packages)
I have these entries in the IronPython site.py to import CPython standard and third-party libraries:
# Add CPython standard libs and DLLs
import sys
sys.path.append(r"D:\Python26\Lib")
sys.path.append(r"D:\Python26\DLLs")
sys.path.append(r"D:\Python26\lib-tk")
sys.path.append(r"D:\Python26")
# Add CPython third-party libs
sys.path.append(r"D:\Python26\Lib\site-packages")
# sqlite3
sys.path.append(r"D:\Python26\Lib\sqlite3")
# Add SQL Server SMO
sys.path.append(r"D:\Program Files\Microsoft SQL Server\100\SDK\Assemblies")
import clr
clr.AddReferenceToFile('Microsoft.SqlServer.Smo.dll')
clr.AddReferenceToFile('Microsoft.SqlServer.SqlEnum.dll')
clr.AddReferenceToFile('Microsoft.SqlServer.ConnectionInfo.dll')
SQL Alchemy imports OK in IronPython, put I receive this error message when trying to connect to SQL Server:
IronPython 2.6.1 (2.6.10920.0) on .NET 2.0.50727.3607
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> e = sqlalchemy.MetaData("mssql://")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "D:\Python26\Lib\site-packages\sqlalchemy\schema.py", line 1780, in __init__
File "D:\Python26\Lib\site-packages\sqlalchemy\schema.py", line 1828, in _bind_to
File "D:\Python26\Lib\site-packages\sqlalchemy\engine\__init__.py", line 241, in create_engine
File "D:\Python26\Lib\site-packages\sqlalchemy\engine\strategies.py", line 60, in create
File "D:\Python26\Lib\site-packages\sqlalchemy\connectors\pyodbc.py", line 29, in dbapi
ImportError: No module named pyodbc
This code works just fine in CPython, but it looks like the pyodbc module isn't accessible from IronPython.
Any suggestions? I realize that this may not be the best way to approach the problem, so I'm open to tackling this a different way. Just wanted to get some experience with using SQL Alchemy and pyodbc.
