SA 2.0 Invalid Precision Value Error SQLBindParameter with pyodbc

[webdjoe]

Describe the bug

When upgrading to SQLAlchemy 2.0.0b1 I receive the following error message with PYODBC version 18 with Microsoft SQL Server 2019. This error does not occur with SA 1.4 and does not occur with MySQL using mysqlclient. Tested with python 3.9 and 3.10 on Ubuntu 20.04 in a Docker Container. The SQL Server and python package are running in the same container, but I've tested it on a separate container running Ubuntu 20.04 as well.

Error Message:

pyodbc.Error: ('HY104', '[HY104] [Microsoft][ODBC Driver 18 for SQL Server]Invalid precision value (0) (SQLBindParameter)')

To Reproduce

This occurs when I reflect a database table, create a new temporary table based on the column names and types and then insert the data after being serialized from a DataFrame.

The error occurs in the conn.execute(tmp.insert(), tbl_data) step in the sql_merge() method. I've eliminated some of the unrelated code:

class DBWriter:
    """Instance to store DB metadata and engine."""
    def __init__(self, sql_config: SectionProxy) -> None:
        self.engine: Engine = get_engine(sql_config)
        self.connector = sql_config['connector']
        self.dialect = self.connector.split('+')[0]
        self.sql_config = sql_config
        self.schema: str = sql_config.get('schema', 'dbo')
        if self.dialect == 'mssql':
            self.meta: MetaData = MetaData(schema=self.schema)
        self.tmp_meta: MetaData = MetaData()
        self.db: str = sql_config['database']

    @staticmethod
    def sql_arrange(df: DataFrame, col_list: list) -> list:
        """Arrange dataframe columns to match SQL columns."""
        df = df.reindex(columns=col_list)
        tbl_data = json.loads(df.to_json(
            orient='records', date_format='iso'))
        return tbl_data

    def sql_merge(self, data: dict, j: int = 0) -> bool:
        """Merge a dictionary of dataframes into an SQL table."""
        for k, v in data.items():
            merge_dict = MergeDict[k]
            if self.dialect == 'mssql':
                self.engine.execution_options(
                    schema_translate_map={None: self.schema})
            with self.engine.begin() as conn:
                tbl: Table = Table(k, self.meta, autoload_with=self.engine)
                tbl_data = self.sql_arrange(v, tbl.c.keys())
                if self.dialect == 'mssql':
                    tmp = self.temp_table(tbl.c)
                    tmp.create(conn)
                    conn.execute(tmp.insert(), tbl_data)
                    merge_qry = merge_str(self.db, self.schema, k,
                                          tmp.c.keys(), **merge_dict)  # type: ignore
                    conn.execute(text(merge_qry))
                    tmp.drop(conn)
                    self.tmp_meta.remove(tmp)
        return True

    def temp_table(self, cols: sa.sql.expression.ColumnCollection) -> Table:
        """Create temporary table based on dialect"""
        new_cols = [Column(col.name, col.type) for col in cols]
        if self.dialect == 'mssql':
            tbl_name = "#tmp"
            return Table(tbl_name, self.tmp_meta, *new_cols)

The table that is being reflected is based on this model but I do not copy the primary key to the new table, only the column name and type:

LARGE_UNICODE = Text().with_variant(NVARCHAR(None), 'mssql')

class products(Base):
    """Declarative model class to hold the products table."""
    __tablename__ = 'products'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='PK_products'),
    )

    id = Column(BigInteger, primary_key=True, autoincrement=False)
    created_at = Column(DateTime, nullable=False)
    updated_at = Column(DateTime, nullable=False)
    title = Column(Unicode(255))
    body_html = Column(LARGE_UNICODE)
    vendor = Column(Unicode(255))
    product_type = Column(Unicode(255))
    handle = Column(Unicode(255))
    published_at = Column(DateTime)
    template_suffix = Column(Unicode(255))
    status = Column(Unicode(255))
    published_scope = Column(Unicode(255))
    tags = Column(LARGE_UNICODE)
    admin_graphql_api_id = Column(Unicode(255))
    image_src = Column(Unicode(255))

Error

Traceback (most recent call last):
  File "/root/venv/lib/python3.9/site-packages/SQLAlchemy-2.0.0b1-py3.9-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2078, in _exec_insertmany_context
    dialect.do_execute(cursor, sub_stmt, sub_params, context)
  File "/root/venv/lib/python3.9/site-packages/SQLAlchemy-2.0.0b1-py3.9-linux-x86_64.egg/sqlalchemy/engine/default.py", line 744, in do_execute
    cursor.execute(statement, parameters)
pyodbc.Error: ('HY104', '[HY104] [Microsoft][ODBC Driver 18 for SQL Server]Invalid precision value (0) (SQLBindParameter)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/root/venv/bin/shopify_cli", line 11, in <module>
    load_entry_point('pyshopify==0.9.9', 'console_scripts', 'shopify_cli')()
  File "/root/venv/lib/python3.9/site-packages/click-8.1.3-py3.9.egg/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/root/venv/lib/python3.9/site-packages/click-8.1.3-py3.9.egg/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/root/venv/lib/python3.9/site-packages/click-8.1.3-py3.9.egg/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/root/venv/lib/python3.9/site-packages/click-8.1.3-py3.9.egg/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/root/venv/lib/python3.9/site-packages/pyshopify-0.9.9-py3.9.egg/pyshopify/cli.py", line 74, in cli_runner
  File "/root/venv/lib/python3.9/site-packages/pyshopify-0.9.9-py3.9.egg/pyshopify/runner.py", line 490, in write_all_to_sql
  File "/root/venv/lib/python3.9/site-packages/pyshopify-0.9.9-py3.9.egg/pyshopify/runner.py", line 545, in products_writer
  File "/root/venv/lib/python3.9/site-packages/pyshopify-0.9.9-py3.9.egg/pyshopify/runner.py", line 363, in __data_writer
  File "/root/venv/lib/python3.9/site-packages/pyshopify-0.9.9-py3.9.egg/pyshopify/runner.py", line 838, in __sql_writer
  File "/root/venv/lib/python3.9/site-packages/pyshopify-0.9.9-py3.9.egg/pyshopify/sql.py", line 476, in sql_merge
  File "/root/venv/lib/python3.9/site-packages/SQLAlchemy-2.0.0b1-py3.9-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1392, in execute
    return meth(
  File "/root/venv/lib/python3.9/site-packages/SQLAlchemy-2.0.0b1-py3.9-linux-x86_64.egg/sqlalchemy/sql/elements.py", line 487, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/root/venv/lib/python3.9/site-packages/SQLAlchemy-2.0.0b1-py3.9-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1616, in _execute_clauseelement
    ret = self._execute_context(
  File "/root/venv/lib/python3.9/site-packages/SQLAlchemy-2.0.0b1-py3.9-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1815, in _execute_context
    return self._exec_insertmany_context(
  File "/root/venv/lib/python3.9/site-packages/SQLAlchemy-2.0.0b1-py3.9-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2081, in _exec_insertmany_context
    self._handle_dbapi_exception(
  File "/root/venv/lib/python3.9/site-packages/SQLAlchemy-2.0.0b1-py3.9-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2303, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/root/venv/lib/python3.9/site-packages/SQLAlchemy-2.0.0b1-py3.9-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2078, in _exec_insertmany_context
    dialect.do_execute(cursor, sub_stmt, sub_params, context)
  File "/root/venv/lib/python3.9/site-packages/SQLAlchemy-2.0.0b1-py3.9-linux-x86_64.egg/sqlalchemy/engine/default.py", line 744, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY104', '[HY104] [Microsoft][ODBC Driver 18 for SQL Server]Invalid precision value (0) (SQLBindParameter)')
[SQL: INSERT INTO [#tmp] (id, created_at, updated_at, title, body_html, vendor, product_type, handle, published_at, template_suffix, status, published_scope, tags, admin_graphql_api_id, image_src) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ? ... 880 characters truncated ...  ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)] [ PARAMETERS REMOVED FOR BREVITY ]

Versions

• OS: Ubuntu 20.04
• Python: 3.9 & 3.10
• SQLAlchemy: 2.0.0b1
• Database: Microsoft SQL Server 2019
• DBAPI (eg: psycopg, cx_oracle, mysqlclient): pyodbc 18

Additional context

No response

[zzzeek]

I would need better information here to be able to act upon this. however the issue is likely due to https://docs.sqlalchemy.org/en/20/changelog/changelog_20.html#change-66bddfbbce3c9e37214e8c5864ab1fad . steps to take:

1. confirm the issue goes away when you create_engine like this:

e = create_engine("mssql+pyodbc://...", use_setinputsizes=False)

2. please provide the CREATE TABLE statement for #tmp. Your code example is not runnable and I can't see what the table definiition looks like.

3. provide a sample of actual data in the statement shown, where you have "[ PARAMETERS REMOVED FOR BREVITY ]", I actually need those to understand what kind of data this is. in particular, the lengths of strings is very important, as are things like decimal or floating point values, integers, and how many significant digits they have, timestamps and what kind of fractional seconds they might have. all of it's important, need it all. thanks

[zzzeek]

oh additionally, it looks like your message is identical to what we just fixed at #8661. so it might be that simple, please run the latest github main of SQLAlchemy to confirm that fixes this issue.

[webdjoe]

@zzzeek Thank you for the quick response. I can confirm setting e = create_engine("mssql+pyodbc://...", use_setinputsizes=False) does fix the problem as well as installing the main branch (without use_setinputsizes set). Let me know if there is another test you would like me to run.

For your reference, that products declarative base class does generate the table but here is the create table command:

CREATE TABLE [dbo].[products12](
	[id] [bigint] NOT NULL,
	[created_at] [datetime] NOT NULL,
	[updated_at] [datetime] NOT NULL,
	[title] [nvarchar](255) NULL,
	[body_html] [nvarchar](max) NULL,
	[vendor] [nvarchar](255) NULL,
	[product_type] [nvarchar](255) NULL,
	[handle] [nvarchar](255) NULL,
	[published_at] [datetime] NULL,
	[template_suffix] [nvarchar](255) NULL,
	[status] [nvarchar](255) NULL,
	[published_scope] [nvarchar](255) NULL,
	[tags] [nvarchar](max) NULL,
	[admin_graphql_api_id] [nvarchar](255) NULL,
	[image_src] [nvarchar](255) NULL,
 CONSTRAINT [PK_product65s] PRIMARY KEY CLUSTERED 
(
	[id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The only numerical column is the bigint, the rest are all unicode text based. The largest column is body_html with the biggest string length being 22k. I'm guessing it was this NVARCHAR(max) column causing the issue. I can provide the full dataset in a shared file if that is helpful.