How to commit multiple records at once the fastest way using postgres\sqlalchemy?

Question

I have a 1,000,000 records that I am trying to enter to the database, some of the records unfortunately are not standing with the db schema. At the moment when a record failed I am doing:

1. rollback to the database
2. observer the exception
3. fix the issue
4. run again. I wish to build a script which would save a side all "bad" records but would commit all the correct ones.

Of course I can commit one by one and then when the commit fail rollback and commit the next but I would pay a "run time price" as the code would run for a long time.

In the example below i have two models: File and Client.The a relation one client has many files. In the commit.py file i wish to commit 1M File objects at once or at batches (1k). at the moment I only understand when something failed when i commit at the end, is there a way to know which object are "bad" ( Integrity errors with the foreign key as example) before, i.e park a side ( in another list) but committing all the "good"

thx a lot for the help

#model.py

from sqlalchemy import Column, DateTime, String, func, Integer, ForeignKey

from . import base
class Client(base):
    __tablename__ = 'clients'
    id = Column(String, primary_key=True)

class File(base):
    __tablename__ = 'files'
    id = Column(Integer, primary_key=True, autoincrement=True)
    client_id = Column(String, ForeignKey('clients.id'))
    path = Column(String)


#init.py
import os
from dotenv import load_dotenv
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

load_dotenv()
db_name = os.getenv("DB_NAME")
db_host = os.getenv("DB_HOST")
db_port = os.getenv("DB_PORT")
db_user = os.getenv("DB_USER")
db_password = os.getenv("DB_PASSWORD")

db_uri = 'postgresql://' + db_user + ':' + db_password + '@' + db_host + ':' + db_port + '/' + db_name
print(f"product_domain: {db_uri}")


base = declarative_base()
engine = create_engine(db_uri)
base.metadata.bind = engine
Session = sessionmaker(bind=engine)
session = Session()
conn = engine.connect()

#commit.py
from . import session

def commit(list_of_1m_File_objects_records):
    #I wish to for loop over the rows and if a specific row rasie excaption to insert it to a list and handle after wards
    for file in list_of_1m_File_objects_records:
        session.add(file)
    session.commit()
    
# client:
# id
# "a"
# "b"
# "c"
# file:
# id|client_id|path
# --|---------|-------------
# 1   "a"      "path1.txt"
# 2   "aa"     "path2.txt"
# 3   "c"      "path143.txt"
# 4   "a"      "pat.txt"
# 5   "b"      "patb.txt"
# wish the file data would enter the database although it has one record "aa" which will raise integrity error

Answer 1

Since I can't comment, I would suggest to use psycopg2 and sqlAlchemy to generate the connection with the db and then use a query with "On conflict" at the end of the query to add and commit your data

Answer 2

Of course I can commit one by one and then when the commit fail rollback and commit the next but I would pay a "run time price" as the code would run for a long time.

What is the source of that price? If it is fsync speed, you can get rid of most of that cost by setting synchronous_commit to off on the local connection. If you have a crash part way through, then you need to figure out which ones had actually been recorded once it comes back up so you know where to start up again, but I wouldn't think that that would be hard to do. This method should get you most benefit for the least work.

at the moment I only understand when something failed when i commit at the end

It sounds like you are using deferred constraints. Is there a reason for that?

is there a way to know which object are "bad" ( Integrity errors with the foreign key as example)

For the case of that example, read all the Client ids into a dictionary before you start (assuming they will fit in RAM) then test Files on the python side so you can reject the orphans before trying to insert them.