如何将SQLite列中带分隔符的值分割成多个列

2 人关注

如何解析 Fruit Basket 中的逗号分隔值，并将其移至其他列。

例如，我想这样

Fruit Basket  Fruit1    Fruit2    Fruit3
------------  --------  --------  --------
Apple
Banana, Pear
Lemon, Peach, Apricot
to becomes this
Fruit Basket  Fruit1    Fruit2    Fruit3
------------  --------  --------  --------
Apple         Apple
Banana, Pear  Banana    Pear
Lemon, Pea... Lemon     Peach      Apricot
如果我不能用纯SQLite语句来做这件事，我怎么能用Python来做呢？


           
            
             你是说你的
             
              Fruit Basket
             
             列已经存储了一个逗号分隔的字符串？它是怎么来的？


           
            
             是的，该列已经存储了逗号分隔的字符串。该列是在一个先前存在的数据库中以这种方式出现的。


           
            
             OneCricketeer
            
            ：


           
            
             你总是保证在1到3个水果之间吗？如果不是，我不确定列式数据库是否是最好的主意。


           
            
             不，我不能保证这一点。你认为我是否应该把这些值分成新的行与新的列？


         
          python


         
          sqlite


          
           
            
            
             Andrej Adamenko
            
           
           
            发布于
            
            2016-09-23


          
           已采纳


          
           
            我遇到了一个类似的问题，我用纯SQL开发了一个解决方案。
           
           --------------------------------------------------
-- Select fruits split into separate columns 
-- Inspired by this article: https://www.vivekkalyan.com/splitting-comma-seperated-fields-sqliteWITH const AS (SELECT 'name' AS name, 10 AS more)
WITH RECURSIVE split(basket_id, fruitCol, str, fruitColNum) AS ( 
WITH const AS (SELECT ', ' AS delimiter)
    SELECT fruit.ROWID, '', Fruit_basket||delimiter, 0 FROM fruit, const
    UNION ALL SELECT
    basket_id,
    substr(str, 0, instr(str, delimiter)),
    substr(str, instr(str, delimiter) + length(delimiter)),
    fruitColNum+1
    FROM split,const WHERE str!=''
SELECT 
      fruit.Fruit_basket, 
      split1.fruitCol as fr_1,
      split2.fruitCol as fr_2,
      split3.fruitCol as fr_3
      -- ...
FROM fruit 
LEFT JOIN (SELECT * FROM split WHERE fruitColNum=1) as split1 ON fruit.ROWID = split1.basket_id
LEFT JOIN (SELECT * FROM split WHERE fruitColNum=2) as split2 ON fruit.ROWID = split2.basket_id
LEFT JOIN (SELECT * FROM split WHERE fruitColNum=3) as split3 ON fruit.ROWID = split3.basket_id
-- ...
The result of this SELECT is
Fruit_basket            fr_1    fr_2    fr_3
-------------           -----   -----   -----
Apple                   Apple       
Banana, Pear            Banana  Pear    
Lemon, Peach, Apricot   Lemon   Peach   Apricot


          
           
            
             
              Got it
             
             def returnFruitName(string, index):
    #Split string and remove white space
    return [x.strip() for x in string.split(',')][index]
cur.create_function("returnFruitName", 2, returnFruitName)
cur.execute("UPDATE t SET Fruit1 = returnFruitName(FruitBasket,0) WHERE FruitBasket IS NOT NULL;")
cur.execute("UPDATE t SET Fruit2 = returnFruitName(FruitBasket,1) WHERE FruitBasket IS NOT NULL;")
cur.execute("UPDATE t SET Fruit3 = returnFruitName(FruitBasket,1) WHERE FruitBasket IS NOT NULL;")


          
           
            
             
              
               对于Python来说，拆开一列是非常简单的（不确定SQLite）。这将你的DB行简化为一个字符串数组，对于SQLite的返回应该是类似的。
              
              text = [
    'Apple',
    'Banana, Pear',
    'Lemon, Peach, Apricot'
for line in text:
    cols = [c.strip() for c in line.split(',')]
    print(cols)
应该为每个字符串行输出一个数组。
['Apple']
['Banana', 'Pear']
['Lemon', 'Peach', 'Apricot']
edit:
这里有一个完整的Python脚本，可以对SQLite做你想要的事情。
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
c.execute(
    '''SELECT *
            FROM Fruits
            WHERE Fruit_Basket IS NOT NULL'''
rows = c.fetchall()
for row in rows:
    fruit_basket = row[0]
    fruits = [f.strip() for f in fruit_basket.split(',')]
    while (len(fruits) < 3):
        fruits.append('')
    print(fruits)
    update = '''UPDATE Fruits
                    SET Fruit1 = ?, Fruit2 = ?, Fruit3 = ?
                    WHERE Fruit_Basket = ?'''
    c.execute(update, fruits + [fruit_basket,])
conn.commit()
conn.close()


           
            
             
              
               
                
                 
                  
                   对了...我通过这个语句
                   
                    cursor.execute("UPDATE table SET Fruit1 = function(FruitBasket,0) WHERE FruitBasket IS NOT NULL;")
                   
                   走到了这一步，其中的伪代码
                   
                    function(FruitBasket,0)
                   
                   将水果名称字符串分割成一个列表，并在索引0处返回第一个字符串......希望这能实现


          
           
            
             
              
               
                
                 
                  
                  
                   Gilles Quenot
                  
                 
                 
                  发布于
                  
                  2016-09-23


          
           
            
             
              
               
                
                 
                  查看手册页:
                 
                 man sqlite3 | less +/-csv
Then use