Question 89973 in PostgreSQL Question posted in
The official documentation can be found here.

No attribute "execute" error on connecting to PostgreSQL

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 FilesPython311Libsite-packagespandasiosql.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?

Tags:

3

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search