CODEWITHSUNDEEP
postgresqlsqlalchemybulkinsert
GroovyRatul
GroovyRatul

Reputation: 1

Removing/Disabling the SQL: INSERT INTO statement in SQL Alchemy bulk insert

I am trying to insert more than 10K records in postgres sql database and using the SQL Alchemy bulk insert functionality using insert statement for this.

        insert_stmt = (
            insert(table)
            .values(records)
            .returning(getattr(table, primary_key))
        )

where table is table name and records is list of dictionaries.

While it works perfectly for happycases, in case of error scenarios in one of the records out of thousands, It logs the whole sql statement and is flooding the whole terminal with that one statement. It looks something like this

sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type integer: "abc" LINE 1: ...user', '2024-05-11T19:48:10.8932'::timestamp), ('abc', 'jo... ^

[SQL: INSERT INTO emp (user_id, first_name, last_name, created_by, create_time) VALUES (%(location_number_m0)s, %(user_id_m0)s, %(role_title_m0)s, %(first_name_m0)s, %(last_name_m0)s, %(created_by_m0)s, %(create_time_m0)s), (%(location_number_m1)s, %(user_id_m1)s, %(role_title_m1)s, %(first_name_m1)s, %(last_name_m1)s, %(created_by_m1)s, %(create_time_m1)s), (%(location_number_m2)s, %(user_id_m2)s, %(role_title_m2)s, %(first_name_m2)s, %(last_name_m2)s, %(created_by_m2)s, %(create_time_m2)s), (%(location_number_m3)s, %(user_id_m3)s, %(role_title_m3)s, %(first_name_m3)s, %(last_name_m3)s, %(created_by_m3)s, %(create_time_m3)s), (%(location_number_m4)s, %(user_id_m4)s, %(role_title_m4)s, %(first_name_m4)s, %(last_name_m4)s, %(created_by_m4)s, %(create_time_m4)s), (%(location_number_m5)s, %(user_id_m5)s, %(role_title_m5)s, %(first_name_m5)s, %(last_name_m5)s, %(created_by_m5)s, %(create_time_m5)s), (%(location_number_m6)s, %(user_id_m6)s, %(role_title_m6)s, %(first_name_m6)s, %(last_name_m6)s, %(created_by_m6)s, %(create_time_m6)s), (%(location_number_m7)s, %(user_id_m7)s, %(role_title_m7)s, %(first_name_m7)s, %(last_name_m7)s, %(created_by_m7)s, %(create_time_m7)s), (%(location_number_m8)s, %(user_id_m8)s, %(role_title_m8)s, %(first_name_m8)s, %(last_name_m8)s, %(created_by_m8)s, %(create_time_m8)s)]

How can i get rid of this statement, its difficult to navigate to the root cause which is above the huge sql statement.

I tried to disable the logs using echo=False, echo_pool=False while create the engine. But its not working.

I tried setting the levels as well to INFO, ERROR, DEBUG, but didn't work as well

Also is there any way to print only the faulty row instead of the whole insert into statement?

Upvotes: 0

Views: 45

Answers (1)

GroovyRatul
GroovyRatul

Reputation: 1

UPDATE - I utilized other way of performing the insert many statements as depicted in SQLAlchemy2.0 which uses something as session.execute(insert(table), records), Using this it internally hides the statements and mentions the no.of characters hidden.

Upvotes: 0

Related Questions