0

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
)
Improve this question
0

1 Answer 1

Reset to default
0

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

You can read more about it in this answer.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments