I'm working on a Python application where I read and extract data from an HTML file. The data is stored in a list, and the number of items in a list is, on average, 50,000+. The items from the list are added to a database. I cannot allow duplicate datetime values in one of the columns, and so, I have to check if a specific value exists in the database already, and if it does, keep incrementing it until there's no duplicates.Here's what my code looks like (I'm using SQLAlchemy):import refrom bs4 import BeautifulSoupfrom datetime import datetime, timedeltafrom models import db, Notesdef import_file(filename, user):    file = open(filename, 'r')    html = file.read()    file.close()    soup = BeautifulSoup(html, lxml)    for link in soup.find_all('a'):        validurl = re.compile(            r'^(?:[a-z0-9\.\-]*)://'            r'(?:(?:[A-Z0-9](?:[A-Z0-9-]{0,61}[A-Z0-9])?\.)+(?:[A-Z]{2,6}\.?|[A-Z0-9-]{2,}(?<!-)\.?)|'            r'\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|'            r'\[?[A-F0-9]*:[A-F0-9:]+\]?)'            r'(?::\d+)?'            r'(?:/?|[/?]\S+)$', re.IGNORECASE)        url = validurl.match(link.get('href'))        if url:                url = link.get('href')                if len(url) <= 2000:                    if link.get('add_date'):                        date = datetime.utcfromtimestamp(int(link.get('add_date')))                        while Notes.query.filter_by(user=user, added_on=date).first():                            date += timedelta(0, 1)                        title = link.string                        import_notes = Notes()                        import_notes.main_url = url                        import_notes.title = title                        import_notes.user = user                        import_notes.added_on = date                        db.session.add(import_notes)                        db.session.commit()However, this code runs very slowly. I calculated how long it was taking per insert, and it came out at exactly 13 seconds per insert. I ran a profiler, and the filter_by query was taking the majority of the time. I figured it was because the script had to run a query so many times per item, and so I changed my code to use a static list instead:import refrom bs4 import BeautifulSoupfrom datetime import datetime, timedeltafrom models import db, Notesdef import_file(filename, user):    file = open(filename, 'r')    html = file.read()    file.close()    soup = BeautifulSoup(html, lxml)    for link in soup.find_all('a'):        validurl = re.compile(            r'^(?:[a-z0-9\.\-]*)://'            r'(?:(?:[A-Z0-9](?:[A-Z0-9-]{0,61}[A-Z0-9])?\.)+(?:[A-Z]{2,6}\.?|[A-Z0-9-]{2,}(?<!-)\.?)|'            r'\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|'            r'\[?[A-F0-9]*:[A-F0-9:]+\]?)'            r'(?::\d+)?'            r'(?:/?|[/?]\S+)$', re.IGNORECASE)        url = validurl.match(link.get('href'))        if url:                url = link.get('href')                if len(url) <= 2000:                    if link.get('add_date'):                        date = datetime.utcfromtimestamp(int(link.get('add_date')))                        while Notes.query.filter_by(user=user, added_on=date).first():                            date += timedelta(0, 1)                        title = link.string                        import_notes = Notes()                        import_notes.main_url = url                        import_notes.title = title                        import_notes.user = user                        import_notes.added_on = date                        db.session.add(import_notes)                        db.session.commit()I thought this might make the code run faster, but there was little to no change in runtime. For comparison, if I remove the duplicate check, I get ~300 inserts per second. I cannot let duplicate datetime values into the database. What can I do to make my code faster?
Checking for duplicate values in database with Python
python;performance;database
In python checking membership of an item in a set is extremely fast. I suggest you use sets.