def test_error_login(self):
for username, password in (
(self.get_option('user'), self.get_option('password') + 'invalid'),
(self.get_option('user') + 'invalid', self.get_option('password')),
):
try:
ctds.connect(self.get_option('server'),
user=username,
password=password,
tds_version='7.1')
except ctds.OperationalError as ex:
msg = "Login failed for user '{0}'.".format(username)
self.assertEqual(str(ex), msg)
self.assertEqual(ex.severity, 9)
self.assertEqual(ex.db_error['number'], 20002)
# FreeTDS version 0.95+ adds a (<host>:<port) to this error.
self.assertTrue('Adaptive Server connection failed' in
ex.db_error['description'])
self.assertEqual(ex.os_error, None)
self.assertTrue(self.server_name_and_instance in
ex.last_message.pop('server'))
self.assertEqual(
ex.last_message, {
'description': msg,
'line': 1,
'number': 18456,
'proc': '',
'severity': 14,
'state': 1
})
else:
self.fail(
'.connect() did not fail as expected') # pragma: nocover
def test_error_unavailable(self):
host = '127.0.0.1' # use an IP to avoid DNS lookup timeouts
try:
ctds.connect(
host,
login_timeout=1,
tds_version='7.1',
port=self.get_option('port', int) + 1000
)
except ctds.OperationalError as ex:
# FreeTDS version 0.95+ adds a (<host>:<port) to this error.
self.assertTrue(
'Unable to connect: Adaptive Server is unavailable or does not exist' in str(ex)
)
self.assertEqual(ex.severity, 9)
self.assertEqual(ex.db_error['number'], 20009)
self.assertTrue(
'Unable to connect: Adaptive Server is unavailable or does not exist' in ex.db_error['description']
)
# Sepcific errors vary by platform and FreeTDS version.
self.assertTrue(isinstance(ex.os_error['description'], unicode_))
self.assertTrue(isinstance(ex.os_error['number'], long_))
self.assertEqual(ex.last_message, None)
else:
self.fail('.connect() did not fail as expected') # pragma: nocover
def test_error_unavailable(self):
host = '127.0.0.1' # use an IP to avoid DNS lookup timeouts
try:
ctds.connect(
host,
login_timeout=1,
tds_version='7.1'
)
except ctds.OperationalError as ex:
# FreeTDS version 0.95+ adds a (<host>:<port) to this error.
self.assertTrue(
'Unable to connect: Adaptive Server is unavailable or does not exist' in str(ex)
)
self.assertEqual(ex.severity, 9)
self.assertEqual(ex.db_error['number'], 20009)
self.assertTrue(
'Unable to connect: Adaptive Server is unavailable or does not exist' in ex.db_error['description']
)
# Sepcific errors vary by platform and FreeTDS version.
self.assertTrue(isinstance(ex.os_error['description'], unicode_))
self.assertTrue(isinstance(ex.os_error['number'], long_))
self.assertEqual(ex.last_message, None)
else:
self.fail('.connect() did not fail as expected') # pragma: nocover
def connect(self, **kwargs):
'''Connect to the database using parameters defined in the config.
'''
kwargs_ = dict( # pylint: disable=consider-using-dict-comprehension
[
(key, self.get_option(key, type_))
for key, type_ in (
('appname', str),
('autocommit', bool),
('database', str),
('instance', str),
('login_timeout', int),
('password', str),
('port', int),
('tds_version', str),
('timeout', int),
('user', str),
)
]
)
kwargs_.update(kwargs)
kwargs_.setdefault('appname', 'egg.tds.unittest')
return ctds.connect(
self.get_option('server'),
**kwargs_
)
def connect(self, **kwargs):
'''Connect to the database using parameters defined in the config.
'''
kwargs_ = dict( # pylint: disable=consider-using-dict-comprehension
[
(key, self.get_option(key, type_))
for key, type_ in (
('appname', str),
('autocommit', bool),
('database', str),
('instance', str),
('login_timeout', int),
('password', str),
('port', int),
('tds_version', str),
('timeout', int),
('user', str),
)
]
)
kwargs_.update(kwargs)
kwargs_.setdefault('appname', 'egg.tds.unittest')
return ctds.connect(
self.get_option('server'),
**kwargs_
)
def connect(self, **kwargs):
'''Connect to the database using parameters defined in the config.
'''
kwargs_ = dict(
[
(key, self.get_option(key, type_))
for key, type_ in (
('autocommit', bool),
('database', str),
('instance', str),
('login_timeout', int),
('password', str),
('port', int),
('tds_version', str),
('timeout', int),
('user', str),
)
]
)
kwargs_.update(kwargs)
return ctds.connect(
self.get_option('server'),
appname='egg.tds.unittest',
**kwargs_
)
def _ctds_connect(ctds, params):
params = params.copy()
params["server"] = params.pop("host")
params["user"] = params.pop("username")
# Default timeout is 5 seconds. We don't want queries to timeout at all so
# set to one week
params["timeout"] = 7 * 24 * 60 * 60
params["autocommit"] = True
return ctds.connect(**params)
def test_typeerror(self):
def string_case(name):
cases = [
(('127.0.0.1',), {name: 1234}),
(('127.0.0.1',), {name: object()}),
]
if PY3: # pragma: nocover
cases.append((('127.0.0.1',), {name: b'1234'}))
return cases
def uint_case(name):
return [
(('127.0.0.1',), {name: '1234'}),
(('127.0.0.1',), {name: unicode_('1234')}),
(('127.0.0.1',), {name: b'1234'}),
(('127.0.0.1',), {name: None}),
(('127.0.0.1',), {name: object()}),
]
def bool_case(name):
return [
(('127.0.0.1',), {name: 'False'}),
(('127.0.0.1',), {name: 0}),
(('127.0.0.1',), {name: 1}),
(('127.0.0.1',), {name: None}),
]
cases = (
[
((None,), {}),
((1,), {},),
] +
uint_case('port') +
string_case('instance') +
string_case('user') +
string_case('password') +
string_case('database') +
string_case('appname') +
string_case('hostname') +
uint_case('login_timeout') +
uint_case('timeout') +
string_case('tds_version') +
bool_case('autocommit') +
bool_case('ansi_defaults') +
bool_case('enable_bcp') +
string_case('paramstyle') +
bool_case('read_only') +
bool_case('ntlmv2')
)
for args, kwargs in cases:
try:
connection = ctds.connect(*args, **kwargs)
connection.close() # pragma: nocover
except TypeError:
pass
else:
self.fail('.connect() did not fail as expected') # pragma: nocover
def test_paramstyle(self):
for paramstyle in ('qmark', 'NUMERIC', 'nAmed', 'unknown'):
try:
connection = ctds.connect('hostname', paramstyle=paramstyle)
connection.close() # pragma: nocover
except ctds.InterfaceError as ex:
self.assertEqual(
str(ex), 'unsupported paramstyle "{0}"'.format(paramstyle))
else:
self.fail(
'.connect() did not fail as expected') # pragma: nocover
def test_tds_version(self):
for tds_version in ('7', '7.13', '7.30'):
try:
connection = ctds.connect('hostname', tds_version=tds_version)
connection.close() # pragma: nocover
except ctds.InterfaceError as ex:
self.assertEqual(
str(ex),
'unsupported TDS version "{0}"'.format(tds_version))
else:
self.fail(
'.connect() did not fail as expected') # pragma: nocover
def test_valueerror(self):
for kwargs in (
{'user': '******' * 256},
{'password': '******' * 256},
{'appname': '*' * 256}
):
try:
connection = ctds.connect('hostname', **kwargs)
connection.close() # pragma: nocover
except ValueError as ex:
self.assertEqual(str(ex), next(iter(kwargs.values())))
else:
self.fail('.connect() did not fail as expected') # pragma: nocover
def test_tds_version(self):
for tds_version in (
'7',
'7.13',
'7.30'
):
try:
connection = ctds.connect('hostname', tds_version=tds_version)
connection.close() # pragma: nocover
except ctds.InterfaceError as ex:
self.assertEqual(str(ex), 'unsupported TDS version "{0}"'.format(tds_version))
else:
self.fail('.connect() did not fail as expected') # pragma: nocover
def test_error_login(self):
for username, password in (
(self.get_option('user'), self.get_option('password') + 'invalid'),
(self.get_option('user') + 'invalid', self.get_option('password')),
):
try:
ctds.connect(
self.get_option('server'),
port=self.get_option('port', type_=int),
instance=self.get_option('instance'),
user=username,
password=password,
tds_version='7.1'
)
except ctds.OperationalError as ex:
msg = "Login failed for user '{0}'.".format(username)
self.assertEqual(
str(ex),
msg
)
self.assertEqual(ex.severity, 9)
self.assertEqual(ex.db_error['number'], 20002)
# FreeTDS version 0.95+ adds a (<host>:<port) to this error.
self.assertTrue(
'Adaptive Server connection failed' in ex.db_error['description']
)
self.assertEqual(ex.os_error, None)
self.assertTrue(self.server_name_and_instance in ex.last_message.pop('server'))
self.assertEqual(ex.last_message, {
'description': msg,
'line': 1,
'number': 18456,
'proc': '',
'severity': 14,
'state': 1
})
else:
self.fail('.connect() did not fail as expected') # pragma: nocover
def test_interfaceerror(self):
for kwargs in (
{'user': '******' * 256},
{'password': '******' * 256},
{'appname': '*' * 256},
{'hostname': '*' * 256},
):
try:
connection = ctds.connect('hostname', **kwargs)
connection.close() # pragma: nocover
except ctds.InterfaceError as ex:
self.assertEqual(str(ex), next(iter(kwargs.values())))
else:
self.fail('.connect() did not fail as expected') # pragma: nocover
def test_paramstyle(self):
for paramstyle in (
'qmark',
'NUMERIC',
'nAmed',
'unknown'
):
try:
connection = ctds.connect('hostname', paramstyle=paramstyle)
connection.close() # pragma: nocover
except ctds.InterfaceError as ex:
self.assertEqual(str(ex), 'unsupported paramstyle "{0}"'.format(paramstyle))
else:
self.fail('.connect() did not fail as expected') # pragma: nocover
def get_ctds_conn(self):
"""
Returns a mssql connection object
https://pypi.org/project/ctds/
"""
db = self.get_connection(self.mssql_conn_id)
conn = ctds.connect(server=db.host,
user=db.login,
password=db.password,
database=self.schema or db.schema,
port=db.port,
login_timeout=30,
timeout=60 * 60,
autocommit=True,
paramstyle='named')
return conn
import pandas as pd
import mysql.connector as sql
from sqlalchemy import create_engine, text
from sqlalchemy import delete
from datetime import datetime
import time
import urllib
import ctds
#AZURE SQL SERVER CONNECTION
params = urllib.quote_plus("DRIVER={ODBC Driver 17 for SQL Server};SERVER=mbslbiserver.database.windows.net;DATABASE=mbsldwh_dev;UID=Reports;PWD=mbsl1234!")
engineAzure = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
df_Migration_Agg = pd.read_sql_query("""SELECT top 10 * FROM Migration_Agg """, engineAzure )
conn = ctds.connect('mbslbiserver.database.windows.net', user='******', password='******', database='mbsldwh_dev')
conn.bulk_insert('Migration_Agg', (df_Migration_Agg.to_records(index=False).tolist()))
