5

In MySQL I have create db with name "test" and one table with name "table". Table have one column name: "A" and set as datatype INT(1).

In Python I have this code:

import MySQLdb
db = MySQLdb.connect(host="localhost",
                 user="root",
                 db="test")

cur = db.cursor()

myList = [1,2,3,4]
for row in myList:
   print row
   cur.execute("INSERT INTO table (a) VALUES (%s)" % row)

Goal from code above is after loop is finish my table "table" shoud have data like this:

|A|
|1|
|2|
|3|
|4|

But my table is empty after refresing. So my question is how to insert into table from loop in Python

Tnx

DaniKR
  • 2,418
  • 10
  • 39
  • 48
  • 1
    I see no error handling... – Karoly Horvath Jun 30 '15 at 13:15
  • @Richard I am new at Python (start learning 2 weeks ago). Now solution is same, but diffrent situation. I thought that my loop is fail, so that's way did not update... What sould I do now? I don't whant to delete it, because of users below, that got upvotes... – DaniKR Jun 30 '15 at 13:31
  • 2
    One important thing to add: Do not use the normal string expansion with `%` for SQL queries. It allows SQL injections. Use the formating the execute statement brings with it, like `cur.execute("INSERT INTO table (a) VALUES (%s)", row)`. – Klaus D. Jun 30 '15 at 13:34
  • You just leave the question up and search harder or try more things next time. Eventually we'll have enough questions of this sort that they won't get asked any more. Your question is closed as a duplicate now to prevent redundant effort and to concentrate knowledge. – Richard Jun 30 '15 at 14:20

2 Answers2

17

You did not commit your transaction. Try adding db.commit() after the loop.

Nebril
  • 3,153
  • 1
  • 33
  • 50
  • dude!!! Life saver :) upvote and accept answer. – DaniKR Jun 30 '15 at 13:19
  • man, it's look like this question might be duplicate, same problem, but driffrent situacion. I am new at Python, I did not know what my couse problem – DaniKR Jun 30 '15 at 13:28
3

You can use db.commit () after your data is loaded or set db.autocommit(True) beforehand.

Richard
  • 56,349
  • 34
  • 180
  • 251