Insert values into SQL column with mysql-python

Question

I am trying to insert values into a column of a SQL table, using MySQLdb in Python 2.7. I am having problems with the command to insert a list into 1 column.

I have a simple table called 'name' as shown below:

+--------+-----------+----------+--------+
| nameid | firstname | lastname | TopAdd |
+--------+-----------+----------+--------+
| 1      | Cookie    | Monster  |        |
| 2      | Guy       | Smiley   |        |
| 3      | Big       | Bird     |        |
| 4      | Oscar     | Grouch   |        |
| 5      | Alastair  | Cookie   |        |
+--------+-----------+----------+--------+

Here is how I created the table:

CREATE TABLE `name` (
  `nameid` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) DEFAULT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  `TopAdd` varchar(40) NOT NULL,
  PRIMARY KEY (`nameid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

Here is how I populated the table:

INSERT INTO `test`.`name`
(`firstname`,`lastname`)
VALUES
("Cookie","Monster"),
("Guy","Smiley"),
("Big","Bird"),
("Oscar","Grouch"),
("Alastair","Cookie");

DISCLAIMER: The original source for the above MySQL example is here.

Here is how I created the a new column named TopAdd:

ALTER TABLE name ADD TopAdd VARCHAR(40) NOT NULL

I now have a list of 5 values that I would like to insert into the column TopAdd as the values of that column. Here is the list.

vals_list = ['aa','bb','cc','dd','ee']

Here is what I have tried (UPDATE statement inside loop):

vals = tuple(vals_list)
for self.ijk in range (0,len(self.vals)):
            self.cursor.execute ("UPDATE name SET TopAdd = %s WHERE 'nameid' = %s" % (self.vals[self.ijk],self.ijk+1))

I get the following error message:

Traceback (most recent call last):
  File "C:\Python27\mySQLdbClass.py", line 70, in <module>
    [Finished in 0.2s with exit code 1]main()
  File "C:\Python27\mySQLdbClass.py", line 66, in main
    db.mysqlconnect()
  File "C:\Python27\mySQLdbClass.py", line 22, in mysqlconnect
    self.cursor.execute ("UPDATE name SET TopAdd = %s WHERE 'nameid' = %s" % (self.vals[self.ijk],self.ijk+1))
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'aa' in 'field list'")

Is there a way to insert these values into the column with a loop or directly as a list?

You might want to fix the DDL in this question; your `INSERT` query will fail if the table is defined without a default value for `TopAdd`. Since you add that column later it looks like it shouldn't be included in the original `CREATE TABLE` statement. — Air, Dec 02 '14 at 16:38

score 2 · Accepted Answer · edited Nov 24 '14 at 17:22

2

Try This:

vals_list = ['aa','bb','cc','dd','ee']

for i, j in enumerate(vals_list):

 self.cursor.execute(("UPDATE test.name SET TopAdd = '%s' WHERE nameid = %s" % (str(j),int(i+1))

edited Nov 24 '14 at 17:22

Christian Gollhardt

16,510
17
74
111

answered Nov 24 '14 at 17:17

Amy Roy

36
1

That worked! I do not understand how your method is working but mine is not. I tried this: `...% (str(self.vals_list[ijk]),int(ijk+1)))` but I am still getting the same error message. When I check the `type()` I get ` ` for my method. Using your method, `type()` gives me the same result. If that is the case, then my method should also work. Is there something that I am doing wrong? – edesz Nov 24 '14 at 17:58
1

have you changed TopAdd ='%s' in your method, in your code it was TopAdd=%s – Amy Roy Nov 25 '14 at 13:01
Thanks. You approach is a lot better. This answers my question. – edesz Nov 27 '14 at 04:35

score 1 · Answer 2 · edited May 23 '17 at 11:56

One problem is here:

for self.ijk in range (0,len(self.vals)):

The range function is creating a list of integers (presumably, the list [0, 1, 2, 3, 4]). When iterating over a collection in a for loop, you bind each successive item in the collection to a name; you do not access them as attributes of an instance. (It also seems appropriate to use xrange here; see xrange vs range.) So the self reference is non-sensical; beyond that, ijk is a terrible name for an integer value, and there's no need to supply the default start value of zero. KISS:

for i in range(len(self.vals)):

Not only does this make your line shorter (and thus easier to read), using i to represent an integer value in a loop is a convention that's well understood. Now we come to another problem:

self.cursor.execute ("UPDATE name SET TopAdd = %s WHERE 'nameid' = %s" % (self.vals[self.ijk],self.ijk+1))

You're not properly parameterizing your query here. Do not follow this advice, which may fix your current error but leaves your code prone to wasted debugging time at best, SQL injection and/or data integrity issues at worst. Instead, replace the % with a comma so that the execute function does the work safely quoting and escaping parameters for you.

With that change, and minus the quotation marks around your column name, nameid:

query = "UPDATE name SET TopAdd = %s WHERE nameid = %s;"
for i in range(len(self.vals)):
    self.cursor.execute(query, (self.vals[i], i + 1))

Should work as expected. You can still use enumerate as suggested by the other answer, but there's no reason to go around typecasting everything in sight; enumerate is documented and gives exactly the types you already want:

for i, val in enumerate(self.vals):
    self.cursor.execute(query, (val, i + 1))

Thank you. Although I marked the above answer as accepted, my final query ended up being very similar to the above answer and to yours(2nd one) - using `enumerate()`. I found this approach to be much more intuitive to understand than mine. Also, thank you for correcting me regarding my usage of `self()` - this was a real misunderstanding on my part and I can simplify code going forward. Thanks again. — edesz, Nov 27 '14 at 04:34

Insert values into SQL column with mysql-python

2 Answers2