How To Split Delimited Values In A Sqlite Column Into Multiple Columns
How can I parse comma separated values in Fruit Basket and move them to other columns. For example, I want this Fruit Basket Fruit1 Fruit2 Fruit3 ------------ -------- --
Solution 1:
Got it
def returnFruitName(string, index):
#Split stringandremove white space
return [x.strip() for x instring.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;")
Solution 2:
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']
edit:
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()
Solution 3:
Check the man page :
man sqlite3 | less +/-csv
Then use
sqlite ... -csv | ...
the output will be quit more easy to parse
Solution 4:
I encountered a simular problem and I developed a solution in pure 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)WITHRECURSIVE split(basket_id, fruitCol, str, fruitColNum) AS (
WITH const AS (SELECT', 'AS delimiter)
SELECT fruit.ROWID, '', Fruit_basket||delimiter, 0FROM fruit, const
UNIONALLSELECT
basket_id,
substr(str, 0, instr(str, delimiter)),
substr(str, instr(str, delimiter) + length(delimiter)),
fruitColNum+1FROM 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
LEFTJOIN (SELECT*FROM split WHERE fruitColNum=1) as split1 ON fruit.ROWID = split1.basket_id
LEFTJOIN (SELECT*FROM split WHERE fruitColNum=2) as split2 ON fruit.ROWID = split2.basket_id
LEFTJOIN (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
Post a Comment for "How To Split Delimited Values In A Sqlite Column Into Multiple Columns"