how to commit changes to SQLAlchemy object in the database table

Question

I am trying to translate a set of columns in my MySQL database using Python's googletrans library.

Sample MySQL table Data:

Label        Answer               Label_Translated  Answer_Translated
cómo estás   Wie heißen sie?      NULL              NULL
wie gehts    per favore rivisita  NULL              NULL
元気ですか    Cuántos años tienes  NULL              NULL

Below is my sample code:

import pandas as pd
import googletrans
from googletrans import Translator
import sqlalchemy
import pymysql
import numpy as np
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import sessionmaker
engine = create_engine("mysql+pymysql:.....")
Session = sessionmaker(bind = engine)
session = Session()
translator = Translator()

I read the database table using:

sql_stmt = "SELECT * FROM translate"
data = session.execute(sql_stmt)

I perform the translation steps using:

for to_translate in data:
    to_translate.Answer_Translated = translator.translate(to_translate.Answer, dest = 'en')
    to_translate.Label_Translated = translator.translate(to_translate.Label, dest = 'en')

I tried session.commit() but the changes are not reflected in the database. Could someone please let me know how to make the changes permanent in the database.

Also when I try:

for rows in data:
    print(rows)

I don't see any output. Before enforcing the changes in the database, is there a way we can view the changes in Python ?

Is it possible you're not calling session.add(to_translate) before you call session.commit()? — wombat
– wombat, Commented Nov 24, 2021 at 15:32
@wombat, strange when I restarted the kernel and run the for loop, I get this error: AttributeError: 'RowProxy' object has no attribute 'Answer_Translated' It's just the same code I am re-executing — Karthik S
– Karthik S, Commented Nov 24, 2021 at 15:37
that looks more like an issue due to restarting your kernel and not rerunning all your cells. I think part of the issue here is we can't rally see an easily reproducible case to help you. — wombat
– wombat, Commented Nov 24, 2021 at 15:43
@wombat, could you please let me know how to share a reproducible example? I am getting the table from my local DB. I am new to SQLAlchemy. — Karthik S
– Karthik S, Commented Nov 24, 2021 at 15:53
Let me answer the question so maybe it will make it clearer with an example — wombat
– wombat, Commented Nov 24, 2021 at 16:14

wombat · Accepted Answer · 2021-11-24 20:21:33Z

3

Rewriting my answer because I missed OP was using a raw query to get his set.

Your issue seems to be that there is no real update logic in your code (although you might have missed that out. Here is what you could do. Keep in mind that it's not the most efficient or elegant way to deal with this, but this might get you in the right direction.

# assuming import sqlalchemy as sa

for to_translate in data:
    session = Session()
    print(to_translate)
    mappings = {}
    mappings['Label'] = to_translate[0]
    mappings['Answer_Translated'] = translator.translate(to_translate.Answer, dest="en")
    mappings['Label_Translated'] = translator.translate(to_translate.Label, dest="en")
    update_str = "update Data set Answer_Translated=:Answer_Translated, set Label_Translated=:Label_Translated where Label == :Label"
    session.execute(sa.text(update_str), mappings)
    session.commit()

This will update your db. Now I can't guarantee it will work out of the box, because your actual table might differ from the sample you posted, but the print statement should be able to guide you in fixing update_str. Note that using the ORM would make this a lot nicer.

edited Nov 24, 2021 at 20:21

answered Nov 24, 2021 at 16:18

wombat

7484 silver badges21 bronze badges

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

5 Comments

Karthik S Over a year ago

I am still getting this error 'RowProxy' object has no attribute 'Answer_Translated'. I created a new table in the database, changed iterator name but still am getting the same error. Could you please let me know how to fix it?

wombat Over a year ago

Yeah I see what the issue is. You're calling session.execute() so you can't simply add them back and push them to your db. It's much easier if you use the ORM the get the data from the db. I'll rewrite my answer in a bit with a way to resolve this.

Karthik S Over a year ago

That'd be really helpful.

Ilja Everilä Over a year ago

String formatting and passing data to queries should not be mixed. It is error prone and the road to SQL injection. It might not happen here, but why use the worse method? Placeholders are safe and DB-API drivers usually also handle data conversions, like passing a dateime, for you.

wombat Over a year ago

@IljaEverilä Yeah I agree. I went for quick and dirty and felt the placeholders would distract from the answer, but it's not so bad really. I've added them. I think it's still readable.

Collectives™ on Stack Overflow

how to commit changes to SQLAlchemy object in the database table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related