mssql pandas.DataFrame.to_sql AttributeError: 'Engine' object has no attribute 'cursor' #23030
Description
Code Sample, a copy-pastable example if possible
# test data
test = pd.DataFrame({'test':[1,2,3]}) #'te','te','te'
# import modules
import pyodbc
from sqlalchemy import create_engine
import urllib
# test 1
params = urllib.parse.quote_plus(r'DRIVER={SQL Server};SERVER=server_name;DATABASE=db_name;Trusted_Connection=yes')
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(params))
test.to_sql(name='Test',con=engine , schema='input', if_exists='replace', index=False)
# test 2
engine = sqlalchemy.create_engine("mssql+pymssql://server_name/db_name?driver=ODBC+Driver+17+for+SQL+Server?trusted_connection=yes", echo=False)
test.to_sql(name='Test',con=engine , schema='input', if_exists='replace', index=False)Problem description
The above code should result in a new table in my mssql server, however, I keep getting the following error:
`---------------------------------------------------------------------------
AttributeError Traceback (most recent call last)
in ()
8
9
---> 10 test.to_sql(name='Test',con=engine , schema='input', if_exists='replace', index=False)
C:\ProgramData\Anaconda3\envs\test_to_sql\lib\site-packages\pandas\core\generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype)
2128 sql.to_sql(self, name, con, schema=schema, if_exists=if_exists,
2129 index=index, index_label=index_label, chunksize=chunksize,
-> 2130 dtype=dtype)
2131
2132 def to_pickle(self, path, compression='infer',
C:\ProgramData\Anaconda3\envs\test_to_sql\lib\site-packages\pandas\io\sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype)
448 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
449 index_label=index_label, schema=schema,
--> 450 chunksize=chunksize, dtype=dtype)
451
452
C:\ProgramData\Anaconda3\envs\test_to_sql\lib\site-packages\pandas\io\sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
1478 if_exists=if_exists, index_label=index_label,
1479 dtype=dtype)
-> 1480 table.create()
1481 table.insert(chunksize)
1482
C:\ProgramData\Anaconda3\envs\test_to_sql\lib\site-packages\pandas\io\sql.py in create(self)
559
560 def create(self):
--> 561 if self.exists():
562 if self.if_exists == 'fail':
563 raise ValueError("Table '%s' already exists." % self.name)
C:\ProgramData\Anaconda3\envs\test_to_sql\lib\site-packages\pandas\io\sql.py in exists(self)
547
548 def exists(self):
--> 549 return self.pd_sql.has_table(self.name, self.schema)
550
551 def sql_schema(self):
C:\ProgramData\Anaconda3\envs\test_to_sql\lib\site-packages\pandas\io\sql.py in has_table(self, name, schema)
1490 "WHERE type='table' AND name=%s;") % wld
1491
-> 1492 return len(self.execute(query, [name, ]).fetchall()) > 0
1493
1494 def get_table(self, table_name, schema=None):
C:\ProgramData\Anaconda3\envs\test_to_sql\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
1371 cur = self.con
1372 else:
-> 1373 cur = self.con.cursor()
1374 try:
1375 if kwargs:
AttributeError: 'Engine' object has no attribute 'cursor'
`
I've searched high and low and have not been able to find a solution to the problem.
Output of pd.show_versions()
INSTALLED VERSIONS
commit: None
python: 3.6.6.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: Intel64 Family 6 Model 79 Stepping 1, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None
pandas: 0.23.4
pytest: None
pip: 18.1
setuptools: 40.2.0
Cython: None
numpy: 1.15.2
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 6.5.0
sphinx: None
patsy: 0.5.0
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 2.2.3
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: 4.6.3
html5lib: 1.0.1
sqlalchemy: 1.2.11
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None