CODEWITHSUNDEEP
pythonmysqlpython-2.7mysql-python
Shadid
Shadid

Reputation: 4252

python SQL query insert shows empty rows

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

Upvotes: 0

Views: 1269

Answers (1)

DarkDiamonD
DarkDiamonD

Reputation: 682

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)

Upvotes: 1

Related Questions