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)