Shakil Ilham

@subiewrx
I need to know one more thing. Even though SEO data was imported to the new site ( doing on a staging site ) the TruSEO score has not been updated. I have to go through each product and update them again in order to update SEO score. Is there any easy way? If you can explain me how and where AIOSEO store SEO scores I might be able to update them from database

Hi @subiewrx

Thank you for answering my question. I could fix this issue. I wrote a python program to match old and new post_ids using two exported CSV files from wp_post table of each database. Then program writes a sql file to update aioseo_posts table.

import csv

newdb = 'wp_posts.csv'
olddb = 'mr_cl_posts.csv'
new_aioseo = 'wp_aioseo_posts.csv'
old_aioseo = 'mr_cl_aioseo_posts.csv'

old_posts = {}
with open(olddb, 'r', encoding='utf8') as f:
? ? reader = csv.DictReader(f)
? ? for row in reader:
? ? ? ? old_posts[row['post_title']] = row['ID']

id_pairs = {}
with open(newdb, 'r', encoding='utf8') as f:
? ? reader = csv.DictReader(f)
? ? for row in reader:
? ? ? ? if row['post_title'] in old_posts:
? ? ? ? ? ? id_pairs[row['ID']] = old_posts[row['post_title']]

data = {}
with open(old_aioseo, 'r', encoding='utf8') as f:
? ? reader = csv.DictReader(f)
? ? for row in reader:
? ? ? ? data[row['post_id']] = row

updated_data = []
with open(new_aioseo, 'r', encoding='utf8') as f:
? ? reader = csv.DictReader(f)
? ? for row in reader:
? ? ? ? if row['post_id'] in id_pairs:
? ? ? ? ? ? old_seo_data = data[id_pairs[row['post_id']]]
? ? ? ? ? ? row['title'], row['description'], row['keywords'], row['keyphrases'], row['page_analysis'] = old_seo_data['title'], old_seo_data['description'], old_seo_data['keywords'], old_seo_data['keyphrases'], old_seo_data['page_analysis']
? ? ? ? ? ? updated_data.append(row)

def sanitize_text(text):
? ? return text.replace("'", "''").replace("\\", "\\\\")

with open('update_records.sql', 'w', encoding='utf8') as file:
? ? for record in updated_data:
? ? ? ? title = sanitize_text(record['title'])
? ? ? ? description = sanitize_text(record['description'])
? ? ? ? keywords = sanitize_text(record['keywords'])
? ? ? ? keyphrases = sanitize_text(record['keyphrases'])
? ? ? ? page_analysis = sanitize_text(record['page_analysis'])
? ? ? ? if title != 'NULL' or description != 'NULL' or keywords != 'NULL' or keyphrases != 'NULL' or page_analysis != 'NULL':
? ? ? ? ? ? sql = f"""UPDATE wp_aioseo_posts SET title = '{title}', description = '{description}', keywords = '{keywords}', keyphrases = '{keyphrases}', page_analysis = '{page_analysis}' WHERE id = {record['id']} AND post_id = {record['post_id']};

"""
? ? ? ? ? ? file.write(sql)

I hope this will be helpful to someone with same issue as me.