AttributeError: 'Engine' object has no attribute 'execute' when trying to run sqlalchemy in python to manage my SQL database

Question

I have the following line of code that keeps giving me an error that Engine object has no object execute. I think I have everything right but no idea what keeps happening. It seemed others had this issue and restarting their notebooks worked. I'm using Pycharm and have restarted that without any resolution. Any help is greatly appreciated!

import pandas as pd
from sqlalchemy import create_engine, text
import sqlalchemy
import pymysql


masterlist = pd.read_excel('Masterlist.xlsx')

user = 'root'
pw = 'test!*'
db = 'hcftest'

engine = create_engine("mysql+pymysql://{user}:{pw}@localhost:3306/{db}"
                           .format(user=user, pw=pw, db=db))


results = engine.execute(text("SELECT * FROM companyname;"))

for row in results:
    print(row)

You need engine.connect(). It's lazy and won't actually connect until needed — roganjosh
– roganjosh, Commented Feb 1, 2023 at 22:16
Side note if you want a pretty good Python-based GUI driven MySQL Editor, Oracle created one that is FREE called MySQL Workbench, and it makes db work as easy as playing with Excel. Unless you are just doing a proof in concept, you might want to look into it and save youself a ton of time. — easleyfixed
– easleyfixed, Commented Feb 1, 2023 at 22:19

jonathan-dufault-kr · Accepted Answer · 2023-02-01 22:38:25Z

54

There was a change from 1.4 to 2.0. The above code will run fine with sqlalchemy version 1.4 I believe. setting SQLALCHEMY_WARN_20=1 python and running the above code reveals this warning:

<stdin>:1: RemovedIn20Warning: The Engine.execute() method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by the Connection.execute() method of Connection, or in the ORM by the Session.execute() method of Session. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

So the correct way to do the code now is:

with engine.connect() as conn:
    result = conn.execute(stmt)

source here describing the behavior in 1.4 and here describing the behavior in 2.0

answered Feb 1, 2023 at 22:38

jonathan-dufault-kr

7286 silver badges11 bronze badges

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

2 Comments

Frank Over a year ago

Did they make it harder to query dynamically on purpose?

Eric Leschinski Over a year ago

@Frank You can find their """apology""" for the breakage here; scroll down to "explicit transactions" around PEP249. docs.sqlalchemy.org/en/14/changelog/…

"SQLAlchemy 2.0 will do away with all attempts to implicitly commit transactions, and usage patterns will always require that the user demarcate the “beginning” and the “end” of a transaction in some way, in the same way as reading or writing to a file in Python has a “beginning” and an “end"

Eric Leschinski · Accepted Answer · 2024-07-05 03:29:39Z

sql = f"""INSERT INTO user_apis
  (api_id, user_id, monthly_requests, total_requests)
  VALUES (1, {current_user.id}, 0, 0)"""

1.x

result = db.engine.execute(sql)

2.x

from sqlalchemy import text
with db.engine.begin() as conn:
    result = conn.execute(text(sql)) 
    conn.commit()              #2.x execute now only works with SELECT.
                               #Inserts, Updates and Deletes now must be 
                               #in a transaction and explicitly committed
                               #use engine echo=True to show transaction status

SELECT sql

with db.engine.connect() as conn:
    result = conn.execute(text(sql)).fetchall()

Add this method to do the right thing on both versions.

Then change all your db.engine.execute(sql) to myengine_execute(sql)

def myengine_execute(sql): 
  #If sqlalchemy version starts with 1.4 then do it the old way
  sqlalchemy_version = version("sqlalchemy")
  if sqlalchemy_version.startswith('1.4.'):
    with engine.connect() as conn: 
        return conn.execute(text(sql)) 
  else:
    #otherwise do it the new way with transactions:
    with engine.connect() as conn: 
        result = conn.execute(text(sql)) 
        #print(result.inserted_primary_key()) 
        conn.commit()

upd_sql = "update bankaccount set amount = amount+5e10 where id = 1234567" 
result = myengine_execute(upd_sql)

James Prentice · Accepted Answer · 2023-02-19 06:50:19Z

1

from .. import db

def get():

    sql_statement = \
        """
            SELECT *
        """

    with db.engine.begin() as conn:
        response = conn.exec_driver_sql(sql_statement).all()
    
    return response

answered Feb 19, 2023 at 6:50

James Prentice

2713 silver badges10 bronze badges

Collectives™ on Stack Overflow

AttributeError: 'Engine' object has no attribute 'execute' when trying to run sqlalchemy in python to manage my SQL database

3 Answers 3

2 Comments

Add this method to do the right thing on both versions.

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Add this method to do the right thing on both versions.

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related