Microsoft SQL connection with SQLAlchemy and pyodbc

[Matthew Albert]

Hi,

I'm trying to make a connection to a 2012 MS SQL database using python 3.4 and SQLAlchemy/pyodbc.

I don't have pyodbc, but noticed that the install of SQLAlchemy included it.  I've copied my code below to select the first value from the table 'Mezzanines'.  Please note the connection string:

'mssql+pyodbc://TheServer//TheDB'

Attempting to connection using windows authentication.

Traceback error shown below.  No module named 'pyodbc'.  Is this because create_engine is trying to find module pyodbc in the sys.path?  Do I need to put pyodbc in the sys.path?

thx

Matt

________________________________________________

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

 

from sqlalchemy_declarative import Address, Base, Person

from sqlalchemy.connectors import pyodbc

 

engine = create_engine('mssql+pyodbc://TheServer//TheDB')

Base.metadata.bind = engine

 

DBSession = sessionmaker(bind=engine)

session = DBSession()

# Write the query

mezz = session.query('Mezzanines').first()

print(mezz.name)

__________________________________________________

Traceback (most recent call last):

  File "C:/Python34/Programs/SQLAlchemy Tutorial/sqlalchemy_insert.py", line 7, in <module>

    engine = create_engine('mssql+pyodbc://gtasfdm')

  File "C:\Python34\lib\site-packages\sqlalchemy\engine\__init__.py", line 362, in create_engine

    return strategy.create(*args, **kwargs)

  File "C:\Python34\lib\site-packages\sqlalchemy\engine\strategies.py", line 74, in create

    dbapi = dialect_cls.dbapi(**dbapi_args)

  File "C:\Python34\lib\site-packages\sqlalchemy\connectors\pyodbc.py", line 51, in dbapi

    return __import__('pyodbc')

ImportError: No module named 'pyodbc'

[Michael Bayer]

pyodbc has to be installed separately, see https://pypi.python.org/pypi/pyodbc/

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

[Matthew Albert]

Thx Mike,

I've been here before.  I'm using windows 64bit and python 3.4.  I only find pyodbc for <=v3.3 here.  Errors because of lacking python 3.3 registry entry.

Somebody on stackoverflow suggested this site for 3.4.

http://www.lfd.uci.edu/~gohlke/pythonlibs/#pyodbc

I downloaded and ran the binary install from this site, seemed to go ok, although python still can't seem to find the module.  The installation wizard correctly identifies the Python34 path and suggests to install at \Python34\Lib\site-packages which all seems reasonable.  The actually install process completes almost instantaneously which seems a little suspicious.  The closest thing to pyodbc I see in ..\site-packages is a folder named:

\pyodbc-3.0.7-py3.4.egg-info

Any thoughts on what is going on?

thx

Matt

[Michael Bayer]

haven’t seen pyodbc work on Python 3 myself, it might not be ready for primetime.   I’d look into pymssql instead it’s well maintained and should have direct py3k support.