[Python] Memory issue when scanning and updating every row of a large table (sqlalchemy)

I want to scan a large table with more than 1 million rows, meanwhile update one column of all rows to it.

Code below leads out of memory issue:

def main():
session = Session()
i = 0
for row in session.query(article).yield_per(100):
i = i + 1
print(row.id)
row.keywords = clean_tag(row.keywords)
if i % 100 == 0:
session.flush()
session.expunge_all()
session.commit()


As supposed, flush() will persist change of objects to database and expunge_all should remove these objects.

What's wrong? Thanks.


edited on 7.17

As suggested by univerio I paste the example here:

#!/usr/bin/env
# coding=utf-8

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, BigInteger, String
from sqlalchemy.orm import sessionmaker

# engine = create_engine('mysql://root:123456@192.168.0.202/toutiao')
engine = create_engine('mysql://root:MyNewPass4!@192.168.3.220/toutiao')
Session = sessionmaker(bind=engine, autoflush=True)

Base = declarative_base()
class article(Base):
# __tablename__ = 'ss_article_group'
__tablename__ = 'article100'
id = Column(BigInteger, primary_key=True)
keywords = Column(String)

def clean_tag(tag):
r"""
>>> clean_tag('a,b\nc d')
'a,b,c,d'
>>> clean_tag('\na,b\n\n')
'a,b'
>>> clean_tag('a,b,')
'a,b'
>>> clean_tag(',')

"""
if tag is None:
return False
tags = tag.split()
new_tag = ','.join(tags)
new_tag = new_tag.strip(',')
if new_tag == '':
return None
if new_tag == tag:
return False
return new_tag

def main():
session = Session()
i = 0
for row in session.query(article).yield_per(100):
i = i + 1
print(row.id)
new_keywords = clean_tag(row.keywords)
if new_keywords != False:
row.keywords = new_keywords
if i % 100 == 0:
session.flush()
session.expunge_all()
session.commit()

if __name__ == '__main__':
main()

Continue reading...