How to split comma delimited values into multiple rows using Python
<p>I'm using Python and SQLite to manipulate a database. </p>

<p>I have an SQLite Table <code>Movies</code> that looks like this:</p>

<pre><code>| ID             | Country     
+----------------+-------------
| 1              | USA, Germany, Mexico 
| 2              | Brazil, Canada
| 3              | Peru
</code></pre>

<p>I would like to split the comma delimited values in <code>Country</code> and insert them into <strong>another table</strong> <code>Countries</code> so that <code>Countries</code> table looks like this </p>

<pre><code>| ID             | Country     
+----------------+-------------
| 1              | USA
| 1              | Germany
| 1              | Mexico
| 2              | Brazil
| 2              | Canada
| 3              | Peru
</code></pre>

<p>How do I do split the values from <code>Country</code> column in <code>Movies</code> table and insert them into <code>Country</code> column in <code>Countries</code> table?</p>

<p>According to <a href="http://stackoverflow.com/questions/12744155/sqlite-create-new-columns-from-column-containing">this post</a>, I can't accomplish this task using pure SQLite. How would I go about it using Python?</p>

<p>Using Python,</p>

<pre><code>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()
</code></pre>

python sqlite