Alembic custom migation code doesn't work big query

Question

I try this query in alembic migration code

def get_my_table_id_list(connection):
    duplicate_id_list = connection.execute(
        """
        SELECT
            a.id
        FROM
            my table AS a
        WHERE
            a.created_datetime > (
                SELECT
                    MIN(b.created_datetime)
                FROM
                    map_direction AS b
                WHERE
                    < condition >)
        LIMIT 1000
        """
    ).fetchall()

    return duplicate_id_list


def delete_my_table(connection, id_list):
    test = connection.execute(
        f"""
        DELETE FROM map_direction
        WHERE my table.id IN ({", ".join(id_list)})
        """
    )

    print(test.rowcount)


def check_and_delete_duplicate_my_tablle(connection):
    duplicate_id_list = get_duplicate_id_list(connection)

    if len(duplicate_id_list) < 1:
        return True
    else:
        duplicate_id_list = list(map(lambda _id: str(_id[0]), duplicate_id_list))

        delete_my_table(connection, duplicate_id_list)

        check_and_delete_duplicate_my_table(connection)

    return False

using this functions in my migration code and run flask db upgrade or alembic upgrade +1

my db is psql, my table all rows: 5,000,000 very big table

If my DB row is less than 100,000 these functions work for me. But my table very very big! this function doesn't work for me.

How I can resolve this problem?

score 0 · Answer 1 · answered Mar 10 '20 at 00:40

def get_my_table_id_list(connection):
    duplicate_id_list = connection.execute(
        """
        SELECT
            a.id
        FROM
            my table AS a
        WHERE
            a.id > (
                SELECT
                    MIN(b.id)
                FROM
                    map_direction AS b
                WHERE
                    < condition >)
        LIMIT 1000
        """
    ).fetchall()

    return duplicate_id_list

............ There was a duplicate of create_datetime.

Alembic custom migation code doesn't work big query

1 Answers1