pyodbc 5.5.0

5.5.0: DECIMAL fixes for PostgreSQL, and crash and memory-safety fixes

on Python PyPI

Version 5.5.0 has been released with wheels for Python 3.11 - 3.15

This release follows 5.4.0 closely. It fixes DECIMAL and NUMERIC values that 5.4.0 and earlier
versions read or wrote incorrectly, mostly with PostgreSQL. It also fixes crashes and
memory-safety bugs found in a review of the C++ code prompted by #1515. Most of the
memory-safety bugs need an unusual setting, value, or driver to reach, but we recommend that
everyone upgrade. It is 5.5.0 rather than 5.4.1 because it adds Connection.decimal_mode;
there will be no 5.4.1.

Check your data

• Writing a Decimal NaN or Infinity stored 0. In 5.4.0 and earlier, Cursor.execute()
  with a Decimal('NaN'), Decimal('sNaN'), Decimal('Infinity'), or Decimal('-Infinity')
  parameter raised SystemError, but only after the statement had run with the value 0. If
  such a value could have reached an insert or update that was then committed (with autocommit
  on, for example), the table may hold zeros where NaN or Infinity was intended.
• PostgreSQL: some values read wrongly in 5.4.0. NaN, Infinity, and -Infinity were
  read as Decimal('0'), and values with more than 38 digits or more than 38 decimal places
  were read as a different number. The values in the database are correct, but values read
  with 5.4.0 and then stored or used elsewhere may not be. (5.3.0 raised an exception for NaN
  and Infinity, and read long values correctly.)

Upgrading from 5.4.0

• If you followed the 5.4.0 notes and set Connection.fetch_decimal_as_string = True, it still
  works, but it now raises a FutureWarning, and it will be removed in 6.0. Use
  Connection.decimal_mode instead:

  • PostgreSQL: remove the line. The new default reads every value correctly. True
    selects the old text path, which cannot read NaN or Infinity, and False selects
    'binary', which reads them as 0.
  • Other databases: decimal_mode = 'locale' behaves exactly like
    fetch_decimal_as_string = True. If your driver never adds thousands separators,
    decimal_mode = 'text' is stricter and is not affected by the locale.

• Reading fetch_decimal_as_string also raises FutureWarning. To silence it until you
  update your code:

  warnings.filterwarnings("ignore", message=".*fetch_decimal_as_string", category=FutureWarning)

New features

• Connection.decimal_mode selects how DECIMAL and NUMERIC values are read:
  • 'binary': into a SQL_NUMERIC_STRUCT, which is not affected by the locale. This is the
    5.4.0 behavior and the default except for PostgreSQL. (With PostgreSQL it reads NaN and
    Infinity as 0.)
  • 'text': as text from drivers that never add thousands separators. The decimal point can
    be . or ,, whatever the locale, and NaN, Infinity, and -Infinity are read.
    Anything else raises DataError rather than being guessed at. This is the default for
    PostgreSQL.
  • 'locale': the text path from before 5.4, which removes thousands separators and currency
    symbols using setDecimalSeparator().
• PostgreSQL: Decimal NaN, Infinity, and -Infinity can be written with Cursor.execute().
  They are sent as text, so databases that can store them do; others raise their own error
  (SQL Server raises DataError). fast_executemany and table-valued parameters cannot send
  them and raise ProgrammingError, as does a signaling NaN.

Behavior changes

• PostgreSQL connections read DECIMAL and NUMERIC as text by default (decimal_mode == 'text').
  Reads are as fast as with the SQL_NUMERIC_STRUCT.
• Connection.fetch_decimal_as_string is deprecated; see "Upgrading from 5.4.0".
• Decimal NaN and Infinity parameters are sent to the database instead of raising
  SystemError; see above.
• With decimal_mode = 'binary', a value the driver reports does not fit a
  SQL_NUMERIC_STRUCT raises DataError instead of being returned as a wrong number. With
  PostgreSQL this is a value with more than 38 digits, counting the column's decimal places,
  so a column such as numeric(60, 45) cannot be read in binary mode. Use 'text', the
  PostgreSQL default.
• setinputsizes() with a (sqltype, size) tuple and no scale keeps the parameter's scale
  instead of using the size as the scale, so (SQL_DECIMAL, 18) no longer binds as
  decimal(18, 18) and rejects values like 123.
• fast_executemany raises ProgrammingError for a Decimal or int with more than 38
  digits bound as a numeric. 5.4.0 sent 39-digit values to the driver.
• Binding a Decimal with more than 32767 digits after the decimal point raises
  ProgrammingError.
• statistics() and tablePrivileges() raise ValueError for a name longer than 32767
  characters, and execute() raises ValueError for a statement longer than 2147483647
  characters. These lengths were passed to the driver truncated.
• Decimal and UUID subclasses whose as_tuple() or bytes_le return invalid values raise
  TypeError.
• Comparing rows raises any error from comparing their values, instead of ignoring it.
• connect() with a connection string that is not a str says "argument 1 must be a str".

Bug fixes

• PostgreSQL: fast_executemany raised "Converting decimal loses precision" for any Decimal
  with more than 6 decimal places, and for fractional values after the same cursor had run the
  statement with a whole number. The precision and scale now come from the values, including
  when setinputsizes() gives a size but no scale.
• The text path no longer misreads values when the driver's decimal point differs from
  pyodbc's separator, for example after locale.setlocale() is called after importing pyodbc
  (#753). This needs decimal_mode = 'text', the PostgreSQL default.
• After an error while sending long (data-at-execution) parameters, the cursor can be used
  again instead of failing with "Function sequence error".
• With setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-8'), catalog functions such as
  columns() returned no rows.
• setDecimalSeparator() with a character that is special in regular expressions, such as ],
  broke reading numbers with decimal_mode = 'locale'.
• A Connection.readvar_initsize of 1 or any odd number made fetching text fail or return
  wrong text, and with the Microsoft driver a value of 1 hung.
• Text parameters of 2 GB or more were sent with a truncated length.
• An attrs_before key too large for an ODBC attribute ID set a different attribute; for
  example, 2**40 + 102 set autocommit (102). Such keys now raise OverflowError.
• Some errors were reported as SystemError instead of the real exception:
  • catalog functions with a name that cannot be encoded (now UnicodeEncodeError)
  • nextset() on a closed cursor (now ProgrammingError)
  • a TVP Decimal column with a value that is not a Decimal, on some Python versions (now
    TypeError)
  • queries after del pyodbc.lowercase or del pyodbc.native_uuid (the defaults are now
    used)
• nextset() returned an exception instead of raising it when the driver could not report the
  next result's columns.
• Fix a memory leak when reading a text or binary column fails.

Crash fixes

These could be triggered by ordinary Python code:

• Reusing a fast_executemany cursor after an error while sending long parameters
• fast_executemany with Decimal NaN or Infinity, a Decimal with a very large exponent, or
  a negative scale from the driver (the last by @ziyziy89 in
  #1515)
• Table-valued parameters with non-str type or schema names, an empty first row, rows that
  change size during execution, or rows and values from sequences that create new objects
  (such as range)
• del row[i] and del row.name, which now raise TypeError
• Unpickling a Row with an inconsistent column map
• Catalog functions such as tables() and columns() on a closed cursor
• fetchval() with rows_as_dicts
• Fetching after a result's column names could not be decoded
• connect() with an encoding that cannot be encoded, or unusual attrs_before values
• Connection.set_attr() with values that cannot be encoded or are too large
• TimeFromTicks() with a float or an out-of-range value (out-of-range values now raise
  OverflowError)
• connect() after del pyodbc.pooling or del pyodbc.odbcversion

Memory-safety fixes

Reads or writes outside a buffer, or use of freed memory. Each needs an unusual setting,
value, driver, or subclass to reach. The review that found these and the crashes above is in
#1521, and what it found but 5.5.0 does not fix is in #1522.

• Connection.readvar_initsize = 1, which let the driver write before the start of the buffer
• Drivers that report a longer length than the buffer they were given when reading text or
  binary columns, getinfo() strings, searchescape, error messages, or Cursor.messages,
  and, on Windows, dataSources()
• Error messages and Cursor.messages with a driver manager that uses a 4-byte SQLWCHAR
• Catalog function names with setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-8'), and names
  of exactly 65533 characters passed to statistics() or tablePrivileges(), which the driver
  read past the end of
• attrs_before string values, which the driver manager could read past the end of
• A bytearray parameter resized by another thread while a statement runs; bytearray
  values are now copied when bound
• Decimal and UUID subclasses whose as_tuple() or bytes_le return invalid values
• A Decimal with an extremely large negative exponent sent with execute()
• fast_executemany batches large enough to overflow the buffer size
• A locale whose decimal point is not a Latin-1 character
• A driver reporting a sql_variant nested in a sql_variant
• Reading GUID columns with pyodbc.native_uuid = True, which passed a length of the wrong
  size and can fail on 64-bit platforms

Performance

• Reading DECIMAL and NUMERIC with decimal_mode = 'binary' takes about 20% less time.

Thanks

• @ziyziy89 for the fast_executemany negative scale fix.

Full Changelog: 5.4.0...5.5.0