Ads 468x60px

##EasyReadMore##

19 6月, 2014

使用 Python 移動 Sqlite 的清單順序

image

建立 Table 表


移動位置聽起來只要把 id 換一下就好了,但在sqlite 下一但設了主鍵值就沒辨法取消了(mysql、msql 皆可取消主鍵值)
1.建立table 表要注意不能將主鍵值加在 id 項,因為需移動順序
2.雖有 autoincrement 會自動將 id 值加一,但因為需配合主鍵條件一起,所以也不能用
sql_cmd = 'CREATE TABLE IF NOT EXISTS ' + favorite_name + '(id INTEGER NOT NULL,\
    name TEXT NOT NULL,size INT NOT NULL,date DATETIME NOT NULL, path TEXT NOT NULL, type TEXT NOT NULL)'
cu.execute(sql_cmd)
connection.commit()

新增資料


因沒有 autoincrement 的幫助,所以只好自己找出最後一筆加入的 id(最大值) 值為多少
#get last id number to make the new id number for different type
#    EX: SELECT id FROM favorite_list1 WHERE type = 1 ORDER BY id DESC LIMIT 1;
sql_cmd = 'SELECT id FROM '+ favorite_name +' WHERE type = '+str(file_type)+' ORDER BY id DESC LIMIT 1'
tmp = cu.execute(sql_cmd)
for each_line in tmp:
    oid = each_line[0]+1 //oid 為大最值

移動順序


new_tmp: 為目標位置
old_tmp : 為原本位置

//如果是逆向移動,所以將現在的id從後面移到前面,所以原本的位置就會被往後推1格
if old_tmp > new_tmp:
    old_tmp = old_tmp+1
    print "backword change list",old_tmp,"to",new_tmp
//如果是順向移動,所以將現在的id從前面移到後面,所以目標的位置就會被往後推1格
else:
    new_tmp = new_tmp+1
    print "forward change list",old_tmp,"to",new_tmp

//先將欲移動目標位置後面的id全部向後移一格
#set all after target id to next pick
cmd = "UPDATE favorite_list%s set id = id + 1 where id >= %s" %(favorite_index,new_tmp)
print cmd
cu.execute(cmd)
connection.commit()

//再將要移動的id插入
#set the current id to target pick
cmd = "UPDATE favorite_list%s set id = %s where id = %s" %(favorite_index,new_tmp,old_tmp)
print cmd
cu.execute(cmd)
connection.commit()

//再將剛剛往後推一格的位置再還原回來
#to keep the id order is not interrupt and discut one pick from original pick
cmd = "UPDATE favorite_list%s set id = id - 1 where id >= %s" %(favorite_index,old_tmp)

 

http://www.oschina.net/question/273644_70378

0 意見:

張貼留言

 
Blogger Templates