Pythonistas, how do I move data from top to bottom to left to right in MySQL (think multiple values for each ID)?
Task at hand is to move data as shown in table 1 to that of table 2.
Table (1)
ID Val
-- ---
1 a
1 b
1 c
2 k
3 l
3 m
3 n
Val columns depend on the number of unique values for each ID. in this case it is 3 but it can be 20 in real world!
Table (2)
ID Val1 Val2 Val3
-- -- -- --
1 a b c
2 k
3 l m n
How am I tackling it for smaller values of Val columns (3 in this case) :
I create a temp table.
create table test(ID int not null, b int auto_increment not null,primary key(ID,b), Val varchar(255));
I then insert data in to test.
I get the following (I have to create the Val columns manually):
ID Val b
-- --- --
1 a 1
1 开发者_如何学Python b 2
1 c 3
2 k 1
3 l 1
3 m 2
3 n 3
I know that this is a tedious process with lot of manual work. This was before I fell in love with Python! An efficient solution in Python for this problem is really appreciated!
This is what I have so far
import MySQLdb
import itertools
import dbstring
cursor = db.cursor()
cursor.execute("select ID, val from mytable")
mydata = cursor.fetchall()
IDlist = []
vallist = []
finallist = []
for record in mydata:
IDlist.append(record[1])
vallist.append(record[2])
zipped = zip(IDlist,vallist)
zipped.sort(key=lambda x:x[0])
for i, j in itertools.groupby(zipped, key=lambda x:x[0]):
finallist = [k[1] for k in j]
finallist.insert(0, i)
finallist += [None] * (4 - len(finallist)) ### Making it a uniform size list
myvalues.append(finallist)
cursor.executemany("INSERT INTO temptable VALUES (%s, %s, %s, %s)", myvalues)
db.close()
the pytonic way to do this is to use itertools.groupby
import itertools
a = [(1, 'a'), (1, 'b'), (2, 'c')]
# groupby need sorted value so sorted in case
a.sort(key=lambda x:x[0])
for i, j in itertools.groupby(a, key=lambda x:x[0]):
print i, [k[1] for k in j]
return
1 ['a', 'b']
2 ['c']
精彩评论