python SQL query insert shows empty rows

Question

I am trying to do a insert query in the SQL. It indicates that it succeed but shows no record in the database. Here's my code

conn = MySQLdb.connect("localhost",self.user,"",self.db)
cursor = conn.cursor()
id_val = 123456;
path_val = "/homes/error.path"
host_val = "123.23.45.64"
time_val = 7
cursor.execute("INSERT INTO success (id,path,hostname,time_elapsed) VALUES (%s,%s,%s,%s)", (id_val, path_val,host_val,time_val))

print "query executed"
rows = cursor.fetchall()
print rows

this outputs the following

query executed
()

it gives me no errors but the database seems to be empty. I tried my SQL query in the mysql console. executed the following command.

INSERT INTO success (id,path,hostname,time_elapsed)
VALUES (1,'sometext','hosttext',4);

This works fine as I can see the database got populated.

mysql> SELECT * FROM success LIMIT 5;
+----+----------+----------+--------------+
| id | path     | hostname | time_elapsed |
+----+----------+----------+--------------+
|  1 | sometext | hosttext |            4 |
+----+----------+----------+--------------+

so I am guessing the SQL query command is right. Not sure why my cursor.execute is not responding. Could someone please point me to the right direction. Can't seem to figure out the bug. thanks

Answer 1

After you are sending your INSERT record, you should commit your changes in the database:

cursor.execute("INSERT INTO success (id,path,hostname,time_elapsed) VALUES (%s,%s,%s,%s)", (id_val, path_val,host_val,time_val))
conn.commit()

When you want to read the data, you should first send your query as you did through your interpreter.

So before you fetch the data, execute the SELECT command:

cursor.execute("SELECT * FROM success")
rows = cursor.fetchall()
print rows

If you want to do it pythonic:

cursor.execute("SELECT * FROM success")
for row in cursor:
    print(row)