DOC: example in docstring of to_sql is out of date

[vahtras]

Pandas version checks

• I have checked that the issue still exists on the latest versions of the docs on main here

Location of the documentation

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

Documentation problem

Everything is fine with the pinned dependencies in the pandas distribution (environment.yml)

However, when I in another context start with a fresh environment with later versions of python and sqlalchemy the examples in the docstring do not work. In particular with docstring to_sql

>>> import pandas as pd
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite://', echo=False)
>>> df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})
>>> df
     name
0  User 1
1  User 2
2  User 3
>>> df.to_sql('users', con=engine)
3
>>> engine.execute("SELECT * FROM users").fetchall()
[(0, 'User 1'), (1, 'User 2'), (2, 'User 3')]

a doctest gives

$ python -m doctest /tmp/from_to_sql_docstring.md                         1 ↵
**********************************************************************
File "/tmp/from_to_sql_docstring.md", line 15, in from_to_sql_docstring.md
Failed example:
    engine.execute("SELECT * FROM users").fetchall()
Exception raised:
    Traceback (most recent call last):
      File "/home/olav/.pyenv/versions/3.10.5/lib/python3.10/doctest.py", line 1350, in __run
        exec(compile(example.source, filename, "single",
      File "<doctest from_to_sql_docstring.md[6]>", line 1, in <module>
        engine.execute("SELECT * FROM users").fetchall()
    AttributeError: 'Engine' object has no attribute 'execute'
**********************************************************************
1 items had failures:
   1 of   7 in from_to_sql_docstring.md
***Test Failed*** 1 failures.

I am here using versions python3.10.5 and versions
pandas==1.5.3
sqlalchemy==2.0.0

Suggested fix for documentation

The smallest fix I can think of that works in both versions is

>>> import pandas as pd
>>> from sqlalchemy import create_engine, text
>>> engine = create_engine('sqlite://', echo=False)
>>> df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})
>>> df
     name
0  User 1
1  User 2
2  User 3
>>> df.to_sql('users', con=engine)
3
>>> with engine.connect() as conn:
...    conn.execute(text("SELECT * FROM users")).fetchall()
[(0, 'User 1'), (1, 'User 2'), (2, 'User 3')]

of course, there could be a better way

[phofl]

Thanks for your report, duplicate of #51015

[phofl]

Can you post in #40686