[SOLVED] Inserting values into MySQL database via SSH in Python works only temporarily

Issue

For a university project I rented a server to run a MySQL database on it. In Python, I connect to the server via SSH (I know, not the best solution) and try to insert values into a table in the database. If I run the INSERT statement and then SELECT directly (in my Python program), I get back the inserted value. If I run my program a second time without the INSERT statement, the value is not displayed. Also, when I look in the database table, the value was not inserted.

Here my code:

from sshtunnel import SSHTunnelForwarder
from mysql import connector

with SSHTunnelForwarder(('ip_ssh_server', 22),
                        ssh_username='ssh_username',
                        ssh_password='ssh_password',
                        remote_bind_address=('127.0.0.1', 3306)) as ssh_tunnel:

    db = connector.MySQLConnection (
        user='database_user',
        password='database_password',
        host='127.0.0.1',
        port=ssh_tunnel.local_bind_port,
        database='database_name'
    )

    cursor = db.cursor()
    query = "INSERT INTO MyTable (MyColumn) VALUES ('Hello, World!')"
    cursor.execute(query)

    query = "SELECT MyColumn FROM MyTable"
    cursor.execute(query)
    result = cursor.fetchall()

    for i in result:
        print(i)

    db.close()

Solution:

Add autocommit=True to database connection

db = connector.MySQLConnection (
    user='database_user',
    password='database_password',
    host='127.0.0.1',
    port=ssh_tunnel.local_bind_port,
    database='database_name',
    autocommit=True
)

Solution

You need to commit the changes with db.commit().

You can read more about it in this answer.

Answered By - Edward Ji

Answer Checked By - Terry (WPSolving Volunteer)