Inserting data into multiple columns in MySQL table from python

Question

For some reason, I'm unable to insert data into multiple columns in mysql table. As soon as I insert into the first column (anyone column), I find it difficult to insert into other columns. I noticed that after inserting into the first column, in the second column, the insertion starts from the point where it ended in the first column, but on the second. I tried inserting on a third column but no insertion took place.

See my code below:

week1=xrange(1,101,1) #for column1
week2=xrange(1,101,1)#for column2

cur.executemany("INSERT INTO stud(classID) VALUES(%s)", [(x,) for x in week1])

db.commit()

Thanks in advance for your suggestions.

Hi @Tupteq, I make use of MySQL-python 1,2,3 connector. I haven't read about the PEP. — Tiger1
– Tiger1, Commented Feb 19, 2014 at 11:41
Then I think you should start from reading PEP. It should answer to this question and to many other you'll have. — Tupteq
– Tupteq, Commented Feb 19, 2014 at 11:44
Thanks a lot, but please i'll need to get this done very quickly. Do you have any other suggestions other than reading the pep? — Tiger1
– Tiger1, Commented Feb 19, 2014 at 11:47

Martijn Pieters · Accepted Answer · 2014-02-19 11:47:41Z

1

You have to produce lists with two columns; if your two columns are coming from two separate iterables, use zip() to join them:

week1 = xrange(1, 101) #for column1
week2 = xrange(1, 101) #for column2

cur.executemany("INSERT INTO stud (classID, othercolumn) VALUES(%s, %s)", zip(week1, week2))
db.commit()

zip(week1, week2) produces a sequence of tuples; each a pair of values taken from both input sequences: [(week1[0], week2[0]), (week1[1], week2[1]), ...].

answered Feb 19, 2014 at 11:47

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Martijn Pieters Over a year ago

@Tiger1: Glad to have been of help!

Tiger1 Over a year ago

One more question before you go, how can I extend the row limit beyond 1000?

Martijn Pieters Over a year ago

What database adapter are you using? I'm not sure what limit this is; number of columns or number of rows for executemany()? This may be a database-specific limit.

Tiger1 Over a year ago

Its the row limit i'm talking about, mine is fixed at 1000 and I need more than that.

Martijn Pieters Over a year ago

@Tiger1: Sorry, I don't know. You can always run your insertions in matches if you cannot work around that.

Collectives™ on Stack Overflow

Inserting data into multiple columns in MySQL table from python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related