QST: Support of SQLAlchemy 2.0 in Pandas

[mickaelandrieu]

After search in Stackoverflow and in your open and closed issues, I can't find any information about the support of SQL Alchemy 2.0 using pd.to_sql()

---

Question about pandas

An exemple is better than a talk.

from sqlalchemy import create_engine, MetaData, Table, Column, String, Integer
import pandas as pd

# Table creation
engine = create_engine(connection_string, echo=True, future=True) # This uses SQL Alchemy 2.0 API
metadata = MetaData()
category = Table(
        'category',
        metadata,
        Column('id', Integer, primary_key = True),
        Column('name', String(128), nullable = False),
        Column('slug', String(128), nullable = False)
    )

category.create(engine, checkfirst=True)

## Insert using pandas => FAIL
categories = pd.DataFrame([{'id' : 1, 'slug': 'test', 'name': 'Test'}])
categories.to_sql('category', engine, index=False)

This is the error I get from the CLI output :

  File "e:\Projects\test\database.py", line 127, in populate_category_table
    categories.to_sql('category', engine, index=False)
  File "E:\Projects\test\env\lib\site-packages\pandas\core\generic.py", 
line 2779, in to_sql
    sql.to_sql(
  File "E:\Projects\test\env\lib\site-packages\pandas\io\sql.py", line 590, in to_sql
    pandas_sql.to_sql(
  File "E:\Projects\test\env\lib\site-packages\pandas\io\sql.py", line 1392, in to_sql
    table.create()
  File "E:\Projects\test\env\lib\site-packages\pandas\io\sql.py", line 722, in create
    if self.exists():
  File "E:\Projects\test\env\lib\site-packages\pandas\io\sql.py", line 709, in exists
    return self.pd_sql.has_table(self.name, self.schema)
  File "E:\Projects\test\env\lib\site-packages\pandas\io\sql.py", line 1429, in has_table
    return self.connectable.run_callable(
  File "E:\Projects\test\env\lib\site-packages\sqlalchemy\future\engine.py", line 335, in _not_implemented
    raise NotImplementedError(
NotImplementedError: This method is not implemented for SQLAlchemy 2.0.

For information about the version of every dependency :

$ ./env/Scripts/pip list
Package         Version
--------------- ---------
beautifulsoup4  4.9.3
certifi         2020.12.5
chardet         4.0.0
esprima         4.0.1
greenlet        1.0.0
idna            2.10
mysqlclient     2.0.3
numpy           1.20.1
pandas          1.2.3
patreon         0.5.0
pip             21.0.1
python-dateutil 2.8.1
pytz            2021.1
requests        2.25.1
setuptools      49.2.1
six             1.15.0
soupsieve       2.2
SQLAlchemy      1.4.0
urllib3         1.26.3

Is there a workaround right now or should I avoid using pandas to insert the data into my database ?

Regards,

[fangchenli]

My suggestion is to downgrade to 1.3.23 for now. We're looking into this issue.

[jorisvandenbossche]

@mickaelandrieu there was just a PR merged that deals with some deprecation warnings: #40471

This might already resolve the compatibility issues with the future sqlalchemy 2.0 as well?

[mickaelandrieu]

Hello @jorisvandenbossche,

When this PR will be released ? I can try and come back here to tell you 👍

Duly noted @fangchenli, thanks for your answer !

Regards

[scnerd]

It's unclear from that MR if it tackled all 2.0 deprecation warnings; there are (at least) two categories of SQLA deprecations, SADeprecationWarning and RemovedIn20Warning, and I'm not sure if that MR only tackled the first.

A complete, official guide for the migration is here. I can confirm that there are numerous SQLA 2.0 warnings thrown in Pandas 1.2.3 (released 2021/03/02), I can install from master and check if they're still there after that MR.

[mickaelandrieu]

Hi @scnerd,

what is the right process to install pandas on master branch ? I'm not really good at Python, but i will help if I can !

Regards,

[scnerd]

@mickaelandrieu pip can accept git repo's like pip install git+https://github.com/pandas-dev/pandas.git

[scnerd]

Having tested some things on master branch, I get at least the following couple of errors:

  /opt/conda/lib/python3.8/site-packages/pandas/io/sql.py:1313: RemovedIn20Warning: The MetaData.bind argument is deprecated and will be removed in SQLAlchemy 2.0. (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
    meta = MetaData(self.connectable, schema=schema)

  /opt/conda/lib/python3.8/site-packages/pandas/io/sql.py:1103: RemovedIn20Warning: The MetaData.bind argument is deprecated and will be removed in SQLAlchemy 2.0. (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
    meta = MetaData(self.pd_sql, schema=schema)

  /opt/conda/lib/python3.8/site-packages/sqlalchemy/sql/schema.py:939: RemovedIn20Warning: The ``bind`` argument for schema methods that invoke SQL against an engine or connection will be required in SQLAlchemy 2.0. (Background on SQLAlchemy 2.0 at: http://sqlalche
.me/e/b8d9)
    bind = _bind_or_error(self)

  /opt/conda/lib/python3.8/site-packages/pandas/io/sql.py:1612: RemovedIn20Warning: The Connection.connect() method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
    with self.connectable.connect() as conn:

[nsepetys]

Is there a workaround right now or should I avoid using pandas to insert the data into my database ?

Regards,

@mickaelandrieu - One other workaround- I created an engine for this particular pandas method and turned off future (future=False) for the engine creation. It's working now. Going to leave a ToDo to come back and replace once pandas is working with this. Let me know if you found a less hacky way that's working without building the package off of master.

[scnerd]

Just to clarify my remark about master, the RemovedIn20Warnings mean that, even on master, Pandas' SQL IO functions are not yet 2.0 compatible. Since SQLAlchemy 2.0 isn't even released yet (at least, as a stable release), I wouldn't consider that an issue (yet), it'll just require pinning sqlalchemy<=2 as a Pandas dependency until those incompatibilities are resolved. Using future=True in your SQLA session is therefore not yet compatible with Pandas.

Frankly, even though the SQLA 2.0 migration guide is great and the changes needed are pretty simple, there's also the concern that these changes are, in some cases, not backwards-compatible with <1.4. This is probably a concern for a library as ubiquitous as Pandas, and means that the upgrades should probably be made in a way that can detect and, when necessary, fall back on the <1.4 conventions.