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 !
On Jun 5, 2013, at 9:47 PM, Victor Reichert <[email protected]> wrote:
> 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
>
>
> --
> 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?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
