update_url.py

import subprocess
import os
import re
import csv

from sqlalchemy import create_engine
from sqlalchemy.sql import select, and_, case
from sqlalchemy import BigInteger, Boolean, Column, Date, DateTime, ForeignKey, Integer, LargeBinary, SmallInteger, String, Table, Text, text, update
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# import env

DB_NAME = os.environ.get('DB_NAME', 'cms')
DB_USER = os.environ.get('DB_USER', 'cms')
DB_PASS = os.environ.get('DB_PASS', 'cms')
DB_PORT = os.environ.get('DB_PORT', 5432)
print(DB_NAME, DB_USER, DB_PASS, DB_PORT, 'aman')

Base = declarative_base()
metadata = Base.metadata

b = subprocess.Popen("docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' cms_pgsql", shell=True, stdout=subprocess.PIPE)
ip = b.communicate()[0].decode("utf-8")
ip = ip[:len(ip)-1]

engine_docker = create_engine('postgresql://'+DB_USER+':'+DB_PASS+'@'+ip+':'+str(DB_PORT)+'/'+DB_NAME, echo=False)

Session = sessionmaker(bind=engine_docker)
Session.configure(bind=engine_docker)
session_docker = Session()

connection_docker = engine_docker.connect()

class Page(Base):
    __tablename__ = 'page'

    id = Column(Integer, primary_key=True, server_default=text("nextval('page_id_seq'::regclass)"))
    static_url = Column(String(512))
    is_index = Column(SmallInteger)
    is_dynamic = Column(SmallInteger)
    draft_en = Column(Text)
    draft_hi = Column(Text)
    title_en = Column(String(256), nullable=False)
    title_hi = Column(String(256))
    body_en = Column(Text)
    body_hi = Column(Text)
    draft_exists = Column(SmallInteger)
    name = Column(String(255))
    type = Column(String(255))
    created_at = Column(Date)
    updated_at = Column(Date)
    deleted_at = Column(Date)
    is_active = Column(Boolean)
    short_url = Column(String(256))


metadata.clear()

def import_csv():
    pages = session_docker.query(Page).all()
    with open('faculty.csv', newline='') as File:
        reader = csv.reader(File)

        for row in reader:
            for entry in pages:
                if entry.static_url == row[1]:
                    row = row[1].split('/')
                    size = len(row)
                    last = row[4].split('+')
                    url = '/'+row[0]+'/'+row[1]+'/'+row[2]+'/'+row[3]+'/'+'People+Faculty+'+last[len(last)-1]
                    entry.static_url = url
                    # session_docker.add(entry)

    session_docker.commit()

import_csv()

print("logging: Updating Page done")