pythonsqlsql-serverpandassqlalchemy

Incorrect syntax near 'CAST' when using pandas.to_sql

I'm trying to write some code to update an sql table from the values in a pandas dataframe. The code I'm using to do this is:

df.to_sql(name='streamlit_test', con=engine, schema='dbo', if_exists='replace', index=False)

Using an sqlalchemy engine. However, I'm getting the error:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'CAST'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")
[SQL: SELECT cast(com.value as nvarchar(max)) FROM fn_listextendedproperty('MS_Description', 'schema', CAST(? AS NVARCHAR(max)), 'table', CAST(? AS NVARCHAR(max)), NULL, NULL ) as com; ]
[parameters: ('dbo', 'streamlit_test')]

I thought it might be something to do with data types, so I've tried changing the dtypes of my dataframe to match the corresponding data types in the table I'm trying to write to (using this lookup: https://learn.microsoft.com/en-us/sql/machine-learning/python/python-libraries-and-data-types?view=sql-server-ver16) but I'm still getting the same error. I haven't managed to find anyone else with a similar problem through googling, so any help/pointers is greatly appreciated!

Edit I've tried using:

from sqlalchemy.dialects.mssql import BIGINT, FLOAT, TEXT, BIT
df.to_sql(name='streamlit_test', con=engine, schema='dbo',
          if_exists='replace', index=False,
          dtype={'col1':BIGINT, 'col2':FLOAT, 'col3':TEXT,
                            'Tickbox':BIT, 'Comment':TEXT})

so that the dtypes match what they are in the created table, but I still get the same error. For more context, to_sql works fine and saves the dataframe to a table if the table doesn't already exist, and if I change if_exists='replace' to if_exists='append', that also works, but when trying to replace the data in the existing table, I get the 42000 error.

My MSSMS version is: Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Solution

  • Microsoft SQL Server offers limited support for using databases that are compatible with earlier versions. For example, SQL Server 2008 creates databases with compatibility level 100 (SQL Server 2008) by default, but it also allows us to use databases that are compatible with

    SQL Server 2005 - database compatibility level 90
    SQL Server 2000 - database compatibility level 80

    SQLAlchemy 2.0 added a feature to reflect table comments. That feature uses the query

    SELECT cast(com.value as nvarchar(max))
FROM fn_listextendedproperty('MS_Description',
    'schema', CAST(? AS NVARCHAR(max)), 'table', CAST(? AS NVARCHAR(max)), NULL, NULL
) as com;

    and that syntax is only supported for database compatibility level ≥ 90. Executing that same query on a database with compatibility level ≤ 80 results in

    sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'CAST'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

    SQLAlchemy 1.4 is required to work with such old databases, e.g.,

    pip install sqlalchemy==1.4.54 pandas==2.1.4