Replace substrings in database using SQLAlchemy

user_9036532 0
Viewed 30

I want to find and replace any instances of a substring within a database (in multiple tables / columns).

I figure I need to somehow loop through the database tables then columns then replace any instance of a substring but not sure how to do that using SQLAlchemy.

This is basically what I want to do but this is looping through a dataframe instead:

for col in df.columns:
        df[col] = df[col].replace('old_str', 'new_str', regex=True)

The substrings I am replacing exist in several different tables within the database.

sqlalchemy python database
1 Answers

You can iterate through all models and all columns and use replace:

from sqlalchemy import MetaData, String, create_engine, text
from sqlalchemy.orm import declarative_base

engine = create_engine(SQLALCHEMY_DATABASE_URI)
metadata = MetaData()
Base = declarative_base(metadata=metadata)
Session = sessionmaker()
Session.configure(bind=engine)

# models definition or imports of them

session = Session()
for mapper in Base.registry.mappers:
    table = mapper.mapped_table
    for column in table.columns:
       if isinstance(column.type, String):
           stmt = f"update {table} set {column} = replace({column}, <old_str>, <new_str>)"
           session.execute(text(stmt))
session.commit()
session.close()
user_79384350
Related