13

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?

ciavam
  • 133
  • 1
  • 6

1 Answers1

26

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'
snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
  • After I wrapped my query string with `text()`, I got `typeerror: expected string or bytes-like object` – Lobshi Mar 19 '23 at 06:37
  • I got the error `TypeError: expected string or bytes-like object` On doing this. – Kaushal Kumar Mar 25 '23 at 05:04
  • 1
    Downgrading SQLAlchemy worked to make the code [here](https://www.kaggle.com/code/cosmosankur/sql-for-data-analytics-with-titanic-data) work again without the error reported in the OP title here, but trying to wrap queries with `text()` I also saw the same TypeError as Lobshi and Kaushal report. I don't think getting that suggests a different problem. ... I'm also tagging here with [the reported issue at GitHub](https://github.com/yhat/pandasql/issues/102). – Wayne Mar 29 '23 at 17:44
  • 1
    @Wayne Thanks for this comment and sorry for the late reply - I had taken a break from SO for a while. You're quite right, I'd somehow missed that the OP was using pandasql rather than standard pandas. I've reworded the answer to reflect this. – snakecharmerb May 29 '23 at 10:57
  • @Wayne is it using """queryvariable""" ? – VGupta Jun 29 '23 at 11:59
  • What is `queryvariable`? An SQL thing? I'm not following. – Wayne Jun 29 '23 at 12:27