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()))