Home > Back-end >  Can I use executemany for a large batch process with sqlite3?
Can I use executemany for a large batch process with sqlite3?

Time:09-17

I have a pretty long python process that is meant to reprocess a massive amount of data from one table, extract the necessary pieces, assign appropriate values and reenter it into another table. The process works great! Except that it is incredibly time intensive. I wonder if there is a way to modify this using an executemany approach? These are snippets of the entire process but delineate where I'm hoping to modify to an executemany

The following code runs through a series of if/then statements to assign the appropriate values to the retrieved information.

    # Current crawl begin date
    cur.execute('SELECT Crawl_Begin_Date FROM Crawl WHERE Crawl_ID=?', current_crawl_ID)
    current_crawl_begin_date = cur.fetchone()
    current_crawl_begin_date = current_crawl_begin_date[0]
    
    # Current crawl end date
    cur.execute('SELECT Crawl_End_Date FROM Crawl WHERE Crawl_ID=?', current_crawl_ID)
    current_crawl_end_date = cur.fetchone()
    current_crawl_end_date = current_crawl_end_date[0]
    
    # URL_Crawl table where Crawl_ID == current crawl
    sql = 'SELECT URL_Crawl_ID, Last_Updated, Last_Published, Date_of_HTML, Unique_URL_ID FROM URL_Crawl WHERE Crawl_ID=%s'
    current_crawl = pd.read_sql_query(sql %(current_crawl_ID), con=db)

    # num keeps track of number of rows read through from current_crawl (number of nodes in current crawl)
    num = 1

    # For every unique url in the current crawl
    for row in current_crawl.itertuples():
        
        # Calculate max date .......................................................
        if ((row.Last_Updated == None) | (row.Last_Updated == '')):
            last_updated = '0'
        else:
            last_updated = row.Last_Updated
        if ((row.Last_Published == None) | (row.Last_Published == '')):
            last_published = '0'
        else:
            last_published = row.Last_Published
        if ((row.Date_of_HTML == None) | (row.Date_of_HTML == '')):
            date_of_html = '0'
        else:
            date_of_html = row.Date_of_HTML
            
        if ((last_updated >= last_published) & (last_updated >= date_of_html)):
            max_date = last_updated
        elif ((last_published >= last_updated) & (last_published >= date_of_html)):
            max_date = last_published
        elif ((date_of_html >= last_updated) & (date_of_html >= last_published)):
            max_date = date_of_html
        # ..........................................................................
        
        # Set remaining variables from current_crawl dateframe
        url_crawl_id = row.URL_Crawl_ID
        unique_url_id = row.Unique_URL_ID

        # Initialize starting and end dates/statuses with None
        starting_date = None
        starting_date_status = None
        ending_date = None
        ending_date_status = None
        
        # URL_Crawl table up until (but not including) current crawl
        sql2 = 'SELECT URL_Crawl_ID, Last_Updated, Last_Published, Date_of_HTML, Unique_URL_ID FROM URL_Crawl WHERE Crawl_ID<%s'
        previous_crawls = pd.read_sql_query(sql2 %(current_crawl_ID), con=db)

        # If row's unique_url_id exists in previous crawls (not a new node)
        if (unique_url_id in (previous_crawls['Unique_URL_ID']).tolist()):
            
            # Situation B ...................................................
            
            # Finding most recent lifetime of existing node
            existing = previous_crawls[previous_crawls['Unique_URL_ID'] == unique_url_id]
            existing_url_crawl_ids = (existing.URL_Crawl_ID).tolist()
            
            existing_in_lifetime = pd.DataFrame()
            
            for i in existing_url_crawl_ids:
                sql3 = 'SELECT * FROM Lifetime WHERE URL_Crawl_ID=%d'
                exist_in_lt = pd.read_sql_query(sql3 %(i), con=db)
                existing_in_lifetime = existing_in_lifetime.append(exist_in_lt, ignore_index=True)
            
            most_recent_lifetime = existing_in_lifetime[existing_in_lifetime.Lifetime_ID == existing_in_lifetime.Lifetime_ID.max()]
                
            # Dates/statuses from most recent lifetime - convert to Strings
            most_recent_starting_date = ((most_recent_lifetime.Starting_Date).tolist())[0]
            most_recent_starting_date_status = ((most_recent_lifetime.Starting_Date_Status).tolist())[0]
            most_recent_ending_date = ((most_recent_lifetime.Ending_Date).tolist())[0]
            most_recent_ending_date_status = ((most_recent_lifetime.Ending_Date_Status).tolist())[0]
            most_recent_lifetimeID = ((most_recent_lifetime.Lifetime_ID).tolist())[0]
                
            if (max_date != '0'):
                if ((max_date >= current_crawl_begin_date) & (max_date <= current_crawl_end_date)):
                    # Situation B.2
                    ending_date = max_date
                    ending_date_status = "Exact"
                    cur.execute("""UPDATE Lifetime SET Ending_Date=?, Ending_Date_Status=? 
                                WHERE Lifetime_ID=?""", (ending_date, ending_date_status, most_recent_lifetimeID))
                    starting_date = max_date
                    starting_date_status = "Exact"
                    ending_date = None
                    ending_date_status = None
                    cur.execute("""INSERT INTO Lifetime VALUES (null, ?, ?, ?, ?, ?)
                                """, (starting_date, ending_date, starting_date_status, ending_date_status, url_crawl_id))
                elif ((max_date < current_crawl_begin_date) & (max_date > most_recent_starting_date)):
                    # Situation B.3
                    ending_date = max_date
                    ending_date_status = "Exact"
                    cur.execute("""UPDATE Lifetime SET Ending_Date=?, Ending_Date_Status=? 
                                WHERE Lifetime_ID=?""", (ending_date, ending_date_status, most_recent_lifetimeID))
                    starting_date = max_date
                    starting_date_status = "Exact"
                    ending_date = current_crawl_begin_date
                    ending_date_status = "Estimated"
                    cur.execute("""INSERT INTO Lifetime VALUES (null, ?, ?, ?, ?, ?)
                                """, (starting_date, ending_date, starting_date_status, ending_date_status, url_crawl_id))
                elif (max_date == most_recent_starting_date):
                    # Situation B.4
                    ending_date = current_crawl_begin_date
                    ending_date_status = "Estimated"
                    cur.execute("""UPDATE Lifetime SET Ending_Date=?, Ending_Date_Status=? 
                                WHERE Lifetime_ID=?""", (ending_date, ending_date_status, most_recent_lifetimeID))
                elif ((max_date > current_crawl_end_date) | (max_date < most_recent_starting_date)):
                    # Situation B.1
                    max_date = '0'
            if (max_date == '0'):
                # Situation B.5
                ending_date = current_crawl_begin_date
                ending_date_status = "Estimated"
                cur.execute("""UPDATE Lifetime SET Ending_Date=?, Ending_Date_Status=? 
                            WHERE Lifetime_ID=?""", (ending_date, ending_date_status, most_recent_lifetimeID))
                    
        # If row's unique_url_id is a new node (not seen in previous crawls)
        else:
            
            # Situation A ...................................................
            
            if (max_date != '0'):
                if ((max_date >= current_crawl_begin_date) & (max_date <= current_crawl_end_date)):
                    # Situation A.2
                    starting_date = max_date
                    starting_date_status = "Exact"
                elif (max_date < current_crawl_begin_date):
                    # Situation A.3
                    starting_date = max_date
                    starting_date_status = "Exact"
                    ending_date = current_crawl_begin_date
                    ending_date_status = "Estimated"
                elif (max_date > current_crawl_end_date):
                    # Situation A.1
                    max_date = '0'
            if (max_date == '0'):
                # Situation A.4
                starting_date = current_crawl_end_date
                starting_date_status = "Estimated"
        
            cur.execute("""INSERT INTO Lifetime VALUES (null, ?, ?, ?, ?, ?)
                        """, (starting_date, ending_date, starting_date_status, ending_date_status, url_crawl_id))
           

Can executemany be used in this capacity? If so, I don't know the appropriate syntax for executemany - I've tried a couple of things that haven't worked yet. The database is SQLite and the program is python based.

CodePudding user response:

Kind of hard to give an exact answer without fully understanding your code. I don't quite see where you are iterating over urls/ids/etc. You will want to make a list for updates and a list for inserts outside your loop and then accumulate the sequences of params in their corresponding list. Finally after the loop you will pass each list to executemany with the fixed SQL you want to execute.

This should give you an idea of how it would work with a loop/iteration.


    #...

    # These are each a list of tuples/lists
    # ie. [(param0, ..., paramN), ..., (param0, ..., paramN)]
    params_to_update = []
    params_to_insert = []

    # For every unique url in the current crawl
    for row in current_crawl.itertuples():

        #...

            if (max_date != '0'):
                if ((max_date >= current_crawl_begin_date) & (max_date <= current_crawl_end_date)):
                    # Situation B.2
                    ending_date = max_date
                    ending_date_status = "Exact"
                    params_to_update.append((ending_date, ending_date_status, most_recent_lifetimeID))
                    starting_date = max_date
                    starting_date_status = "Exact"
                    ending_date = None
                    ending_date_status = None
                    params_to_insert.append((starting_date, ending_date, starting_date_status, ending_date_status, url_crawl_id))
                elif ((max_date < current_crawl_begin_date) & (max_date > most_recent_starting_date)):
                    # Situation B.3
                    ending_date = max_date
                    ending_date_status = "Exact"
                    params_to_update.append((ending_date, ending_date_status, most_recent_lifetimeID))
                    starting_date = max_date
                    starting_date_status = "Exact"
                    ending_date = current_crawl_begin_date
                    ending_date_status = "Estimated"
                    params_to_insert.append((starting_date, ending_date, starting_date_status, ending_date_status, url_crawl_id))

    # After for loop is done.
    # Call UPDATE for each sequence of params in this list.
    UPDATE_SQL = """UPDATE Lifetime SET Ending_Date=?, Ending_Date_Status=? WHERE Lifetime_ID=?"""
    cur.executemany(UPDATE_SQL, params_to_update)
    # Call INSERT for each sequence of params in this list.
    INSERT_SQL = """INSERT INTO Lifetime VALUES (null, ?, ?, ?, ?, ?)"""
    cur.executemany(INSERT_SQL, params_to_insert)

stackoverflow.com: using-executemany-to-update-entries-in-an-existing-sqlite3-database-using-pyt

docs.python.org: python doc executemany example

  • Related