Connect to SQL Server (with pyodbc) getting error: sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') None None

[Victor Reichert]

Hello World!

 

This is my first foray into python and SQL Alchemy, and I'm spinning my wheels.  I'm running the code below and am able to connect to my DB and query data without error.

 

import pyodbc

cnxn = pyodbc.connect('DSN=py_test; Trusted_Connection=Yes')

 

However, when I try

import sqlalchemy
engine = sqlalchemy.create_engine('mssql+pyodbc://DSN=py_test; Trusted_Connection=Yes')

result = engine.execute("SELECT * FROM dbo.test_table")

I receive the following error, I am running python 3.3 on 32 bit Windows 7 Enterprise

 

Traceback (most recent call last):
  File "C:\Python33\lib\site-packages\sqlalchemy\pool.py", line 757, in _do_get
    return self._pool.get(wait, self._timeout)
  File "C:\Python33\lib\site-packages\sqlalchemy\util\queue.py", line 166, in get
    raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Python33\lib\site-packages\sqlalchemy\engine\strategies.py", line 80, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Python33\lib\site-packages\sqlalchemy\engine\default.py", line 285, in connect
    return self.dbapi.connect(*cargs, **cparams)
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\V114804\workspace\Remetrica_Simulated_ILC_WW_AP_20130520_ELT\Src\Test_DB_Connection.py", line 11, in <module>
    result = engine.execute("SELECT * FROM dbo.test_table")
  File "C:\Python33\lib\site-packages\sqlalchemy\engine\base.py", line 1613, in execute
    connection = self.contextual_connect(close_with_result=True)
  File "C:\Python33\lib\site-packages\sqlalchemy\engine\base.py", line 1661, in contextual_connect
    self.pool.connect(),
  File "C:\Python33\lib\site-packages\sqlalchemy\pool.py", line 272, in connect
    return _ConnectionFairy(self).checkout()
  File "C:\Python33\lib\site-packages\sqlalchemy\pool.py", line 425, in __init__
    rec = self._connection_record = pool._do_get()
  File "C:\Python33\lib\site-packages\sqlalchemy\pool.py", line 777, in _do_get
    con = self._create_connection()
  File "C:\Python33\lib\site-packages\sqlalchemy\pool.py", line 225, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Python33\lib\site-packages\sqlalchemy\pool.py", line 318, in __init__
    self.connection = self.__connect()
  File "C:\Python33\lib\site-packages\sqlalchemy\pool.py", line 368, in __connect
    connection = self.__pool._creator()
  File "C:\Python33\lib\site-packages\sqlalchemy\engine\strategies.py", line 87, in connect
    ) from e
sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') None None

 

I have also tried:

 

import sqlalchemy

import pyodbc

 

def connect():
    pyodbc.connect('DRIVER={SQL Server};Server=SDAWWRMSD05;Database=ReMetrica_Results_201207;Trusted_Connection=Yes')

print('Connect Method Created')

created_engine = sqlalchemy.create_engine('mssql://', creator=connect)
created_result = created_engine.execute("SELECT * FROM dbo.test_table")

The definition is called, then the program hangs.

 

If anyone could please give me some advice on how to get around this that would be great. 

 

Thank you for your help, please let me know if I can provide any additional information.

 

~Victor

 

[Michael Bayer]

I will show you a short program that you can use to experiment with the Pyodbc connector - the purpose of this program is to illustrate what SQLAlchemy will send to pyodbc.connect():

from sqlalchemy.connectors import pyodbc

from sqlalchemy.engine.url import make_url

conn = pyodbc.PyODBCConnector()

url = make_url('mssql+pyodbc://DSN=py_test; Trusted_Connection=Yes')

connect_args = conn.create_connect_args(url)

print(connect_args)

This returns:

[['dsn=DSN=py_test; Trusted_Connection=Yes;Trusted_Connection=Yes'], {}]

where you can see there's an extra "DSN=" in there.  

There's a large number of connection examples for Pyodbc here:  http://docs.sqlalchemy.org/en/rel_0_8/dialects/mssql.html#additional-connection-examples

You can see, that for standard "TrustedConnection", you only need the dsn name:

create_engine('mssql+pyodbc://mydsn')

which our test script shows the arguments as:

[['dsn=mydsn;Trusted_Connection=Yes'], {}]

looks good to me, so good luck !

--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Victor Reichert]

Thank you!

 

That got me going.

 

For what it's worth, I was using python 3.3 and trying to generate a custom connection string wtih the following command:

 

import urllib

string = urllib.quote_plus('DRIVER={SQL Server};Server=Server;Database=DB;Trusted_Connection=Yes')

conn = pyodbc.PyODBCConnector()
url = make_url('mssql+pyodbc://?odbc_connect='+string)
connect_args = conn.create_connect_args(url)
print(connect_args)

 

Which generated:

 

AttributeError: 'module' object has no attribute 'quote_plus'

I swtiched to python 2.7 and it worked fine.  If the above issue is a general problem in python 3, It might be worth noting the the documentation example will not work in that version.

 

I'm getting the feeling that python3 is not well supported yet. 

 

Thank you again for your help,

 

~Victor

[Michael Bayer]

On Jun 7, 2013, at 6:11 PM, Victor Reichert <vfr...@gmail.com> wrote:

Thank you!

 

That got me going.

 

For what it's worth, I was using python 3.3 and trying to generate a custom connection string wtih the following command:

 

import urllib

string = urllib.quote_plus('DRIVER={SQL Server};Server=Server;Database=DB;Trusted_Connection=Yes')

conn = pyodbc.PyODBCConnector()
url = make_url('mssql+pyodbc://?odbc_connect='+string)
connect_args = conn.create_connect_args(url)
print(connect_args)

 

Which generated:

 

AttributeError: 'module' object has no attribute 'quote_plus'

I swtiched to python 2.7 and it worked fine.  If the above issue is a general problem in python 3, It might be worth noting the the documentation example will not work in that version.

 

I'm getting the feeling that python3 is not well supported yet. 

not for pyodbc in 0.8, but in 0.9 (git master) it's working pretty well (no 2to3 step either).