SELECT from multiple tables sqlite3

Question

I have 2 tables, "vector" and "vocab." I'm trying to do this:

c.execute('SELECT value FROM vector WHERE word IN (SELECT word FROM vocab)')

I'm getting the error sqlite3.OperationalError: no such table: vocab Of course, this is because I haven't connected to the vocab table. I only connected to the vector table before:

dbname = "/Users/quantumjuker/NLP/vector.db"
conn = sqlite3.connect(dbname)
c = conn.cursor()

How can I connect to the vocab table as well so I don't receive an error?

Thanks!

Does the `vocab` table actually exist? This is what the error message is asking you to check. You don't need to connect to a table, but rather a _database_. Is the `vocab` table in the same database as the `vector` table? — Tim Biegeleisen, Sep 24 '17 at 05:24
In the folder with all my code, I have a vector.db file and a vocab.db file, so it looks like they're in separate databases. — dvn, Sep 24 '17 at 05:27
Check here: https://stackoverflow.com/questions/6824717/sqlite-how-do-you-join-tables-from-different-databases ... not sure about how to do it from Python. — Tim Biegeleisen, Sep 24 '17 at 05:28
Why is the other table in a separate database? Why don't you put both tables into the same database? — CL., Sep 24 '17 at 06:35

score 0 · Accepted Answer · answered Sep 24 '17 at 05:42

So SQLite3 has an ability to read additional SQLite data files. This is done using the ATTACH command. The nice thing about it is that it is used as an sql query. So you do something like:

c.execute("ATTACH 'vocab.db' AS 'vocabulary'");

Note the AS aliases the database to a name, not the table to a name. Once this is done you can run your query against the vocab table as well.

SELECT from multiple tables sqlite3

1 Answers1