Checking for duplicate values in database with Python 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 re
from bs4 import BeautifulSoup
from datetime import datetime, timedelta
from models import db, Notes

def 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,}(?&lt;!-)\.?)|'
            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) &lt;= 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 re
from bs4 import BeautifulSoup
from datetime import datetime, timedelta
from models import db, Notes

def 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,}(?&lt;!-)\.?)|'
            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) &lt;= 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?
In python checking membership of an item in a set is extremely fast. 

I suggest you use sets.