Opening database file from within SQLite command-line shell

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 command within the Sqlite shell to open a database is .open

The syntax is,

sqlite> .open dbasename.db

If it is a new database that you would like to create and open, it is

sqlite> .open --new dbasename.db

If the database is existing in a different folder, the path has to be mentioned like this:

sqlite> .open D:/MainFolder/SubFolder/...database.db

In Windows Command shell, you should use '\' to represent a directory, but in SQLite directories are represented by '/'. If you still prefer to use the Windows notation, you should use an escape sequence for every '\'

Answer 4

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 5

You can simply specify the database file name in the command line:

bash-3.2 # sqlite3 UserDb.sqlite
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.

sqlite> .databases
main: /db/UserDb.sqlite

sqlite> .tables
accountLevelSettings  genres               syncedThumbs
collectionActivity    recordingFilter      thumbs
contentStatus         syncedContentStatus 

sqlite> select count(*) from genres;
10

Moreover, you can execute your query from the command line:

bash-3.2 # sqlite3 UserDb.sqlite 'select count(*) from genres'
10

You could attach another database file from the SQLite shell:

sqlite> attach database 'RelDb.sqlite' as RelDb;

sqlite> .databases
main: /db/UserDb.sqlite
RelDb: /db/RelDb_1.sqlite

sqlite> .tables
RelDb.collectionRelationship  contentStatus               
RelDb.contentRelationship     genres                      
RelDb.leagueRelationship      recordingFilter             
RelDb.localizedString         syncedContentStatus         
accountLevelSettings          syncedThumbs                
collectionActivity            thumbs                      

The tables from this 2nd database will be accessible via prefix of the database:

sqlite> select count(*) from RelDb.localizedString;
2442

But who knows how to specify multiple database files from the command line to execute the query from the command line?

Answer 6

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 7

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 8

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.