Cannot insert into OCI Autonomus database via python oracledb

Question

I want to insert rows into an OCI Autonomous database (version 23c) via python oracledb package:

def _populate_database( auth: dict, args: dict):
    with oracledb.connect(user=auth["username"], password=auth["password"], dsn=args["connectionstring"]) as conn:
        with conn.cursor() as cursor:
            cursor.execute(f"""INSERT ALL
                            INTO ADMIN.regular VALUES (3203952, 555, 'O', 10.4434, 0, 'my text name', 'my addr 23')
                            INTO ADMIN.regular VALUES (3203953, 5, 'O', 10.25, 4, 'my text name', 'my addr 23')
                            INTO ADMIN.regular VALUES (3203955, 3, 'O', 11.12, 3, 'my text name', 'my addr 23')
                            INTO ADMIN.regular VALUES (3203956, 5, 'O', 10.25, 4, 'my text name', 'my addr 23')
                            INTO ADMIN.regular VALUES (3203957, 27, 'O', 13.25, 4, 'my text name', 'my addr 23')
                            INTO ADMIN.regular VALUES (3203958, 5, 'O', 11.12, 4, 'my text name', 'my addr 23')
                            INTO ADMIN.regular VALUES (3203959, 5, 'O', 10.75, 3, 'my text name', 'my addr 23')
                            INTO ADMIN.regular VALUES (3203960, 5, 'O', 12.4435, 3, 'my text name', 'my addr 23')
                            INTO ADMIN.regular VALUES (3203962, 5, 'O', 11.62, 3, 'my text name', 'my addr 23')
                            INTO ADMIN.regular VALUES (3203963, 5, 'N', 11.5, 3, 'my text name', 'my addr 23')
                            SELECT * FROM dual
                            """)

The command succeeded without error, log, etc, but when I execute a select statement, I don't see any rows.

Can anybody help me, what did I wrong?

Answer 1

You are missing a commit! You need to add a call to conn.commit() prior to the context block completing. The context manager for with oracledb.connect() will automatically close the connection and roll back any changes that have been made, so you need to call conn.commit() if you want those changes to persist. You can also set the autocommit flag if you want to avoid the additional round trip.