No attribute "execute" error on connecting to PostgreSQL

Question

I have my postgres installed on PC1 and I am connecting to the database using PC2. I have modified the settings so that postgres on PC1 is accessible to local network.

On PC2 I am doing the following:

import pandas as pd, pyodbc
from sqlalchemy import create_engine
z1 = create_engine('postgresql://postgres:***@192.168.40.154:5432/myDB')
z2 = pd.read_sql(fr"""select * from public."myTable" """, z1)

I get the error:

File "C:\Program Files\Python311\Lib\site-packages\pandas\io\sql.py", line 1405, in execute
    return self.connectable.execution_options().execute(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'OptionEngine' object has no attribute 'execute'

While running the same code on PC1 I get no error.

I just noticed that it happens only when reading from the db. If I do to_sql it works. Seems there is missing on the PC2 as instead of trying 192.168.40.154:5432 if I use localhost:5432 I get the same error.

Edit: Following modification worked but not sure why. Can someone please educate me what could be the reason for this.

from sqlalchemy.sql import text
connection = connection = z1.connect()
stmt = text("SELECT * FROM public.myTable")
z2 = pd.read_sql(stmt, connection)

Edit2:

PC1:
pd.__version__
'1.5.2'
import sqlalchemy
sqlalchemy.__version__
'1.4.46'


PC2:
pd.__version__
'1.5.3'
import sqlalchemy
sqlalchemy.__version__
'2.0.0'

Does it mean that if I update the packages on PC1 everything is going to break?

What versions of SQLAlchemy and Pandas? Add information as update to question. — Adrian Klaver
– Adrian Klaver, Commented Jan 27, 2023 at 20:53

Marcelo Peñafiel · Accepted Answer · 2023-01-30 00:56:11Z

5

I ran into the same problem just today and basically it's the SQLalchemy version, if you look at the documentation here the SQLalchemy version 2.0.0 was released a few days ago so pandas is not updated, for now I think the solution is sticking with the 1.4.x version.

answered Jan 30, 2023 at 0:56

Marcelo Peñafiel

661 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

cyril Over a year ago

Did you open an issue already ?

P H · Accepted Answer · 2023-01-27 22:06:52Z

1

The sqlalchemy.sql.text() part is not the issue. The addition of connection() to the connect_engine() instruction seems to have done the trick.

answered Jan 27, 2023 at 22:06

P H

111 bronze badge

1 Comment

Zanam Over a year ago

I tried both connection and text and they both need to be present for the code to work.

swiss_knight · Accepted Answer · 2023-02-07 22:55:58Z

You should also use a context manager in addition to a SQLAlchemy SQL clause using text, e.g.:

import pandas as pd, pyodbc
from sqlalchemy import create_engine, text
engine = create_engine('postgresql://postgres:***@192.168.40.154:5432/myDB')

with engine.begin() as connection: 
    res = pd.read_sql(
        sql=text(fr'SELECT * FROM public."myTable"'),
        con=connection,
    )

As explained here https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html :

conSQLAlchemy connectable, str, or sqlite3 connection

Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported. The user is responsible for engine disposal and connection closure for the SQLAlchemy connectable; str connections are closed automatically. See here.

--> especially this point: https://docs.sqlalchemy.org/en/20/core/connections.html#connect-and-begin-once-from-the-engine

Collectives™ on Stack Overflow

No attribute "execute" error on connecting to PostgreSQL

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related