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>Here's a workaround which successfully got the result you wanted.</p>

<hr>

<p>Instead of having to write <code>con = sqlite3.connect("data.db")</code> and then having to write <code>con.commit()</code> and <code>con.close()</code>, you can shorten your code to written like this:</p>

<pre><code>with sqlite3.connect("Data.db") as connection:
    c = connection.cursor()
</code></pre>

<p>This way you won't have to commit the changes and close each time you're working with a database. Just a nifty shortcut I learned. Now onto your code...
<p> Personally, I'm unfamiliar with the SQL statement <strong>ATTACH DATABASE</strong>. I would incorporate your new database at the <em>end</em> of your program instead that way you can avoid any conflicts that you aren't knowledgeable of handling (such as your OperationalError given). So first I would begin to get the desired result <em>and then</em> insert that into your new table. Your third execution statement can be rewritten like so:</p>

<pre><code>c.execute("""SELECT DISTINCT Movies.ID, Countries.Country
        FROM Movies
        CROSS JOIN Countries
        WHERE Movies.ID = Countries.ID AND Countries.Country = 'Peru'
        """)
</code></pre>

<p>This does the job, but you need to use <strong>fetchall()</strong> to return your result set in a list of tuples which can then be inserted into your new table. So you'd type this:</p>

<pre><code>rows = c.fetchall()
</code></pre>

<p>Now you can open a new connection by creating the "PeruData.db" database, creating the table, and inserting the values.</p>

<pre><code>with sqlite3.connect("PeruData.db") as connection:
    c = connection.cursor()

    c.execute("CREATE TABLE Movies (ID INT, Country TEXT)")
    c.executemany("INSERT INTO Movies VALUES(?, ?)", rows)
</code></pre>

<p>That's it.
Hope I was able to answer your question!</p>

python sqlite