如何将 SQLite 列中的分隔值拆分为多列 [英] How to split delimited values in a SQLite column into multiple columns

问题描述

如何解析 Fruit Basket 中的逗号分隔值并将它们移动到其他列.

How can I parse comma separated values in Fruit Basket and move them to other columns.

例如，我想要这个

Fruit Basket  Fruit1    Fruit2    Fruit3
------------  --------  --------  --------
Apple
Banana, Pear
Lemon, Peach, Apricot

变成这个

Fruit Basket  Fruit1    Fruit2    Fruit3
------------  --------  --------  --------
Apple         Apple
Banana, Pear  Banana    Pear
Lemon, Pea... Lemon     Peach      Apricot

如果我不能用纯 SQLite 语句做到这一点，我如何用 Python 做到这一点?

If I cannot do this with a pure SQLite statement, how can I do it using Python?

推荐答案

对于 Python(不确定 SQLite)来说，将一列分开非常简单.这将您的 DB 行简化为一个字符串数组，并且应该与 SQLite 返回类似.

Pulling apart the one column is be pretty simple for Python (not sure about SQLite). This simplifies your DB row into an array of strings and should be similar for the SQLite return.

text = [
    'Apple',
    'Banana, Pear',
    'Lemon, Peach, Apricot'
]

for line in text:
    cols = [c.strip() for c in line.split(',')]
    print(cols)

应该为每个字符串行输出一个数组:

Should output an array for each string line:

['Apple']
['Banana', 'Pear']
['Lemon', 'Peach', 'Apricot']

这是一个完整的 Python 脚本，可以执行您对 SQLite 的要求:

Here's a full Python script to do what you're looking for to 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()

这篇关于如何将 SQLite 列中的分隔值拆分为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！