Search code examples
pythonpandassqlalchemypandasql

AttributeError: 'str' object has no attribute '_execute_on_connection'

I have a problem with following code:

from pandasql import sqldf
import pandas as pd

df = pd.DataFrame({'column1': [1, 2, 3], 'column2': [4, 5, 6]})

query = "SELECT * FROM df WHERE column1 > 1"

new_dataframe = sqldf(query)

print(new_dataframe)

When I submit, I have this error:

Traceback (most recent call last):

  File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\sqlalchemy\engine\base.py:1410 in execute
    meth = statement._execute_on_connection

AttributeError: 'str' object has no attribute '_execute_on_connection'


The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File ~\AppData\Local\Programs\Spyder\pkgs\spyder_kernels\py3compat.py:356 in compat_exec
    exec(code, globals, locals)

  File c:\users\yv663dz\downloads\untitled1.py:18
    new_dataframe = sqldf(query)

  File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandasql\sqldf.py:156 in sqldf
    return PandaSQL(db_uri)(query, env)

  File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandasql\sqldf.py:61 in __call__
    result = read_sql(query, conn)

  File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandas\io\sql.py:592 in read_sql
    return pandas_sql.read_query(

  File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandas\io\sql.py:1557 in read_query
    result = self.execute(*args)

  File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\pandas\io\sql.py:1402 in execute
    return self.connectable.execution_options().execute(*args, **kwargs)

  File ~\AppData\Local\Programs\Spyder\Python\lib\site-packages\sqlalchemy\engine\base.py:1412 in execute
    raise exc.ObjectNotExecutableError(statement) from err

ObjectNotExecutableError: Not an executable object: 'SELECT * FROM df WHERE column1 > 1'

I installed the latest versions of pandas, pandasql and sqlalchemy and I use Spyder as IDE. Could someone help me please?

Solution

  • SQLAlchemy 2.0 (released 2023-01-26) requires that raw SQL queries be wrapped by sqlalchemy.text.

    The general solution for this error message is to pass the query text to sqlalchemy.text()

    from sqlalchemy import text
...

query = text("SELECT * FROM some_table WHERE column1 > 1")

    However in this case the OP is using pandasql, which expects a string. There does not seem to be a straightforward way to make pandasql compatible with SQLAlchemy >= 2.0, and the package seems to be unmaintained, so the only solutions are to find a fork that has fixed the problem (there are some), fork the project yourself and fix it, or downgrade your SQLAlchemy installation using your Python package manager. For example, if you use pip:

    python3 -m pip install --upgrade 'sqlalchemy<2.0'