AttributeError: 'Connection' object has no attribute 'connect' when use df.to_sql()

Question

I am trying to store data retrieved from a website into MySQL database via a pandas data frame. However, when I make the function call df.to_sql(), the compiler give me an error message saying: AttributeError: 'Connection' object has no attribute 'connect'. I tested it couple times and I am sure that there is neither connection issue nor table existence issue involved. Is there anything wrong with the code itself? The code I am using is the following:

    from sqlalchemy import create_engine, text
    import pandas as pd
    import mysql.connector

    
    config = configparser.ConfigParser()
    config.read('db_init.INI')
    password = config.get("section_a", "Password")
    host = config.get("section_a", "Port")
    database = config.get("section_a", "Database")

    engine = create_engine('mysql+mysqlconnector://root:{0}@{1}/{2}'.
                           format(password, host, database),
                           pool_recycle=1, pool_timeout=57600, future=True)
    
    conn = engine.connect()
    df.to_sql("tableName", conn, if_exists='append', index = False)

The full stack trace looks like this:

Traceback (most recent call last):
  File "/Users/chent/Desktop/PFSDataParser/src/FetchPFS.py", line 304, in <module>
    main()
  File "/Users/chent/Desktop/PFSDataParser/src/FetchPFS.py", line 287, in main
    insert_to_db(experimentDataSet, expName)
  File "/Users/chent/Desktop/PFSDataParser/src/FetchPFS.py", line 89, in insert_to_db
    df.to_sql(tableName, conn, if_exists='append', index = False)
  File "/Users/chent/opt/anaconda3/lib/python3.9/site-packages/pandas/core/generic.py", line 2951, in to_sql
    return sql.to_sql(
  File "/Users/chent/opt/anaconda3/lib/python3.9/site-packages/pandas/io/sql.py", line 698, in to_sql
    return pandas_sql.to_sql(
  File "/Users/chent/opt/anaconda3/lib/python3.9/site-packages/pandas/io/sql.py", line 1754, in to_sql
    self.check_case_sensitive(name=name, schema=schema)
  File "/Users/chent/opt/anaconda3/lib/python3.9/site-packages/pandas/io/sql.py", line 1647, in check_case_sensitive
    with self.connectable.connect() as conn:

AttributeError: 'Connection' object has no attribute 'connect'

The version of pandas I am using is 1.4.4, sqlalchemy is 2.0

I tried to make a several execution of sql query, for example, CREATE TABLE xxx IF NOT EXISTS or SELECT * FROM, all of which have given me the result I wish to see.

Answer 1

I just run into this problem too. Pandas 1.x doesn't support SqlAlchemy 2 yet. As the relevant Github issue shows the next release of Pandas will require sqlalchemy<2.0. For now you have to downgrade to SqlAlchemy 1.4.x with eg :

pip install --upgrade SQLAlchemy==1.4.46

The problem is caused by an incompatibility between the Pandas version and SqlAlchemy 2.0. SqlAlchemy 2.0 was released on January 28, 2023 while even the latest Pandas version at the time, 1.5.3 was released on January 19.

Pandas does support sqlalchemy.engine.Connection. From the docs :

cons : qlalchemy.engine.(Engine or Connection) or sqlite3.Connection

Using SQLAlchemy makes it possible to use any DB supported by that library. Legacy support is provided for sqlite3.Connection objects. The user is responsible for engine disposal and connection closure for the SQLAlchemy connectable See here.

I downgraded to SqlAlchemy 1.4.46 and to_sql stopped complaining.

If you use pip you can downgrade using :

pip install --upgrade SQLAlchemy==1.4.46

or

pip install SQLAlchemy
pip install SQLAlchemy==1.4.46