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>

<p>The current error is probably caused by a typo or other minor problem. I could create the databases described here and successfully do the insert after fixing minor errors: a missing backslash at an end of line and adding qualifiers for the selected columns.</p>

<p>But I also advise your to use aliases for the tables in multi-table selects. The code that works in my test is:</p>

<pre><code>cur.execute("\
        INSERT INTO Movies \
        (ID, Country) \
        SELECT m.ID, c.Country\
        FROM other.Movies  m CROSS JOIN other.Countries c \
        WHERE m.ID = c.ID AND c.Country = 'Peru'")
</code></pre>

python sqlite