How to handle primary key conflict in Sqlite using Python

Here is an example showing

  1. ignoring primary key conflicting record in SQLite, when doing batching inserting and commit at the end
  2. record the error in a log file with timestamp
import sqlite3
import logging
from datetime import datetime

# Set up logging to a file
log_filename = f"sqli-{datetime.now().strftime('%Y%m%d%H%M%S')}.log"
logging.basicConfig(filename=log_filename, level=logging.ERROR, format='%(asctime)s - %(levelname)s - %(message)s')

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create a table with a primary key
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    )
''')

# Insert data into the table (batch insert)
data_to_insert = [
    ('John Doe',),
    ('Jane Smith',),
    ('John Doe',),  # This will cause a primary key conflict
    ('Bob Johnson',),
]

for record in data_to_insert:
    try:
        cursor.execute("INSERT INTO users (name) VALUES (?)", record)
    except sqlite3.IntegrityError as e:
        error_message = f"Ignoring record due to conflict: {record}\nError: {e}"
        print(error_message)
        logging.error(error_message)
        

# Commit the batch insert
conn.commit()

# Query the data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

# Print the results
for row in rows:
    print(row)

cursor.close()
conn.close()