sqlalchemy: don't include PWD in pyodbc connection string if URL does not have a password
I am trying to use sqlalchemy to create an engine for Microsoft Azure Synapse SQL datawarehouse using pyodbc.
I have developed the code below.
"""Functionality to work with the DataWareHouse from Python"""
# See https://docs.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver15
import queue
import threading
from typing import Any
import pyodbc
import pandas as pd
from sqlalchemy import create_engine
import db_config
import pandas as pd
DRIVER = "ODBC+Driver+17+for+SQL+Server"
from urllib.parse import quote_plus
class Datawarehouse:
"""The Datawarehouse provides functionality to work with the ODE Datawarehouse"""
def __init__( # pylint: disable=too-many-arguments
self, server: str, database: str, username=None, password=None, authentication=None
):
"""The Datawarehouse instance provides functionality to work ODE Datawarehouse
Args:
server (str): [description]
database (str): [description]
username ([type], optional): [description]. Defaults to None.
password ([type], optional): [description]. Defaults to None.
authentication ([type], optional): [description]. For example SqlPassword, ActiveDirectoryPassword, ActiveDirectoryIntegrated, ActiveDirectoryInteractive. Defaults to None.
"""
self.server = server
self.database = database
self.username = username
self.password = password
self.authentication = authentication
def get_connection_string(self) -> str:
"""Returns a pyodbc connectionstring based on the current configuration
Returns:
str: [description]
"""
server = self.server
database = self.database
username = quote_plus(self.username)
password = quote_plus(self.password)
if self.password and not self.authentication:
return f"mssql+pyodbc://{username}:{password}@{server}:1433/{database}?driver={DRIVER}&autocommit=true"
if self.authentication=="ActiveDirectoryPassword":
return f"mssql+pyodbc://{username}:{password}@{server}:1433/{database}?driver={DRIVER}&autocommit=true&Authentication=ActiveDirectoryPassword"
if self.authentication=="ActiveDirectoryIntegrated":
return f"mssql+pyodbc://{server}:1433/{database}?driver={DRIVER}&autocommit=true&Authentication=ActiveDirectoryIntegrated"
if self.authentication=="ActiveDirectoryInteractive":
return f"mssql+pyodbc://{username}@{server}:1433/{database}?driver={DRIVER}&autocommit=true&Authentication=ActiveDirectoryInteractive"
return f"mssql+pyodbc://{server}:1433/{database}?driver={DRIVER}&autocommit=true"
def get_engine(self, echo: bool = False) -> Any:
"""Returns a SQL Alchemy Engine based on the current configuration
Returns:
[Any]: [description]
"""
connectionstring = self.get_connection_string()
return create_engine(connectionstring, fast_executemany=True, echo=echo).execution_options(
autocommit=True
)
def to_sql(self, data: pd.DataFrame, table: str, schema: str, if_exists="append"):
"""Saves the given DataFrame to the specified {schema}.{table}
Args:
data (pd.DataFrame): A Pandas DataFrame
table ([type]): The name of the table. For example 'forecast_hourly'
schema ([type]): The name of the schema. For example 'noaa'.
if_exists (str, optional): What to do if the table exists: Replace or Append.
Defaults to "append".
"""
engine = self.get_engine()
with engine.connect() as con:
if if_exists == "replace":
con.execute(f"TRUNCATE TABLE {schema}.{table}")
data.to_sql(
table, schema=schema, con=con, if_exists="append", index=False, chunksize=100
)
def execute(self, sql: str, *args):
"""Executes the specified sql string on the Datawarehouse
Args:
sql (str): A select string
"""
engine = self.get_engine()
with engine.connect() as con:
return con.execute(sql, *args)
def test_can_connect_using_activedirectory_interactive_official():
# from https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15
server = 'tcp:' + db_config.server
database = db_config.database
username = db_config.username
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+";Authentication=ActiveDirectoryInteractive")
# cursor = cnxn.cursor()
query = "select top 2 * from eia.data_view"
# Then
data = pd.read_sql(sql=query, con=cnxn)
assert len(data)==2
def test_can_connect_using_activedirectory_integrated_official():
"""Can log in directly using AD"""
# from https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15
server = 'tcp:' + db_config.server
database = db_config.database
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+";Authentication=ActiveDirectoryIntegrated")
# cursor = cnxn.cursor()
query = "select top 2 * from eia.data_view"
# Then
data = pd.read_sql(sql=query, con=cnxn)
assert len(data)==2
def test_can_connect_using_activedirectory_interactive_sqlalchemy():
# When
dwh = Datawarehouse(
server = db_config.server,
database= db_config.database,
username = db_config.username,
password = db_config.password,
authentication="ActiveDirectoryInteractive"
)
engine = dwh.get_engine(echo=True)
query = "select top 2 * from eia.data_view"
# Then
data = pd.read_sql(sql=query, con=engine)
assert len(data)==2
def test_can_connect_using_activedirectory_integrated_sqlalchemy():
# When
dwh = Datawarehouse(
server = db_config.server,
database= db_config.database,
username = db_config.username,
password = db_config.password,
authentication="ActiveDirectoryIntegrated"
)
engine = dwh.get_engine(echo=True)
query = "select top 2 * from eia.data_view"
# Then
data = pd.read_sql(sql=query, con=engine)
assert len(data)==2
It uses a db_config.py file that looks like
server = INSERT
database= INSERT
username = INSERT
password = INSERT
If I run pytest on it via pytest -s -vv test_connection.py I get something like
============================= test session starts =============================
platform win32 -- Python 3.7.6, pytest-6.0.2, py-1.9.0, pluggy-0.13.1 -- C:\<LONGPATH>\.venv\scripts\python.exe
cachedir: .pytest_cache
rootdir: C:\repos\trading_analytics\us-trading, configfile: pytest.ini
collecting ... collected 4 items
users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_interactive_official PASSED
users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_integrated_official PASSED
users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_interactive_sqlalchemy FAILED
users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_integrated_sqlalchemy FAILED
================================== FAILURES ===================================
________ test_can_connect_using_activedirectory_interactive_sqlalchemy ________
self = Engine(mssql+pyodbc://<USERNAME>%40<DOMAIN>@<SERVERNAME>.database.windows.net:1433/<DATABASE>?Authentication=ActiveDirectoryInteractive&autocommit=true&driver=ODBC+Driver+17+for+SQL+Server)
fn = <bound method Pool.connect of <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>>
connection = None
def _wrap_pool_connect(self, fn, connection):
dialect = self.dialect
try:
> return fn()
.venv\lib\site-packages\sqlalchemy\engine\base.py:2338:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>
def connect(self):
"""Return a DBAPI connection from the pool.
The connection is instrumented such that when its
``close()`` method is called, the connection will be returned to
the pool.
"""
if not self._use_threadlocal:
> return _ConnectionFairy._checkout(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:364:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
cls = <class 'sqlalchemy.pool.base._ConnectionFairy'>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>
threadconns = None, fairy = None
@classmethod
def _checkout(cls, pool, threadconns=None, fairy=None):
if not fairy:
> fairy = _ConnectionRecord.checkout(pool)
.venv\lib\site-packages\sqlalchemy\pool\base.py:778:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
cls = <class 'sqlalchemy.pool.base._ConnectionRecord'>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>
@classmethod
def checkout(cls, pool):
> rec = pool._do_get()
.venv\lib\site-packages\sqlalchemy\pool\base.py:495:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>
def _do_get(self):
use_overflow = self._max_overflow > -1
try:
wait = use_overflow and self._overflow >= self._max_overflow
return self._pool.get(wait, self._timeout)
except sqla_queue.Empty:
# don't do things inside of "except Empty", because when we say
# we timed out or can't connect and raise, Python 3 tells
# people the real error is queue.Empty which it isn't.
pass
if use_overflow and self._overflow >= self._max_overflow:
if not wait:
return self._do_get()
else:
raise exc.TimeoutError(
"QueuePool limit of size %d overflow %d reached, "
"connection timed out, timeout %d"
% (self.size(), self.overflow(), self._timeout),
code="3o7r",
)
if self._inc_overflow():
try:
return self._create_connection()
except:
with util.safe_reraise():
> self._dec_overflow()
.venv\lib\site-packages\sqlalchemy\pool\impl.py:140:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x0000019CF57ADB88>
type_ = None, value = None, traceback = None
def __exit__(self, type_, value, traceback):
# see #2703 for notes
if type_ is None:
exc_type, exc_value, exc_tb = self._exc_info
self._exc_info = None # remove potential circular references
if not self.warn_only:
compat.raise_(
> exc_value, with_traceback=exc_tb,
)
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
def raise_(
exception, with_traceback=None, replace_context=None, from_=False
):
r"""implement "raise" with cause support.
:param exception: exception to raise
:param with_traceback: will call exception.with_traceback()
:param replace_context: an as-yet-unsupported feature. This is
an exception object which we are "replacing", e.g., it's our
"cause" but we don't want it printed. Basically just what
``__suppress_context__`` does but we don't want to suppress
the enclosing context, if any. So for now we make it the
cause.
:param from\_: the cause. this actually sets the cause and doesn't
hope to hide it someday.
"""
if with_traceback is not None:
exception = exception.with_traceback(with_traceback)
if from_ is not False:
exception.__cause__ = from_
elif replace_context is not None:
# no good solution here, we would like to have the exception
# have only the context of replace_context.__context__ so that the
# intermediary exception does not change, but we can't figure
# that out.
exception.__cause__ = replace_context
try:
> raise exception
.venv\lib\site-packages\sqlalchemy\util\compat.py:182:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>
def _do_get(self):
use_overflow = self._max_overflow > -1
try:
wait = use_overflow and self._overflow >= self._max_overflow
return self._pool.get(wait, self._timeout)
except sqla_queue.Empty:
# don't do things inside of "except Empty", because when we say
# we timed out or can't connect and raise, Python 3 tells
# people the real error is queue.Empty which it isn't.
pass
if use_overflow and self._overflow >= self._max_overflow:
if not wait:
return self._do_get()
else:
raise exc.TimeoutError(
"QueuePool limit of size %d overflow %d reached, "
"connection timed out, timeout %d"
% (self.size(), self.overflow(), self._timeout),
code="3o7r",
)
if self._inc_overflow():
try:
> return self._create_connection()
.venv\lib\site-packages\sqlalchemy\pool\impl.py:137:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>
def _create_connection(self):
"""Called by subclasses to create a new ConnectionRecord."""
> return _ConnectionRecord(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:309:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.base._ConnectionRecord object at 0x00000194F4983648>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>
connect = True
def __init__(self, pool, connect=True):
self.__pool = pool
if connect:
> self.__connect(first_connect_check=True)
.venv\lib\site-packages\sqlalchemy\pool\base.py:440:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.base._ConnectionRecord object at 0x00000194F4983648>
first_connect_check = True
def __connect(self, first_connect_check=False):
pool = self.__pool
# ensure any existing connection is removed, so that if
# creator fails, this attribute stays None
self.connection = None
try:
self.starttime = time.time()
connection = pool._invoke_creator(self)
pool.logger.debug("Created new connection %r", connection)
self.connection = connection
except Exception as e:
with util.safe_reraise():
> pool.logger.debug("Error on connect(): %s", e)
.venv\lib\site-packages\sqlalchemy\pool\base.py:661:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x0000019CF57ADEC8>
type_ = None, value = None, traceback = None
def __exit__(self, type_, value, traceback):
# see #2703 for notes
if type_ is None:
exc_type, exc_value, exc_tb = self._exc_info
self._exc_info = None # remove potential circular references
if not self.warn_only:
compat.raise_(
> exc_value, with_traceback=exc_tb,
)
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
def raise_(
exception, with_traceback=None, replace_context=None, from_=False
):
r"""implement "raise" with cause support.
:param exception: exception to raise
:param with_traceback: will call exception.with_traceback()
:param replace_context: an as-yet-unsupported feature. This is
an exception object which we are "replacing", e.g., it's our
"cause" but we don't want it printed. Basically just what
``__suppress_context__`` does but we don't want to suppress
the enclosing context, if any. So for now we make it the
cause.
:param from\_: the cause. this actually sets the cause and doesn't
hope to hide it someday.
"""
if with_traceback is not None:
exception = exception.with_traceback(with_traceback)
if from_ is not False:
exception.__cause__ = from_
elif replace_context is not None:
# no good solution here, we would like to have the exception
# have only the context of replace_context.__context__ so that the
# intermediary exception does not change, but we can't figure
# that out.
exception.__cause__ = replace_context
try:
> raise exception
.venv\lib\site-packages\sqlalchemy\util\compat.py:182:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.base._ConnectionRecord object at 0x00000194F4983648>
first_connect_check = True
def __connect(self, first_connect_check=False):
pool = self.__pool
# ensure any existing connection is removed, so that if
# creator fails, this attribute stays None
self.connection = None
try:
self.starttime = time.time()
> connection = pool._invoke_creator(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:656:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
connection_record = <sqlalchemy.pool.base._ConnectionRecord object at 0x00000194F4983648>
def connect(connection_record=None):
if dialect._has_events:
for fn in dialect.dispatch.do_connect:
connection = fn(
dialect, connection_record, cargs, cparams
)
if connection is not None:
return connection
> return dialect.connect(*cargs, **cparams)
.venv\lib\site-packages\sqlalchemy\engine\strategies.py:114:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x00000194F4B491C8>
cargs = ('DRIVER={ODBC Driver 17 for SQL Server};Server=<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;UID=<USERNAME>@<DOMAIN>;PWD=;Authentication=ActiveDirectoryInteractive',)
cparams = {'autocommit': True}
def connect(self, *cargs, **cparams):
# inherits the docstring from interfaces.Dialect.connect
> return self.dbapi.connect(*cargs, **cparams)
E pyodbc.Error: ('FA003', "[FA003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Password option must not be specified, if Authentication option is 'ActiveDirectoryInteractive'. (0) (SQLDriverConnect); [FA003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Password option must not be specified, if Authentication option is 'ActiveDirectoryInteractive'. (0)")
.venv\lib\site-packages\sqlalchemy\engine\default.py:493: Error
The above exception was the direct cause of the following exception:
def test_can_connect_using_activedirectory_interactive_sqlalchemy():
# When
dwh = Datawarehouse(
server = db_config.server,
database= db_config.database,
username = db_config.username,
password = db_config.password,
authentication="ActiveDirectoryInteractive"
)
engine = dwh.get_engine(echo=True)
query = "select top 2 * from eia.data_view"
# Then
> data = pd.read_sql(sql=query, con=engine)
users\<USERNAME>\<FILENAME>.py:136:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.venv\lib\site-packages\pandas\io\sql.py:515: in read_sql
chunksize=chunksize,
.venv\lib\site-packages\pandas\io\sql.py:1295: in read_query
result = self.execute(*args)
.venv\lib\site-packages\pandas\io\sql.py:1162: in execute
*args, **kwargs
.venv\lib\site-packages\sqlalchemy\engine\base.py:2236: in execute
connection = self._contextual_connect(close_with_result=True)
.venv\lib\site-packages\sqlalchemy\engine\base.py:2304: in _contextual_connect
self._wrap_pool_connect(self.pool.connect, None),
.venv\lib\site-packages\sqlalchemy\engine\base.py:2342: in _wrap_pool_connect
e, dialect, self
.venv\lib\site-packages\sqlalchemy\engine\base.py:1584: in _handle_dbapi_exception_noconnection
sqlalchemy_exception, with_traceback=exc_info[2], from_=e
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
raise exception
.venv\lib\site-packages\sqlalchemy\engine\base.py:2338: in _wrap_pool_connect
return fn()
.venv\lib\site-packages\sqlalchemy\pool\base.py:364: in connect
return _ConnectionFairy._checkout(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:778: in _checkout
fairy = _ConnectionRecord.checkout(pool)
.venv\lib\site-packages\sqlalchemy\pool\base.py:495: in checkout
rec = pool._do_get()
.venv\lib\site-packages\sqlalchemy\pool\impl.py:140: in _do_get
self._dec_overflow()
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69: in __exit__
exc_value, with_traceback=exc_tb,
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
raise exception
.venv\lib\site-packages\sqlalchemy\pool\impl.py:137: in _do_get
return self._create_connection()
.venv\lib\site-packages\sqlalchemy\pool\base.py:309: in _create_connection
return _ConnectionRecord(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:440: in __init__
self.__connect(first_connect_check=True)
.venv\lib\site-packages\sqlalchemy\pool\base.py:661: in __connect
pool.logger.debug("Error on connect(): %s", e)
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69: in __exit__
exc_value, with_traceback=exc_tb,
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
raise exception
.venv\lib\site-packages\sqlalchemy\pool\base.py:656: in __connect
connection = pool._invoke_creator(self)
.venv\lib\site-packages\sqlalchemy\engine\strategies.py:114: in connect
return dialect.connect(*cargs, **cparams)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x00000194F4B491C8>
cargs = ('DRIVER={ODBC Driver 17 for SQL Server};Server=<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;UID=<USERNAME>@<DOMAIN>;PWD=;Authentication=ActiveDirectoryInteractive',)
cparams = {'autocommit': True}
def connect(self, *cargs, **cparams):
# inherits the docstring from interfaces.Dialect.connect
> return self.dbapi.connect(*cargs, **cparams)
E sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('FA003', "[FA003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Password option must not be specified, if Authentication option is 'ActiveDirectoryInteractive'. (0) (SQLDriverConnect); [FA003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Password option must not be specified, if Authentication option is 'ActiveDirectoryInteractive'. (0)")
E (Background on this error at: http://sqlalche.me/e/13/dbapi)
.venv\lib\site-packages\sqlalchemy\engine\default.py:493: DBAPIError
________ test_can_connect_using_activedirectory_integrated_sqlalchemy _________
self = Engine(mssql+pyodbc://<SERVERNAME>.database.windows.net:1433/<DATABASE>?Authentication=ActiveDirectoryIntegrated&autocommit=true&driver=ODBC+Driver+17+for+SQL+Server)
fn = <bound method Pool.connect of <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>>
connection = None
def _wrap_pool_connect(self, fn, connection):
dialect = self.dialect
try:
> return fn()
.venv\lib\site-packages\sqlalchemy\engine\base.py:2338:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>
def connect(self):
"""Return a DBAPI connection from the pool.
The connection is instrumented such that when its
``close()`` method is called, the connection will be returned to
the pool.
"""
if not self._use_threadlocal:
> return _ConnectionFairy._checkout(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:364:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
cls = <class 'sqlalchemy.pool.base._ConnectionFairy'>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>
threadconns = None, fairy = None
@classmethod
def _checkout(cls, pool, threadconns=None, fairy=None):
if not fairy:
> fairy = _ConnectionRecord.checkout(pool)
.venv\lib\site-packages\sqlalchemy\pool\base.py:778:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
cls = <class 'sqlalchemy.pool.base._ConnectionRecord'>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>
@classmethod
def checkout(cls, pool):
> rec = pool._do_get()
.venv\lib\site-packages\sqlalchemy\pool\base.py:495:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>
def _do_get(self):
use_overflow = self._max_overflow > -1
try:
wait = use_overflow and self._overflow >= self._max_overflow
return self._pool.get(wait, self._timeout)
except sqla_queue.Empty:
# don't do things inside of "except Empty", because when we say
# we timed out or can't connect and raise, Python 3 tells
# people the real error is queue.Empty which it isn't.
pass
if use_overflow and self._overflow >= self._max_overflow:
if not wait:
return self._do_get()
else:
raise exc.TimeoutError(
"QueuePool limit of size %d overflow %d reached, "
"connection timed out, timeout %d"
% (self.size(), self.overflow(), self._timeout),
code="3o7r",
)
if self._inc_overflow():
try:
return self._create_connection()
except:
with util.safe_reraise():
> self._dec_overflow()
.venv\lib\site-packages\sqlalchemy\pool\impl.py:140:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x0000019CF5DB2488>
type_ = None, value = None, traceback = None
def __exit__(self, type_, value, traceback):
# see #2703 for notes
if type_ is None:
exc_type, exc_value, exc_tb = self._exc_info
self._exc_info = None # remove potential circular references
if not self.warn_only:
compat.raise_(
> exc_value, with_traceback=exc_tb,
)
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
def raise_(
exception, with_traceback=None, replace_context=None, from_=False
):
r"""implement "raise" with cause support.
:param exception: exception to raise
:param with_traceback: will call exception.with_traceback()
:param replace_context: an as-yet-unsupported feature. This is
an exception object which we are "replacing", e.g., it's our
"cause" but we don't want it printed. Basically just what
``__suppress_context__`` does but we don't want to suppress
the enclosing context, if any. So for now we make it the
cause.
:param from\_: the cause. this actually sets the cause and doesn't
hope to hide it someday.
"""
if with_traceback is not None:
exception = exception.with_traceback(with_traceback)
if from_ is not False:
exception.__cause__ = from_
elif replace_context is not None:
# no good solution here, we would like to have the exception
# have only the context of replace_context.__context__ so that the
# intermediary exception does not change, but we can't figure
# that out.
exception.__cause__ = replace_context
try:
> raise exception
.venv\lib\site-packages\sqlalchemy\util\compat.py:182:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>
def _do_get(self):
use_overflow = self._max_overflow > -1
try:
wait = use_overflow and self._overflow >= self._max_overflow
return self._pool.get(wait, self._timeout)
except sqla_queue.Empty:
# don't do things inside of "except Empty", because when we say
# we timed out or can't connect and raise, Python 3 tells
# people the real error is queue.Empty which it isn't.
pass
if use_overflow and self._overflow >= self._max_overflow:
if not wait:
return self._do_get()
else:
raise exc.TimeoutError(
"QueuePool limit of size %d overflow %d reached, "
"connection timed out, timeout %d"
% (self.size(), self.overflow(), self._timeout),
code="3o7r",
)
if self._inc_overflow():
try:
> return self._create_connection()
.venv\lib\site-packages\sqlalchemy\pool\impl.py:137:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>
def _create_connection(self):
"""Called by subclasses to create a new ConnectionRecord."""
> return _ConnectionRecord(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:309:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.base._ConnectionRecord object at 0x0000019CF5DB20C8>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>
connect = True
def __init__(self, pool, connect=True):
self.__pool = pool
if connect:
> self.__connect(first_connect_check=True)
.venv\lib\site-packages\sqlalchemy\pool\base.py:440:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.base._ConnectionRecord object at 0x0000019CF5DB20C8>
first_connect_check = True
def __connect(self, first_connect_check=False):
pool = self.__pool
# ensure any existing connection is removed, so that if
# creator fails, this attribute stays None
self.connection = None
try:
self.starttime = time.time()
connection = pool._invoke_creator(self)
pool.logger.debug("Created new connection %r", connection)
self.connection = connection
except Exception as e:
with util.safe_reraise():
> pool.logger.debug("Error on connect(): %s", e)
.venv\lib\site-packages\sqlalchemy\pool\base.py:661:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x0000019CF5DB2208>
type_ = None, value = None, traceback = None
def __exit__(self, type_, value, traceback):
# see #2703 for notes
if type_ is None:
exc_type, exc_value, exc_tb = self._exc_info
self._exc_info = None # remove potential circular references
if not self.warn_only:
compat.raise_(
> exc_value, with_traceback=exc_tb,
)
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
def raise_(
exception, with_traceback=None, replace_context=None, from_=False
):
r"""implement "raise" with cause support.
:param exception: exception to raise
:param with_traceback: will call exception.with_traceback()
:param replace_context: an as-yet-unsupported feature. This is
an exception object which we are "replacing", e.g., it's our
"cause" but we don't want it printed. Basically just what
``__suppress_context__`` does but we don't want to suppress
the enclosing context, if any. So for now we make it the
cause.
:param from\_: the cause. this actually sets the cause and doesn't
hope to hide it someday.
"""
if with_traceback is not None:
exception = exception.with_traceback(with_traceback)
if from_ is not False:
exception.__cause__ = from_
elif replace_context is not None:
# no good solution here, we would like to have the exception
# have only the context of replace_context.__context__ so that the
# intermediary exception does not change, but we can't figure
# that out.
exception.__cause__ = replace_context
try:
> raise exception
.venv\lib\site-packages\sqlalchemy\util\compat.py:182:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.pool.base._ConnectionRecord object at 0x0000019CF5DB20C8>
first_connect_check = True
def __connect(self, first_connect_check=False):
pool = self.__pool
# ensure any existing connection is removed, so that if
# creator fails, this attribute stays None
self.connection = None
try:
self.starttime = time.time()
> connection = pool._invoke_creator(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:656:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
connection_record = <sqlalchemy.pool.base._ConnectionRecord object at 0x0000019CF5DB20C8>
def connect(connection_record=None):
if dialect._has_events:
for fn in dialect.dispatch.do_connect:
connection = fn(
dialect, connection_record, cargs, cparams
)
if connection is not None:
return connection
> return dialect.connect(*cargs, **cparams)
.venv\lib\site-packages\sqlalchemy\engine\strategies.py:114:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x0000019CF6042E48>
cargs = ('DRIVER={ODBC Driver 17 for SQL Server};Server=<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;Trusted_Connection=Yes;Authentication=ActiveDirectoryIntegrated',)
cparams = {'autocommit': True}
def connect(self, *cargs, **cparams):
# inherits the docstring from interfaces.Dialect.connect
> return self.dbapi.connect(*cargs, **cparams)
E pyodbc.Error: ('FA001', '[FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option. (0) (SQLDriverConnect); [FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option. (0)')
.venv\lib\site-packages\sqlalchemy\engine\default.py:493: Error
The above exception was the direct cause of the following exception:
def test_can_connect_using_activedirectory_integrated_sqlalchemy():
# When
dwh = Datawarehouse(
server = db_config.server,
database= db_config.database,
username = db_config.username,
password = db_config.password,
authentication="ActiveDirectoryIntegrated"
)
engine = dwh.get_engine(echo=True)
query = "select top 2 * from eia.data_view"
# Then
> data = pd.read_sql(sql=query, con=engine)
users\<USERNAME>\<FILENAME>.py:152:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.venv\lib\site-packages\pandas\io\sql.py:515: in read_sql
chunksize=chunksize,
.venv\lib\site-packages\pandas\io\sql.py:1295: in read_query
result = self.execute(*args)
.venv\lib\site-packages\pandas\io\sql.py:1162: in execute
*args, **kwargs
.venv\lib\site-packages\sqlalchemy\engine\base.py:2236: in execute
connection = self._contextual_connect(close_with_result=True)
.venv\lib\site-packages\sqlalchemy\engine\base.py:2304: in _contextual_connect
self._wrap_pool_connect(self.pool.connect, None),
.venv\lib\site-packages\sqlalchemy\engine\base.py:2342: in _wrap_pool_connect
e, dialect, self
.venv\lib\site-packages\sqlalchemy\engine\base.py:1584: in _handle_dbapi_exception_noconnection
sqlalchemy_exception, with_traceback=exc_info[2], from_=e
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
raise exception
.venv\lib\site-packages\sqlalchemy\engine\base.py:2338: in _wrap_pool_connect
return fn()
.venv\lib\site-packages\sqlalchemy\pool\base.py:364: in connect
return _ConnectionFairy._checkout(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:778: in _checkout
fairy = _ConnectionRecord.checkout(pool)
.venv\lib\site-packages\sqlalchemy\pool\base.py:495: in checkout
rec = pool._do_get()
.venv\lib\site-packages\sqlalchemy\pool\impl.py:140: in _do_get
self._dec_overflow()
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69: in __exit__
exc_value, with_traceback=exc_tb,
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
raise exception
.venv\lib\site-packages\sqlalchemy\pool\impl.py:137: in _do_get
return self._create_connection()
.venv\lib\site-packages\sqlalchemy\pool\base.py:309: in _create_connection
return _ConnectionRecord(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:440: in __init__
self.__connect(first_connect_check=True)
.venv\lib\site-packages\sqlalchemy\pool\base.py:661: in __connect
pool.logger.debug("Error on connect(): %s", e)
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69: in __exit__
exc_value, with_traceback=exc_tb,
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
raise exception
.venv\lib\site-packages\sqlalchemy\pool\base.py:656: in __connect
connection = pool._invoke_creator(self)
.venv\lib\site-packages\sqlalchemy\engine\strategies.py:114: in connect
return dialect.connect(*cargs, **cparams)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x0000019CF6042E48>
cargs = ('DRIVER={ODBC Driver 17 for SQL Server};Server=<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;Trusted_Connection=Yes;Authentication=ActiveDirectoryIntegrated',)
cparams = {'autocommit': True}
def connect(self, *cargs, **cparams):
# inherits the docstring from interfaces.Dialect.connect
> return self.dbapi.connect(*cargs, **cparams)
E sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('FA001', '[FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option. (0) (SQLDriverConnect); [FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option. (0)')
E (Background on this error at: http://sqlalche.me/e/13/dbapi)
.venv\lib\site-packages\sqlalchemy\engine\default.py:493: DBAPIError
=========================== short test summary info ===========================
FAILED users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_interactive_sqlalchemy
FAILED users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_integrated_sqlalchemy
======================== 2 failed, 2 passed in 38.82s =========================
As you can see I can connect and get data via pyodbc on its own. But not via sqlalchemy.
Versions.
- OS: Window 10
- Python: 3.7.6
- SQLAlchemy: 1.3.19
- Database: Microsoft Azure Synapse SQL Datawarehouse
- DBAPI: pyodbc 4.0.30
About this issue
- Original URL
- State: closed
- Created 4 years ago
- Comments: 19 (11 by maintainers)
Commits related to this issue
- Apply test patch for issue #5592 — committed to gordthompson/sqlalchemy by gordthompson 4 years ago
- Add support for Azure authentication options Fixes: #5592 Change-Id: I0688e5ea0fc6b01a0b72f397daea8f57a2ec0766 (cherry picked from commit dcd2be16b9c9a6a52b6bb7e144e31998b7cee631) — committed to sqlalchemy/sqlalchemy by gordthompson 4 years ago
I’m really impressed by the collaboration and Communication on this problem.
Will test tomorrow Danish time when i’m Back on Work.
@MarcSkovMadsen - If it’s just a warning then it doesn’t really need to be “fixed”. Azure DW is a bit different from SQL Server when it comes to things like transactions.
@MarcSkovMadsen - It occurred to me that the “authentication=” keyword should be lowercase to be consistent with “driver=” so I pushed another change to my branch. The connection URI I used for testing was
@zzzeek - That’s the ultimate plan, for sure. We’ll also need to do some refactoring as this is mssql-specific so it really belongs in sqlalchemy/dialects/mssql/pyodbc.py, not sqlalchemy/connectors/pyodbc.py. Just trying to pin down the requirements first.
@MarcSkovMadsen - Thanks for the clarification. I have pushed another commit to my mssql_pwd_1_3 branch. Please uninstall and reinstall via pip and see if that works any better.
@zzzeek
Not in my experience. SQL Server lets us create a login with a blank password, but when connecting via ODBC we can either use
UID=anon;PWD=or just omit thePWD=argument altogether.@MarcSkovMadsen - If you want to test the above patch you can