def test_pyodbc_connect_dsn_extra(self):
dialect = pyodbc.dialect()
u = url.make_url("mssql://*****:*****@mydsn/?LANGUAGE=us_" "english&foo=bar")
connection = dialect.create_connect_args(u)
dsn_string = connection[0][0]
assert ";LANGUAGE=us_english" in dsn_string
assert ";foo=bar" in dsn_string
def test_pyodbc_disconnect(self):
dialect = pyodbc.dialect()
class MockDBAPIError(Exception):
pass
class MockProgrammingError(MockDBAPIError):
pass
dialect.dbapi = Mock(
Error=MockDBAPIError, ProgrammingError=MockProgrammingError)
for error in [
MockDBAPIError("[%s] some pyodbc message" % code)
for code in [
'08S01', '01002', '08003', '08007',
'08S02', '08001', 'HYT00', 'HY010']
] + [
MockProgrammingError(message)
for message in [
"(some pyodbc stuff) The cursor's connection has been closed.",
"(some pyodbc stuff) Attempt to use a closed connection."
]
]:
eq_(dialect.is_disconnect(error, None, None), True)
eq_(dialect.is_disconnect(
MockProgrammingError("not an error"), None, None), False)
def test_pyodbc_odbc_connect_with_dsn(self):
dialect = pyodbc.dialect()
u = url.make_url(
"mssql:///?odbc_connect=dsn%3Dmydsn%3BDatabase" "%3Ddatabase%3BUID%3Dusername%3BPWD%3Dpassword"
)
connection = dialect.create_connect_args(u)
eq_([["dsn=mydsn;Database=database;UID=username;PWD=password"], {}], connection)
def test_pyodbc_host_no_driver(self):
dialect = pyodbc.dialect()
u = url.make_url("mssql://*****:*****@hostspec/database")
def go():
return dialect.create_connect_args(u)
connection = assert_warnings(
go,
[
"No driver name specified; this is expected by "
"PyODBC when using DSN-less connections"
],
)
eq_(
[
[
"Server=hostspec;Database=database;UI"
"D=username;PWD=password"
],
{},
],
connection,
)
def test_pyodbc_connect_comma_port(self):
dialect = pyodbc.dialect()
u = \
url.make_url('mssql://*****:*****@hostspec:12345/data'
'base')
connection = dialect.create_connect_args(u)
eq_([['DRIVER={SQL Server};Server=hostspec,12345;Database=datab'
'ase;UID=username;PWD=password'], {}], connection)
def test_pyodbc_connect_config_port(self):
dialect = pyodbc.dialect()
u = \
url.make_url('mssql://*****:*****@hostspec/database?p'
'ort=12345')
connection = dialect.create_connect_args(u)
eq_([['DRIVER={SQL Server};Server=hostspec;Database=database;UI'
'D=username;PWD=password;port=12345'], {}], connection)
def test_pyodbc_hostname(self):
dialect = pyodbc.dialect()
u = url.make_url(
'mssql://*****:*****@hostspec/database?driver=SQL+Server'
)
connection = dialect.create_connect_args(u)
eq_([['DRIVER={SQL Server};Server=hostspec;Database=database;UI'
'D=username;PWD=password'], {}], connection)
def test_pyodbc_connect_comma_port(self):
dialect = pyodbc.dialect()
u = url.make_url("mssql://*****:*****@hostspec:12345/data" "base")
connection = dialect.create_connect_args(u)
eq_(
[["DRIVER={SQL Server};Server=hostspec,12345;Database=datab" "ase;UID=username;PWD=password"], {}],
connection,
)
def test_pyodbc_odbc_connect(self):
dialect = pyodbc.dialect()
u = \
url.make_url('mssql:///?odbc_connect=DRIVER%3D%7BSQL+Server'
'%7D%3BServer%3Dhostspec%3BDatabase%3Ddatabase'
'%3BUID%3Dusername%3BPWD%3Dpassword')
connection = dialect.create_connect_args(u)
eq_([['DRIVER={SQL Server};Server=hostspec;Database=database;UI'
'D=username;PWD=password'], {}], connection)
def test_pyodbc_odbc_connect_ignores_other_values(self):
dialect = pyodbc.dialect()
u = \
url.make_url('mssql://*****:*****@localhost/dbdiff?od'
'bc_connect=DRIVER%3D%7BSQL+Server%7D%3BServer'
'%3Dhostspec%3BDatabase%3Ddatabase%3BUID%3Duse'
'rname%3BPWD%3Dpassword')
connection = dialect.create_connect_args(u)
eq_([['DRIVER={SQL Server};Server=hostspec;Database=database;UI'
'D=username;PWD=password'], {}], connection)
def test_pyodbc_extra_connect(self):
dialect = pyodbc.dialect()
u = \
url.make_url('mssql://*****:*****@hostspec/database?L'
'ANGUAGE=us_english&foo=bar')
connection = dialect.create_connect_args(u)
eq_(connection[1], {})
eq_(connection[0][0]
in ('DRIVER={SQL Server};Server=hostspec;Database=database;'
'UID=username;PWD=password;foo=bar;LANGUAGE=us_english',
'DRIVER={SQL Server};Server=hostspec;Database=database;UID='
'username;PWD=password;LANGUAGE=us_english;foo=bar'), True)
def test_pyodbc_extra_connect(self):
dialect = pyodbc.dialect()
u = url.make_url("mssql://*****:*****@hostspec/database?L" "ANGUAGE=us_english&foo=bar")
connection = dialect.create_connect_args(u)
eq_(connection[1], {})
eq_(
connection[0][0]
in (
"DRIVER={SQL Server};Server=hostspec;Database=database;"
"UID=username;PWD=password;foo=bar;LANGUAGE=us_english",
"DRIVER={SQL Server};Server=hostspec;Database=database;UID="
"username;PWD=password;LANGUAGE=us_english;foo=bar",
),
True,
)
def test_pyodbc_connect_config_port(self):
dialect = pyodbc.dialect()
u = url.make_url(
"mssql://*****:*****@hostspec/database?p"
"ort=12345&driver=SQL+Server"
)
connection = dialect.create_connect_args(u)
eq_(
[
[
"DRIVER={SQL Server};Server=hostspec;Database=database;UI"
"D=username;PWD=password;port=12345"
],
{},
],
connection,
)
def test_pyodbc_odbc_connect_ignores_other_values(self):
dialect = pyodbc.dialect()
u = url.make_url("mssql://*****:*****@localhost/dbdiff?od"
"bc_connect=DRIVER%3D%7BSQL+Server%7D%3BServer"
"%3Dhostspec%3BDatabase%3Ddatabase%3BUID%3Duse"
"rname%3BPWD%3Dpassword")
connection = dialect.create_connect_args(u)
eq_(
[
[
"DRIVER={SQL Server};Server=hostspec;Database=database;UI"
"D=username;PWD=password"
],
{},
],
connection,
)
def test_dates(self, type_, args, kw, res, server_version,
use_type_descriptor, driver):
"Exercise type specification for date types."
if driver == "base":
from sqlalchemy.dialects.mssql import base
dialect = base.MSDialect()
elif driver == "pyodbc":
from sqlalchemy.dialects.mssql import pyodbc
dialect = pyodbc.dialect()
elif driver == "pymssql":
from sqlalchemy.dialects.mssql import pymssql
dialect = pymssql.dialect()
else:
assert False
if server_version:
dialect.server_version_info = server_version
else:
dialect.server_version_info = MS_2008_VERSION
metadata = MetaData()
typ = type_(*args, **kw)
if use_type_descriptor:
typ = dialect.type_descriptor(typ)
col = Column("date_c", typ, nullable=None)
date_table = Table("test_mssql_dates", metadata, col)
gen = dialect.ddl_compiler(dialect, schema.CreateTable(date_table))
testing.eq_(
gen.get_column_specification(col),
"%s %s" % (
col.name,
res,
),
)
self.assert_(repr(col))
def test_pyodbc_extra_connect(self):
dialect = pyodbc.dialect()
u = url.make_url(
"mssql://*****:*****@hostspec/database?L"
"ANGUAGE=us_english&foo=bar&driver=SQL+Server"
)
connection = dialect.create_connect_args(u)
eq_(connection[1], {})
eq_(
connection[0][0]
in (
"DRIVER={SQL Server};Server=hostspec;Database=database;"
"UID=username;PWD=password;foo=bar;LANGUAGE=us_english",
"DRIVER={SQL Server};Server=hostspec;Database=database;UID="
"username;PWD=password;LANGUAGE=us_english;foo=bar",
),
True,
)
def test_pyodbc_odbc_connect(self):
dialect = pyodbc.dialect()
u = url.make_url(
"mssql:///?odbc_connect=DRIVER%3D%7BSQL+Server"
"%7D%3BServer%3Dhostspec%3BDatabase%3Ddatabase"
"%3BUID%3Dusername%3BPWD%3Dpassword"
)
connection = dialect.create_connect_args(u)
eq_(
[
[
"DRIVER={SQL Server};Server=hostspec;Database=database;UI"
"D=username;PWD=password"
],
{},
],
connection,
)
def test_pyodbc_token_injection(self):
token1 = "someuser%3BPORT%3D50001"
token2 = "some{strange}pw%3BPORT%3D50001"
token3 = "somehost%3BPORT%3D50001"
token4 = "somedb%3BPORT%3D50001"
u = url.make_url("mssql+pyodbc://%s:%s@%s/%s?driver=foob" %
(token1, token2, token3, token4))
dialect = pyodbc.dialect()
connection = dialect.create_connect_args(u)
eq_(
(
("DRIVER={foob};Server=somehost%3BPORT%3D50001;"
"Database=somedb%3BPORT%3D50001;UID={someuser;PORT=50001};"
"PWD={some{strange}}pw;PORT=50001}", ),
{},
),
connection,
)
def test_pyodbc_odbc_connect_ignores_other_values(self):
dialect = pyodbc.dialect()
u = url.make_url(
"mssql://*****:*****@localhost/dbdiff?od"
"bc_connect=DRIVER%3D%7BSQL+Server%7D%3BServer"
"%3Dhostspec%3BDatabase%3Ddatabase%3BUID%3Duse"
"rname%3BPWD%3Dpassword"
)
connection = dialect.create_connect_args(u)
eq_(
[
[
"DRIVER={SQL Server};Server=hostspec;Database=database;UI"
"D=username;PWD=password"
],
{},
],
connection,
)
def test_pyodbc_extra_connect_azure(self):
# issue #5592
dialect = pyodbc.dialect()
u = url.make_url(
"mssql+pyodbc://@server_name/db_name?"
"driver=ODBC+Driver+17+for+SQL+Server;"
"authentication=ActiveDirectoryIntegrated"
)
connection = dialect.create_connect_args(u)
eq_(connection[1], {})
eq_(
connection[0][0]
in (
"DRIVER={ODBC Driver 17 for SQL Server};"
"Server=server_name;Database=db_name;"
"Authentication=ActiveDirectoryIntegrated",
),
True,
)
def test_pyodbc_disconnect(self):
dialect = pyodbc.dialect()
class MockDBAPIError(Exception):
pass
class MockProgrammingError(MockDBAPIError):
pass
dialect.dbapi = Mock(
Error=MockDBAPIError, ProgrammingError=MockProgrammingError
)
for error in [
MockDBAPIError(code, "[%s] some pyodbc message" % code)
for code in [
"08S01",
"01002",
"08003",
"08007",
"08S02",
"08001",
"HYT00",
"HY010",
]
] + [
MockProgrammingError(message)
for message in [
"(some pyodbc stuff) The cursor's connection has been closed.",
"(some pyodbc stuff) Attempt to use a closed connection.",
]
]:
eq_(dialect.is_disconnect(error, None, None), True)
eq_(
dialect.is_disconnect(
MockProgrammingError("Query with abc08007def failed"),
None,
None,
),
False,
)
def test_pyodbc_token_injection(self):
token1 = "someuser%3BPORT%3D50001"
token2 = "somepw%3BPORT%3D50001"
token3 = "somehost%3BPORT%3D50001"
token4 = "somedb%3BPORT%3D50001"
u = url.make_url(
'mssql+pyodbc://%s:%s@%s/%s?driver=foob' % (
token1, token2, token3, token4
)
)
dialect = pyodbc.dialect()
connection = dialect.create_connect_args(u)
eq_(
[[
"DRIVER={foob};Server=somehost%3BPORT%3D50001;"
"Database=somedb%3BPORT%3D50001;UID='someuser;PORT=50001';"
"PWD='somepw;PORT=50001'"], {}],
connection
)
def test_pyodbc_host_no_driver(self):
dialect = pyodbc.dialect()
u = url.make_url("mssql+pyodbc://username:password@hostspec/database")
def go():
return dialect.create_connect_args(u)
connection = assert_warnings(
go,
[
"No driver name specified; this is expected by "
"PyODBC when using DSN-less connections"
],
)
eq_(
(
("Server=hostspec;Database=database;UI"
"D=username;PWD=password", ),
{},
),
connection,
)
def test_pyodbc_empty_url_no_warning(self):
dialect = pyodbc.dialect()
u = url.make_url("mssql+pyodbc://")
# no warning is emitted
dialect.create_connect_args(u)
def test_pyodbc_connect_dsn_non_trusted(self):
dialect = pyodbc.dialect()
u = url.make_url("mssql://*****:*****@mydsn")
connection = dialect.create_connect_args(u)
eq_([["dsn=mydsn;UID=username;PWD=password"], {}], connection)
def test_pyodbc_connect_old_style_dsn_trusted(self):
dialect = pyodbc.dialect()
u = url.make_url("mssql:///?dsn=mydsn")
connection = dialect.create_connect_args(u)
eq_([["dsn=mydsn;Trusted_Connection=Yes"], {}], connection)
def test_pyodbc_connect_dsn_trusted(self):
dialect = pyodbc.dialect()
u = url.make_url("mssql+pyodbc://mydsn")
connection = dialect.create_connect_args(u)
eq_((("dsn=mydsn;Trusted_Connection=Yes", ), {}), connection)
def test_pyodbc_connect_dsn_non_trusted(self):
dialect = pyodbc.dialect()
u = url.make_url('mssql://*****:*****@mydsn')
connection = dialect.create_connect_args(u)
eq_([['dsn=mydsn;UID=username;PWD=password'], {}], connection)
def test_pyodbc_connect_old_style_dsn_trusted(self):
dialect = pyodbc.dialect()
u = url.make_url('mssql:///?dsn=mydsn')
connection = dialect.create_connect_args(u)
eq_([['dsn=mydsn;Trusted_Connection=Yes'], {}], connection)
def test_pyodbc_connect_dsn_trusted(self):
dialect = pyodbc.dialect()
u = url.make_url('mssql://mydsn')
connection = dialect.create_connect_args(u)
eq_([['dsn=mydsn;Trusted_Connection=Yes'], {}], connection)
def test_pyodbc_connect_dsn_trusted(self):
dialect = pyodbc.dialect()
u = url.make_url("mssql://mydsn")
connection = dialect.create_connect_args(u)
eq_([["dsn=mydsn;Trusted_Connection=Yes"], {}], connection)
