CODEWITHSUNDEEP
pythonmysqlsqlsqlalchemy
novawaly
novawaly

Reputation: 1251

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

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)

Upvotes: 25

Views: 62365

Answers (3)

Jeremy Busk
Jeremy Busk

Reputation: 483

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)

Upvotes: 14

James Prentice
James Prentice

Reputation: 262

from .. import db

def get():

    sql_statement = \
        """
            SELECT *
        """

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

Upvotes: 1

jonathan-dufault-kr
jonathan-dufault-kr

Reputation: 698

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

Upvotes: 52

Related Questions