def test_binary_output(self): with self.connect() as connection: with connection.cursor() as cursor: sproc = self.test_binary_output.__name__ with self.stored_procedure( cursor, sproc, ''' @pIn VARBINARY(8), @pOut VARBINARY(16) OUTPUT AS SET @pOut = @pIn + @pIn; '''): inputs = (b'\x01\x02\x03\x04', ctds.Parameter(ctds.SqlVarBinary(None, size=16), output=True)) outputs = cursor.callproc(sproc, inputs) self.assertEqual(id(outputs[0]), id(inputs[0])) self.assertEqual(outputs[1], inputs[0] + inputs[0]) self.assertRaises(ctds.InterfaceError, cursor.fetchone) # Note: When the output parameter size is less than the actual data, # SQL Server truncates the data. inputs = (b'\x01\x02\x03\x04', ctds.Parameter(ctds.SqlVarBinary(None, size=2), output=True)) outputs = cursor.callproc(sproc, inputs) self.assertEqual(id(outputs[0]), id(inputs[0])) self.assertEqual(outputs[1], inputs[0][:2]) self.assertRaises(ctds.InterfaceError, cursor.fetchone)
def test_output_with_resultset(self): with self.connect() as connection: with connection.cursor() as cursor: sproc = self.test_output_with_resultset.__name__ with self.stored_procedure( cursor, sproc, ''' @pOut INT OUTPUT AS SET @pOut = 5; SELECT 10; '''): with warnings.catch_warnings(record=True) as warns: cursor.callproc( sproc, (ctds.Parameter(ctds.SqlInt(0), output=True), )) cursor.callproc(sproc, { '@pOut': ctds.Parameter(ctds.SqlInt(0), output=True) }) self.assertEqual(len(warns), 2) self.assertEqual([ str(warn.message) for warn in warns ], [ 'output parameters are not supported with result sets' ] * len(warns)) self.assertEqual(warns[0].category, ctds.Warning)
def test_sql_conversion_warning(self): with self.connect(ansi_defaults=False) as connection: with connection.cursor() as cursor: sproc = self.test_sql_conversion_warning.__name__ with self.stored_procedure( cursor, sproc, ''' @pInt TINYINT OUTPUT, @pBigInt BIGINT OUTPUT AS SELECT @pInt = @pInt * 255; SELECT @pBigInt = @pBigInt - 1000; '''): with warnings.catch_warnings(record=True) as warns: inputs = ( ctds.Parameter(10, output=True), ctds.Parameter(1234, output=True), ) outputs = cursor.callproc(sproc, inputs) self.assertEqual(outputs, (None, inputs[1].value - 1000)) self.assertEqual(len(warns), 1) msg = 'Arithmetic overflow occurred.' self.assertEqual([str(warn.message) for warn in warns], [msg] * len(warns)) self.assertEqual(warns[0].category, ctds.Warning)
def test_parameter(self): param1 = ctds.Parameter(b'123', output=True) self.assertEqual(param1.value, b'123') self.assertTrue(isinstance(param1, ctds.Parameter)) param2 = ctds.Parameter(b'123') self.assertEqual(param1.value, b'123') self.assertEqual(type(param1), type(param2)) self.assertTrue(isinstance(param2, ctds.Parameter))
def test___repr__(self): for parameter, expected in ( (ctds.Parameter(b'123', output=True), "ctds.Parameter(b'123', output=True)" if PY3 else "ctds.Parameter('123', output=True)"), (ctds.Parameter(unicode_('123'), output=False), "ctds.Parameter('123')" if PY3 else "ctds.Parameter(u'123')"), (ctds.Parameter(None), "ctds.Parameter(None)"), (ctds.Parameter(ctds.SqlVarBinary(b'4321', size=10)), "ctds.Parameter(ctds.SqlVarBinary(b'4321', size=10))" if PY3 else "ctds.Parameter(ctds.SqlVarBinary('4321', size=10))")): self.assertEqual(repr(parameter), expected)
def test_dict_outputs(self): with self.connect() as connection: with connection.cursor() as cursor: sproc = self.test_dict.__name__ with self.stored_procedure( cursor, sproc, ''' @pBigInt BIGINT OUTPUT, @pVarChar VARCHAR(32) OUTPUT, @pVarBinary VARBINARY(32) OUTPUT, @pDateTime DATETIME AS SET @pBigInt = @pBigInt * 2; SET @pVarBinary = CONVERT(VARBINARY(32), @pVarChar); SET @pVarChar = CONVERT(VARCHAR(32), @pDateTime, 120); '''): types = [str] if not PY3: # pragma: nocover types.append(unicode_) for type_ in types: inputs = { type_('@pBigInt'): ctds.Parameter(12345, output=True), type_('@pVarChar'): ctds.Parameter( unicode_('hello world, how\'s it going! '), output=True), type_('@pVarBinary'): ctds.Parameter(ctds.SqlVarBinary(None, size=32), output=True), type_('@pDateTime'): datetime(2011, 11, 5, 12, 12, 12) } outputs = cursor.callproc(sproc, inputs) self.assertNotEqual(id(outputs[type_('@pBigInt')]), id(inputs[type_('@pBigInt')])) self.assertNotEqual(id(outputs[type_('@pVarChar')]), id(inputs[type_('@pVarChar')])) self.assertNotEqual(id(outputs[type_('@pVarBinary')]), id(inputs[type_('@pVarBinary')])) self.assertEqual(id(outputs[type_('@pDateTime')]), id(inputs[type_('@pDateTime')])) self.assertEqual(outputs[type_('@pBigInt')], inputs[type_('@pBigInt')].value * 2) self.assertEqual( outputs[type_('@pVarChar')], inputs[type_( '@pDateTime')].strftime('%Y-%m-%d %H:%M:%S')) self.assertEqual( outputs[type_('@pVarBinary')], bytes(inputs[type_('@pVarChar')].value.encode( 'utf-8')))
def test_decimal_outofrange(self): with self.connect() as connection: with connection.cursor() as cursor: sproc = self.test_decimal_outofrange.__name__ with self.stored_procedure( cursor, sproc, ''' @pDecimal DECIMAL(7,3), @pDecimalOut VARCHAR(MAX) OUTPUT AS SET @pDecimalOut = CONVERT(VARCHAR(MAX), @pDecimal); '''): for value in ( 10**38, -10**38, ): inputs = ( Decimal(value), ctds.Parameter(unicode_('*' * 256), output=True), ) try: cursor.callproc(sproc, inputs) except ctds.DataError as ex: self.assertEqual( str(ex), "Decimal('{0}') out of range".format(value)) else: self.fail('.callproc() did not fail as expected' ) # pragma: nocover
def test_varchar_null(self): with self.connect() as connection: with connection.cursor() as cursor: sproc = self.test_varchar_null.__name__ with self.stored_procedure( cursor, sproc, ''' @pVarChar VARCHAR(256), @pIsNullOut BIT OUTPUT AS IF @pVarChar IS NULL BEGIN SET @pIsNullOut = 1; END ELSE BEGIN SET @pIsNullOut = 0; END '''): for value in (None, '', '0', 'one'): inputs = ( value, ctds.Parameter(True, output=True), ) outputs = cursor.callproc(sproc, inputs) # $future: fix this once supported by FreeTDS # Currently FreeTDS (really the db-lib API) will # turn the empty string to NULL self.assertEqual(outputs[1], inputs[0] is None or inputs[0] == '')
def test_decimal_output(self): with self.connect() as connection: with connection.cursor() as cursor: sproc = self.test_decimal_output.__name__ with self.stored_procedure( cursor, sproc, ''' @pString VARCHAR(MAX), @pDecimalOut DECIMAL(15,5) OUTPUT AS SET @pDecimalOut = CONVERT(DECIMAL(15,5), @pString); '''): for value in ('1230.456', '123456789.1234', '123', '1234567890.123456789123456789', None): inputs = ( value, ctds.Parameter(ctds.SqlDecimal(value, precision=15, scale=5), output=True), ) outputs = cursor.callproc(sproc, inputs) self.assertEqual(id(outputs[0]), id(inputs[0])) if outputs[0] is not None: self.assertEqual( Decimal(outputs[0]).quantize( Decimal('.00001')), outputs[1]) else: self.assertEqual(None, outputs[1])
def test_format_named(self): with self.connect(paramstyle='named') as connection: with connection.cursor() as cursor: args = { unicode_('none'): None, 'int': -1234567890, unicode_('bigint'): 2**45, 'bytes': ctds.Parameter(b'1234'), 'byte_array': bytearray('1234', 'ascii'), 'string': unicode_( b'hello \'world\' ' + (b'\xe3\x83\x9b' if self.nchars_supported else b''), encoding='utf-8'), 'datetime': datetime(2001, 1, 1, 12, 13, 14, 150 * 1000), 'decimal': Decimal('123.4567890'), 'money': Decimal('1000000.4532') } query = cursor.executemany( ''' SELECT :none AS none, :int AS int, CONVERT(BIGINT, :bigint) AS bigint, :bytes AS bytes, :byte_array AS bytearray, :string AS string, :string AS string_again, CONVERT(DATETIME, :datetime) AS datetime, :decimal AS decimal, CONVERT(MONEY, :money) AS money ''', (args, args)) self.assertEqual(None, query) while cursor.description: self.assertEqual([tuple(row) for row in cursor.fetchall()], [( args['none'], args['int'], args['bigint'], args['bytes'].value, bytes(args['byte_array']), args['string'], args['string'], args['datetime'], args['decimal'], self.round_money(args['money']), )]) cursor.nextset()
def test_int(self): with self.connect() as connection: with connection.cursor() as cursor: sproc = self.test_int.__name__ with self.stored_procedure( cursor, sproc, ''' @pTinyInt TINYINT, @pSmallInt SMALLINT, @pInt INT, @pBigInt BIGINT, @pTinyIntOut TINYINT OUTPUT, @pSmallIntOut SMALLINT OUTPUT, @pIntOut INT OUTPUT, @pBigIntOut BIGINT OUTPUT AS SELECT @pTinyIntOut = @pTinyInt + 1; SELECT @pSmallIntOut = -1 * @pSmallInt; SELECT @pIntOut = -1 * @pInt; SELECT @pBigIntOut = -1 * @pBigInt; '''): inputs = ( 1, 2**8, 2**15, 2**31, ctds.Parameter(ctds.SqlTinyInt(None), output=True), ctds.Parameter(ctds.SqlSmallInt(None), output=True), ctds.Parameter(ctds.SqlInt(None), output=True), ctds.Parameter(ctds.SqlBigInt(None), output=True), ) outputs = cursor.callproc(sproc, inputs) self.assertRaises(ctds.InterfaceError, cursor.fetchone) self.assertEqual(id(outputs[0]), id(inputs[0])) self.assertEqual(id(outputs[1]), id(inputs[1])) self.assertEqual(id(outputs[2]), id(inputs[2])) self.assertEqual(id(outputs[3]), id(inputs[3])) self.assertEqual(outputs[4:], ( outputs[0] + 1, outputs[1] * -1, outputs[2] * -1, outputs[3] * -1, ))
def test_format_numeric(self): with self.connect() as connection: with connection.cursor() as cursor: args = ( None, -1234567890, 2 ** 45, ctds.Parameter(b'1234'), bytearray('1234', 'ascii'), unicode_( b'hello \'world\' ' + (b'\xe3\x83\x9b' if self.nchars_supported else b''), encoding='utf-8' ), datetime(2001, 1, 1, 12, 13, 14, 150 * 1000), Decimal('123.4567890'), Decimal('1000000.4532') ) query = cursor.execute( ''' SELECT :0 AS none, :1 AS int, CONVERT(BIGINT, :2) AS bigint, :3 AS bytes, :4 AS bytearray, :5 AS string, :5 AS string_again, CONVERT(DATETIME, :6) AS datetime, :7 AS decimal, CONVERT(MONEY, :8) AS money ''', args ) self.assertEqual(None, query) self.assertEqual( tuple(cursor.fetchone()), ( args[0], args[1], args[2], args[3].value, bytes(args[4]), args[5], args[5], args[6], args[7], self.round_money(args[8]), ) )
def test_sql_conversion_error(self): with self.connect(ansi_defaults=True) as connection: with connection.cursor() as cursor: sproc = self.test_sql_conversion_warning.__name__ with self.stored_procedure( cursor, sproc, ''' @pInt TINYINT OUTPUT, @pBigInt BIGINT OUTPUT AS SELECT @pInt = @pInt * 255; SELECT @pBigInt = @pBigInt - 1000; '''): inputs = ( ctds.Parameter(10, output=True), ctds.Parameter(1234, output=True), ) try: cursor.callproc(sproc, inputs) except ctds.ProgrammingError as ex: self.assertEqual( str(ex), 'Arithmetic overflow error for data type tinyint, value = {0}.' .format(inputs[0].value * 255))
def test_binary_inputoutput(self): with self.connect() as connection: with connection.cursor() as cursor: sproc = self.test_binary_inputoutput.__name__ with self.stored_procedure( cursor, sproc, ''' @pInOut VARBINARY(16) OUTPUT AS SET @pInOut = @pInOut + @pInOut; '''): value = b'\x01\x02\x03\x04' inputs = (ctds.Parameter(ctds.SqlVarBinary(value, size=16), output=True), ) outputs = cursor.callproc(sproc, inputs) self.assertEqual(outputs, (value + value, )) self.assertRaises(ctds.InterfaceError, cursor.fetchone)
def test_decimal(self): with self.connect() as connection: with connection.cursor() as cursor: sproc = self.test_decimal.__name__ with self.stored_procedure( cursor, sproc, ''' @pDecimal DECIMAL(7,3), @pDecimalOut VARCHAR(MAX) OUTPUT AS SET @pDecimalOut = CONVERT(VARCHAR(MAX), @pDecimal); '''): for value, string, truncation in ( (1000.0, '1000.000', False), (-1000.0, '-1000.000', False), (9999.999, '9999.999', True), (-9999.999, '-9999.999', True), (.999, '0.999', True), (-.999, '-0.999', True), (.001, '0.001', True), (-.001, '-0.001', True), (0, '0.000', False), ): inputs = ( Decimal(value if PY27 else str(value)), ctds.Parameter(unicode_('*' * 256), output=True), ) with warnings.catch_warnings(record=True) as warns: outputs = cursor.callproc(sproc, inputs) if truncation and PY27: self.assertEqual(len(warns), 1) msg = "Decimal('{0}') exceeds SQL DECIMAL precision; truncating".format( inputs[0]) self.assertEqual( [str(warn.message) for warn in warns], [msg] * len(warns)) self.assertEqual(warns[0].category, ctds.Warning) else: self.assertEqual(len(warns), 0) self.assertEqual(id(outputs[0]), id(inputs[0])) self.assertEqual(string, outputs[1]) self.assertRaises(ctds.InterfaceError, cursor.fetchone)
def test_string(self): parameter = unicode_(b'what DB encoding is used? \xc2\xbd', encoding='utf-8') for index, param in enumerate(( parameter, ctds.Parameter(parameter), )): with self.connect(autocommit=False) as connection: try: with connection.cursor() as cursor: cursor.execute(''' CREATE TABLE {0} ( String VARCHAR(1000) COLLATE SQL_Latin1_General_CP1_CI_AS ) '''.format(self.test_string.__name__)) with warnings.catch_warnings(record=True) as warns: connection.bulk_insert(self.test_string.__name__, [(param, )]) # Python 3.4 and lower has an issue recording the same warning multiple times. # See http://bugs.python.org/issue4180. if index == 0 or PY35: # pragma: nobranch self.assertEqual(len(warns), 1) self.assertEqual([str(warn.message) for warn in warns], [ '''\ Direct bulk insert of a Python str object may result in unexpected character \ encoding. It is recommended to explicitly encode Python str values for bulk \ insert.\ ''' ] * len(warns)) self.assertEqual([warn.category for warn in warns], [Warning] * len(warns)) with connection.cursor() as cursor: cursor.execute('SELECT * FROM {0}'.format( self.test_string.__name__)) self.assertEqual( [tuple(row) for row in cursor.fetchall()], [(parameter.encode('utf-8').decode('latin-1'), )]) finally: connection.rollback()
def test_reuse(self): with self.connect() as connection: with connection.cursor() as cursor: for value in ( None, 123456, unicode_('hello world'), b'some bytes', ): for output in (True, False): parameter = ctds.Parameter(value, output=output) for _ in range(0, 2): # Ignore warnings generated due to output parameters # used with result sets. with warnings.catch_warnings(record=True): cursor.execute( ''' SELECT :0 ''', (parameter, )) self.assertEqual( [tuple(row) for row in cursor.fetchall()], [(value, )])
def test_guid(self): with self.connect() as connection: with connection.cursor() as cursor: sproc = self.test_guid.__name__ with self.stored_procedure( cursor, sproc, ''' @pIn UNIQUEIDENTIFIER, @pOut UNIQUEIDENTIFIER OUTPUT AS SET @pOut = @pIn; '''): for value in ( None, uuid.uuid1(), ): inputs = (value, ctds.Parameter(uuid.uuid1(), output=True)) outputs = cursor.callproc(sproc, inputs) # FreeTDS doesn't support passing raw GUIDs, so they are convereted to # VARCHAR. On output, the type is also VARCHAR. expected = uuid.UUID('{{{0}}}'.format( outputs[1])) if value is not None else None self.assertEqual(value, expected)
def _test__cmp__(self, __cmp__, expected, op): CASES = ( (ctds.Parameter(b'1234'), ctds.Parameter(b'123')), (ctds.Parameter(b'123'), ctds.Parameter(b'123')), (ctds.Parameter(b'123'), ctds.Parameter(b'123', output=True)), (ctds.Parameter(b'123'), ctds.Parameter(b'1234')), (ctds.Parameter(b'123'), b'123'), (ctds.Parameter(b'123'), ctds.Parameter(123)), (ctds.Parameter(b'123'), unicode_('123')), (ctds.Parameter(b'123'), ctds.SqlBinary(None)), (ctds.Parameter(b'123'), 123), (ctds.Parameter(b'123'), None), ) for ix, args in enumerate(CASES): operation = '[{0}]: {1} {2} {3}'.format(ix, repr(args[0]), op, repr(args[1])) if expected[ix] == TypeError: try: result = __cmp__(*args) except TypeError as ex: regex = ( r"'{0}' not supported between instances of '[^']+' and '[^']+'" .format(op) if not PY3 or PY36 else r'unorderable types: \S+ {0} \S+'.format(op)) self.assertTrue(re.match(regex, str(ex)), ex) else: self.fail('{0} did not fail as expected'.format( operation)) # pragma: nocover else: self.assertEqual(__cmp__(*args), expected[ix], operation)
def test_nvarchar(self): with self.connect() as connection: with connection.cursor() as cursor: sproc = self.test_nvarchar.__name__ with self.stored_procedure( cursor, sproc, ''' @pVarChar NVARCHAR(256), @pVarCharOut NVARCHAR(256) OUTPUT AS SET @pVarCharOut = @pVarChar; '''): format_ = (unichr_(191) + unicode_(' 8 ') + unichr_(247) + unicode_(' 2 = 4 ? {0} {1} {2}')) snowman = unichr_(9731) # Python must be built with UCS4 support to test the large codepoints. catface = (unichr_(128568) if self.UCS4_SUPPORTED else self.UNICODE_REPLACEMENT) flower = (unichr_(127802) if self.UCS4_SUPPORTED else self.UNICODE_REPLACEMENT) # Older versions of SQL server don't support passing codepoints outside # of the server's code page. SQL Server defaults to latin-1, so assume # non-latin-1 codepoints won't be supported. if not self.use_sp_executesql: # pragma: nocover catface = unicode_('?') snowman = unicode_('?') flower = unicode_('?') inputs = ( format_.format(snowman, catface, flower), ctds.Parameter(ctds.SqlVarChar(None, size=256), output=True), ) # If the connection supports UTF-16, unicode codepoints outside of the UCS-2 # range are supported and not replaced by ctds. if self.use_utf16: outputs = cursor.callproc(sproc, inputs) self.assertEqual(inputs[0], outputs[1]) else: # pragma: nocover # The catface is not representable in UCS-2, and therefore is replaced. with warnings.catch_warnings(record=True) as warns: outputs = cursor.callproc(sproc, inputs) if ord(catface) > 2**16: self.assertEqual(len(warns), 2) msg = unicode_('Unicode codepoint U+{0:08X} is not representable in UCS-2; replaced with U+{1:04X}') # pylint: disable=line-too-long self.assertEqual( [str(warn.message) for warn in warns], [ msg.format( ord(char), ord(self.UNICODE_REPLACEMENT)) for char in (catface, flower) ]) self.assertEqual(warns[0].category, ctds.Warning) else: self.assertEqual(len(warns), 0) # pragma: nocover self.assertEqual( format_.format( snowman, self.UNICODE_REPLACEMENT if self.use_sp_executesql else unicode_('?'), self.UNICODE_REPLACEMENT if self.use_sp_executesql else unicode_('?')), outputs[1]) self.assertEqual(id(inputs[0]), id(outputs[0])) self.assertNotEqual(id(inputs[1]), id(outputs[1]))