How insert into a new table from another table cross joined with another table
<p>I'm using Python and SQLite to manipulate a database. </p>

<p>I have a SQLite table <code>Movies</code> in database <code>Data</code> that looks like this:</p>

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

<p>I have a table <code>Countries</code> in the same database that looks like this</p>

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

<p>I want to insert from database <code>Data</code> all movies from Peru into a new database <code>PeruData</code> that looks like this</p>

<pre><code>| ID             | Country     
+----------------+-------------
| 2              | Peru
| 3              | Peru
</code></pre>

<p>I'm new to SQL and having trouble programming the right query. </p>

<p>Here's my attempt:</p>

<pre><code>con = sqlite3.connect("PeruData.db")
cur = con.cursor()

cur.execute("CREATE TABLE Movies (ID, Country);")
cur.execute("ATTACH DATABASE 'Data.db' AS other;")
cur.execute("\
        INSERT INTO Movies \
        (ID, Country) \
        SELECT ID, Country
        FROM other.Movies CROSS JOIN other.Countries\
        WHERE other.Movies.ID = other.Countries.ID AND other.Countries.Country = 'Peru'\

con.commit()
con.close()
</code></pre>

<p>Clearly, I'm doing something wrong because I get the error</p>

<blockquote>
  <p>sqlite3.OperationalError: no such table: other.Countries</p>
</blockquote>