How To Split Comma Delimited Values Into Multiple Rows Using Python

I'm using Python and SQLite to manipulate a database. I have an SQLite Table Movies that looks like this: | ID | Country +----------------+------------- | 1

Solution 1:

Using Python,

cursor.execute("""Select * from Movies""")
all_data = cursor.fetchall()
cursor.execute("""CREATE TABLE IF NOT EXISTS Countries
                    (ID TEXT,
                    Country TEXT)""")
for single_data in all_data:
    countries = single_data[1].split()
    for single_country in countries:
        cursor.execute("""INSERT INTO Countries VALUES(%s,"%s")"""%(single_data[0],single_country))
    conn.commit()

Solution 2:

You can solve this in pure SQLite using a common table expression.

createtable movies(id integerprimary key, country text);
insertinto movies values
(1,'USA, Germany, Mexico'), (2,'Brazil, Canada'), (3,'Peru');

createtable countries(id integer, country text);
insertinto countries
WITH split(id, country, str) AS (
    SELECT id, '', country||','FROM movies
    UNIONALLSELECT id,
    trim(substr(str, 0, instr(str, ','))),
    substr(str, instr(str, ',')+1)
    FROM split WHERE str!=''
) SELECT id, country FROM split WHERE country!=''ORDERBY id;

SELECT*FROM countries;

id|country
1|USA
1|Germany
1|Mexico
2|Brazil
2|Canada
3|Peru