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.

create table movies(id integer primary key, country text);
insert into movies values
(1,'USA, Germany, Mexico'), (2,'Brazil, Canada'), (3,'Peru');

create table countries(id integer, country text);
insert into countries
WITH split(id, country, str) AS (
    SELECT id, '', country||',' FROM movies
    UNION ALL SELECT id,
    trim(substr(str, 0, instr(str, ','))),
    substr(str, instr(str, ',')+1)
    FROM split WHERE str!=''
) SELECT id, country FROM split WHERE country!='' ORDER BY id;

SELECT * FROM countries;

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