Description
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
polars as pl
query = "SELECT getdate(), t.* FROM table "
df_sql = pl.read_sql(query, conn_uri_db1)`
df_sql.schema
df_sql.to_pandas(use_pyarrow_extension_array=True).to_sql('table_name', conn_uri_db2, if_exists='replace')
>Error message: 'ArrowExtensionArray' object has no attribute 'to_pydatetime'```Issue Description
Hi everyone.
I was trying to take data from one database and write it into another, using Polars, but I was getting this error, analyzing the trace it occurs when polars uses pandas behind the scene to convert their dataframe to pandas dataframe and use the to_sql command, so I reproduced what polars does.
Apparently the error only occurs when using a dataframe that has datetime columns.
Thanks in advance for your help.
Here is the full trace error:
~\AppData\Local\Temp\ipykernel_14916\424779397.py in <module>
----> 1 df_sql.to_pandas(use_pyarrow_extension_array=True).to_sql('teste_2', con, if_exists='replace')
~\Anaconda3\lib\site-packages\pandas\core\generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
2985 from pandas.io import sql
2986
-> 2987 return sql.to_sql(
2988 self,
2989 name,
~\Anaconda3\lib\site-packages\pandas\io\sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)
693 )
694
--> 695 return pandas_sql.to_sql(
696 frame,
697 name,
~\Anaconda3\lib\site-packages\pandas\io\sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)
1736 )
1737
-> 1738 total_inserted = sql_engine.insert_records(
1739 table=table,
1740 con=self.connectable,
~\Anaconda3\lib\site-packages\pandas\io\sql.py in insert_records(self, table, con, frame, name, index, schema, chunksize, method, **engine_kwargs)
1323
1324 try:
-> 1325 return table.insert(chunksize=chunksize, method=method)
1326 except exc.SQLAlchemyError as err:
1327 # GH34431
~\Anaconda3\lib\site-packages\pandas\io\sql.py in insert(self, chunksize, method)
922 raise ValueError(f"Invalid parameter `method`: {method}")
923
--> 924 keys, data_list = self.insert_data()
925
926 nrows = len(self.frame)
~\Anaconda3\lib\site-packages\pandas\io\sql.py in insert_data(self)
890 vals = ser._values
891 if vals.dtype.kind == "M":
--> 892 d = vals.to_pydatetime()
893 elif vals.dtype.kind == "m":
894 # store as integers, see GH#6921, GH#7076
AttributeError: 'ArrowExtensionArray' object has no attribute 'to_pydatetime'
Expected Behavior
I expect to be able to write columns with datetime type into my database.
Installed Versions
INSTALLED VERSIONS
python : 3.9.13.final.0
python-bits : 64
pandas : 1.5.3
numpy : 1.21.5
pytz : 2022.1
dateutil : 2.8.2
setuptools : 63.4.1
pip : 22.2.2
Cython : 0.29.32
pytest : 7.1.2
hypothesis : None
sphinx : 5.0.2
blosc : None
feather : None
xlsxwriter : 3.0.3
lxml.etree : 4.9.1
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.11.3
IPython : 7.31.1
pandas_datareader: None
bs4 : 4.11.1
bottleneck : 1.3.5
brotli :
fastparquet : None
fsspec : 2023.3.0
gcsfs : None
matplotlib : 3.5.2
numba : 0.55.1
numexpr : 2.8.3
odfpy : None
openpyxl : 3.0.10
pandas_gbq : None
pyarrow : 11.0.0
pyreadstat : None
pyxlsb : None
s3fs : 2023.3.0
scipy : 1.9.1
snappy :
sqlalchemy : 2.0.5.post1
tables : 3.6.1
tabulate : 0.8.10
xarray : 0.20.1
xlrd : 2.0.1
xlwt : None
zstandard : None
tzdata : None