python SQL query insert shows empty rows
<p>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 </p>

<pre><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
</code></pre>

<p>this outputs the following </p>

<pre><code>query executed
()
</code></pre>

<p>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.</p>

<pre><code>INSERT INTO success (id,path,hostname,time_elapsed)
VALUES (1,'sometext','hosttext',4);
</code></pre>

<p>This works fine as I can see the database got populated.</p>

<pre><code>mysql&gt; SELECT * FROM success LIMIT 5;
+----+----------+----------+--------------+
| id | path     | hostname | time_elapsed |
+----+----------+----------+--------------+
|  1 | sometext | hosttext |            4 |
+----+----------+----------+--------------+
</code></pre>

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

<p>After you are sending your INSERT record, you should commit your changes in the database:</p>

<pre><code>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()
</code></pre>

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

<p>So before you fetch the data, execute the SELECT command:</p>

<pre><code>cursor.execute("SELECT * FROM success")
rows = cursor.fetchall()
print rows
</code></pre>

<p>If you want to do it pythonic:</p>

<pre><code>cursor.execute("SELECT * FROM success")
for row in cursor:
    print(row)
</code></pre>

python mysql python-2.7 mysql-python