Opening database file from within SQLite command-l

Question

I'm using the SQLite Command Line Shell. As documented, I can open a database by supplying it as an argument to the executable:

sqlite3 data.db

I cannot figure out how to open a database file from within the tool after having invoked it without supplying the file as a command-line argument (if I, say, double-click sqlite3.exe in Windows). What is the command within the SQLite shell tool to specify a database file?

Answer 1

You can attach one and even more databases and work with it in the same way like using sqlite dbname.db

sqlite3
:
sqlite> attach "mydb.sqlite" as db1;

and u can see all attached databases with .databases

where in normal way the main is used for the command-line db

.databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main                                                                       
1    temp                                                                       
2    ttt              c:\home\user\gg.ite                                   

Answer 2

I think the simplest way to just open a single database and start querying is:

sqlite> .open "test.db"
sqlite> SELECT * FROM table_name ... ;

Notice: This works only for versions 3.8.2+

Answer 3

The same way you do it in other db system, you can use the name of the db for identifying double named tables. unique tablenames can used directly.

select * from ttt.table_name;

or if table name in all attached databases is unique

select * from my_unique_table_name;

But I think the of of sqlite-shell is only for manual lookup or manual data manipulation and therefor this way is more inconsequential

normally you would use sqlite-command-line in a script

Answer 4

create different db files using
      >sqlite3 test1.db
sqlite> create table test1 (name text);
sqlite> insert into test1 values('sourav');
sqlite>.exit
      >sqlite3 test2.db
sqlite> create table test2 (eid integer);
sqlite> insert into test2 values (6);
sqlite>.exit
      >sqlite
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test1.db
sqlite> select * from test1;
sourav
sqlite> .open test2.db
sqlite> select * from test1;
Error: no such table: test1
sqlite> select * from test2;
6
sqlite> .exit
      >

Thank YOU.

Answer 5

Older SQLite command-line shells (sqlite3.exe) do not appear to offer the .open command or any readily identifiable alternative.

Although I found no definitive reference it seems that the .open command was introduced around version 3.15. The SQLite Release History first mentions the .open command with 2016-10-14 (3.15.0).

Answer 6

I wonder why no one was able to get what the question actually asked. It stated What is the command within the SQLite shell tool to specify a database file?

A sqlite db is on my hard disk E:\ABCD\efg\mydb.db. How do I access it with sqlite3 command line interface? .open E:\ABCD\efg\mydb.db does not work. This is what question asked.

I found the best way to do the work is

• copy-paste all your db files in 1 directory (say E:\ABCD\efg\mydbs)
• switch to that directory in your command line
• now open sqlite3 and then .open mydb.db

This way you can do the join operation on different tables belonging to different databases as well.