BUG: Pandas to_sql 'Engine' object has no attribute 'cursor'

[ganbaaelmer]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

from tqdm import tqdm
from datetime import datetime
import sys
from azure.storage.blob import ContainerClient
from azure.core.exceptions import ResourceNotFoundError, ClientAuthenticationError
from azure.identity import ClientSecretCredential
import re
from dotenv import load_dotenv
import pickle
import io
import pandas as pd
from sqlalchemy import create_engine
import urllib
import logging
import sys
import time
import sqlalchemy
from typing import Sequence, Hashable
from collections import defaultdict
import os
from azure.storage.blob import BlobServiceClient
from sqlalchemy import text
import numpy as np
from sqlalchemy.dialects import mssql
import polars as pl
import csv
import pandas as pd
import io
import os
from datetime import datetime
import tqdm as tqdm
import sqlalchemy
from sqlalchemy import create_engine, inspect

from sqlalchemy import create_engine
from sqlalchemy.engine import URL
import pandas as pd


load_dotenv()
SERVER = os.environ.get("SERVER")
DATABASE = os.environ.get("DATABASE")
UID = os.environ.get("UID")
PASSWORD = os.environ.get("PASSWORD")

# Construct the connection string for Azure SQL Server
params = urllib.parse.quote_plus(
    'DRIVER={ODBC Driver 18 for SQL Server};'
    f'SERVER={SERVER};'
    f'DATABASE={DATABASE};'
    f'UID={UID};'
    f'PWD={PASSWORD}'
)

engine = create_engine(f'mssql+pyodbc:///?odbc_connect={params}')
df.to_sql(custom_table_name, 
                            con=engine, 
                            if_exists = 'append',  # Options: 'fail', 'replace', 'append'
                            index = False,         # Set to True if you want to include index as a column
                            #dtype = outputdict_dtype 
                            )

Issue Description

BUG: Pandas to_sql 'Engine' object has no attribute 'cursor'

Expected Behavior

BUG: Pandas to_sql 'Engine' object has no attribute 'cursor'

Installed Versions

INSTALLED VERSIONS

commit : 0691c5c
python : 3.12.4
python-bits : 64
OS : Windows
OS-release : 11
Version : 10.0.26100
machine : AMD64
processor : Intel64 Family 6 Model 151 Stepping 5, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : English_United States.1252

pandas : 2.2.3
numpy : 1.26.0
pytz : 2024.2
dateutil : 2.9.0.post0
pip : 24.2
Cython : None
sphinx : 7.3.7
IPython : 8.27.0
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.3
blosc : None
bottleneck : 1.3.7
dataframe-api-compat : None
fastparquet : None
fsspec : 2024.9.0
html5lib : None
hypothesis : None
gcsfs : None
jinja2 : 3.1.4
lxml.etree : 5.2.1
matplotlib : 3.9.2
numba : 0.60.0
numexpr : 2.8.7
odfpy : None
openpyxl : 3.1.5
pandas_gbq : 0.24.0
psycopg2 : 2.9.10
pymysql : 1.4.6
pyarrow : 14.0.2
pyreadstat : None
pytest : 7.4.4
python-calamine : None
pyxlsb : None
s3fs : 2024.6.1
scipy : 1.13.1
sqlalchemy : None
tables : 3.10.1
tabulate : 0.9.0
xarray : 2023.6.0
xlrd : None
xlsxwriter : 3.2.0
zstandard : 0.22.0
tzdata : 2024.1
qtpy : 2.4.1
pyqt5 : None

[asishm]

What's your sqlalchemy version? pandas now requires sqlalchemy>2.

[mfebrizio]

This is related to #58949, which was closed.

For OP, I believe adjusting your code like this solves the issue:

with engine.connect() as conn:
    df.to_sql(..., con=conn.connection, ...)

[ganbaaelmer]

@mfebrizio
i changed as you suggested then new error rises:
SELECT
name
FROM
sqlite_master
WHERE
type IN ('table', 'view')
AND name=?;
': ('42S02', "[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW)"); 4743)

[fabclmnt]

@ganbaaelmer I had the same issue, and as mentioned by @asishm using sqlalchemy>2 solved this for me.

[RafaelTarachuck]

I did the same as @fabclmnt , and that also solved my problem.