Question 85335 in Docker Question posted in
The official documentation can be found here.

MariaDB – Inserting Values doesn't affect any rows – Docker

I want to insert given values from my docker app-service to the MariaDB-service.

The connection has been established because I can execute SELECT * FROM via the MariaDB.connection.cursor.

First of all I create the connection:

def get_conn() -> mariadb.connection:
    try:
        conn = mariadb.connect(
            user="XXX",
            database="XXX",
            password="XXX",
            host="db",
            port=33030,
        )
    except mariadb.Error as e:
        print(f'Error connecting to MariaDB Platform: {e}')
        sys.exit(1)

    return conn

Then I create a mariadb.connection.cursor-Object:

def get_cur() -> mariadb.connection.cursor:
    conn = get_conn()
    cur = conn.cursor()
    return cur

Finally I want to insert new values in the table testing:

def write_data():
    cursor = get_cur()
    conn = get_conn()

    cursor.execute('INSERT INTO testing (title) VALUE ("2nd automatic entry");')
    print("Executed Query")
    conn.commit()
    cursor.close()
    conn.close()
    print("Closed Connection")

    return True

To test, if the entries are inserted, I started with 1 manual entry, then executed the write_data()-function and to finish of I inserted a 2nd manual entry via the console.

After the procedure the table looks like:

MariaDB Table

Note that the ÃŽd is on AUTO_INCREMENT. So the function write_data() was not skipped entirely, because the 2nd manual entry got the id 3 and not 2.

Tags:

2

Answers


  1. 0

    It’s really easy, in a new table with new code, to unintentionally do an INSERT without a COMMIT. That is especially true using the Python connector, which doesn’t use autocommit. A dropped connection with an open transaction rolls back the transaction. And, a rolled-back INSERT does not release the autoincremented ID value for reuse.

    This kind of thing happens, and it’s no cause for alarm.

    A wise database programmer won’t rely on a set of autoincrementing IDs with no gaps in it.

    Login or Signup to reply.
  2. 0

    You’re committing a transaction in a different connection than the one your cursor belongs to.

    get_conn() creates a new database connection and returns it.

    get_cur() calls get_conn, that gets it a new connection, retrieves a cursor object that belongs to it, and returns it.

    In your main code, you call get_conn – that gives you connection A.
    Then you obtain a cursor by calling get_cur – that creates a connection B and returns a cursor belonging to it.

    You run execute on the cursor object (Connection B) but commit the connection you got in the first call (Connection A).

    PS: This was a really fun problem to debug, thanks 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search