I am a bit new to Sqlalchemy
My goal is to update a table using Sqlalchemy, the total number of entries in the live environment will be around 44k But I have tried to run my code on 2k entries for test purposes.
The process of bulk update for 2k takes around 2-3secs on local whereas on staging it takes more than 400 secs
for chunk in range(0, len(news_dictionary), 100):
new = []
for index, all_clusters in enumerate(
news_dictionary[chunk:chunk + 100]):
for news_id in all_clusters['clusters']:
new.append({"id": news_id,
"cluster_id": uuids_list[index + chunk]})
local_session.bulk_update_mappings(Feeds_class, new)
local_session.commit()
whereas, The DB is Postgres replicated master, Feeds_class is a table defined using sqlalchemy , news_dictionary is a list of dictionaries, the goal of this for loop is to insert "cluster_id" wherever a news_id was found in the Feeds_class.
Feeds_class already has data but the cluster_id column is empty, hence the update command
here's the definition of my Feeds_class
class Feeds_class(Base):
__tablename__ = 'feeds1'
id = Column(INTEGER, nullable=False, primary_key=True)
text = Column(Text, nullable=True)
description = Column(Text, nullable=True)
url = Column(Text, nullable=True)
image = Column(Text, nullable=True)
slug = Column(Text, nullable=True)
jobid = Column(Text, nullable=True)
source = Column(Text, nullable=True)
source_url = Column(Text, nullable=True)
logo = Column(Text, nullable=True)
pagetype = Column(Text, nullable=True)
htmlpath = Column(Text, nullable=True)
favicon = Column(Text, nullable=True)
md5 = Column(Text, nullable=True)
is_headline = Column(BOOLEAN, nullable=False, default=False)
download_time = Column(FLOAT, nullable=True)
time = Column(FLOAT, nullable=True)
last_parse_time = Column(FLOAT, nullable=True)
expiry = Column(FLOAT, nullable=True)
genre = Column(ARRAY(TEXT), default=[], nullable=False)
created_at = Column(DateTime, nullable=False, default=datetime.utcnow)
updated_at = Column(DateTime, nullable=False, default=datetime.utcnow)
cluster_id = Column(UUID(as_uuid=True), ForeignKey(f'{cluster_table_name}.id')
the following is the cluster class which have a foreign key relation to the feeds
class Clusters_class(Base):
__tablename__ = cluster_table_name
# app_uuid = Column(u'_uuid', UUID(), primary_key=True)
id = Column(
UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
created_at = Column(
TIMESTAMP,
nullable=True,
default=datetime.utcnow())
created_at_timestamp = Column(Float, nullable=True)
clusters = Column(ARRAY(INTEGER),nullable=False)
news = relationship('Feeds_class', backref=cluster_table_name,
lazy='dynamic')
Base.metadata.create_all(engine)