Opening database file from within SQLite command-line shell
SqliteSqlite Problem Overview
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?
Sqlite Solutions
Solution 1 - Sqlite
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
Solution 2 - Sqlite
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+
Solution 3 - Sqlite
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 ''
Solution 4 - Sqlite
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
Solution 5 - Sqlite
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?
Solution 6 - Sqlite
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.
Solution 7 - Sqlite
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).
Solution 8 - Sqlite
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.