Linux open sqlite3 file

How to properly view a .sqlite file using sqlite?

I have installed the program sqlite in order to view file types with that extension, however even after reading its help documentation and its manpage, I am still confused about how I get it to show me what I want. So how exactly do you use this program? I just want to properly view a file of that extension. I am running Ubuntu GNOME 16.04 with GNOME 3.20.

What output or view type do you want? There are also GUI database explorers which would be easier to handle, I think.

@ByteCommander: Depends what types there are, could you give me some examples? Also, I don’t really mind if it is in CLI or GUI.

@AndreaLazzarotto: I don’t know. All I know is I have .sqlite files created by Firefox that I want to view.

sqlite is the old version. I strongly suggest you use sqlite3 but you probably already did that if you installed the wonderful sqlitebrowser as suggested in the A. 🙂

1 Answer 1

Let’s start with the more user-friendly way and use the GUI tool sqlitebrowser . It offers you to easily explore a database without having to know SQL commands.

You can install it with the command

sudo apt install sqlitebrowser 

and run it from the launcher/dash/application menu or using the command

In the main window you can click Open database to open your *.sqlite file. It will then display something like this (here displaying the database of a Firefox add-on for applying user styles):

I already switched to the Browse Data tab on the left panel, where you can view the database table contents now. You select which table to display in the «Table:» combobox.

Of course you can also do this from the command-line, using e.g. sqlite3 . This method requires you to know at least a basic set of SQL commands and is better suited for advanced users or if you need to parse the output in a script.

You install sqlite3 using the command

and then run it with the following command ( -column and -header option for better readability of the output, see man sqlite3 for more info):

Then you get an interactive SQLite3 command prompt, like this:

$ sqlite3 -column -header SQLite version 3.11.0 2016-02-15 17:29:24 Enter ".help" for usage hints. sqlite> 

The first thing you must do now is to open the database file. Therefore you can use the .open SQLite command. Enter this to the sqlite> prompt (again using the same Firefox add-on database as above, you will type a different path of course):

.open "/home/bytecommander/.mozilla/firefox/gtltfeay.default/stylish.sqlite" 

Note that Tab completion works here and will help you to enter the path to your database file.

You can now see the list of loaded databases using the .databases command (file name is truncated because it’s so long):

sqlite> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main /home/bytecommander/.mozilla/firefox/gtltfeay.default/styl 

You see the database we just opened is now called main .

In the next step we list all tables of the main (default) database:

sqlite> .tables style_meta styles 

We see the two tables style_meta and styles listed.

Let’s display the style_meta table completely with all columns. Therefore we need the SQL command SELECT * FROM style_meta; (don’t forget the semicolon!):

sqlite> SELECT * FROM style_meta; id style_id name value ---------- ---------- ---------- ----------- 46 1 domain lichess.org 47 1 type site 48 3 domain lichess.org 49 3 type site 50 2 domain lichess.org 51 2 type site 53 4 type global 

To get help with commands specific to SQLite3’s interactive shell (commands starting with a period), type .help at the sqlite> prompt or read its manpage man sqlite3 . All other commands are common SQL, you should search for a basic SQL tutorial to learn them.

You can exit the interactive sqlite3 shell again using the .exit command or Ctrl + D .

Источник

Opening database file from within SQLite command-line shell

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

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?

9 Answers 9

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 

You specify the database by prefixing the table name with the database name. In the example above the prefix would be «db1», so eg SELECT * FROM db1.tbl1;

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+

The command within the Sqlite shell to open a database is .open

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 ‘\’

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

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?

Источник

Which apps read/write an SQLite 3 database in Ubuntu?

Have an app on another device which exports an SQLite v3 database. I wish to read and alter the content of that .db file. Tried Libre Office Base, no joy. Which apps will read that .db file, and edit it? Hoping for a GUI app.

Editing an SQLite3 .db file requires knowing the SQL to make changes to the sqlite3 database anyways even with apps that can read the file — is there a reason you don’t use the command line sqlite3 program to do that?

LibreOffice should work with Sqlite though. This will work on LO too: wiki.openoffice.org/wiki/Documentation/How_Tos/…

5 Answers 5

• Create and compact database files
• Create, define, modify and delete tables
• Create, define and delete indexes
• Browse, edit, add and delete records
• Search records
• Import and export records as text
• Import and export tables from/to CSV files
• Import and export databases from/to SQL dump files
• Issue SQL queries and inspect the results
• Examine a log of all SQL commands issued by the application

Install with sudo apt install sqlitebrowser

I’d like to add you call DB Browser for SQLite from the command line by typing sqlitebrowser . I think the command name is for historical reasons.

There is an application in the repositories for Ubuntu versions before 18.04 called sqliteman . It is listed in those repositories as «GUI tool for SQLite3 admin and developers alike».

I haven’t used it much but it seems like it’s a readily available GUI tool in the repositories. If you are using a version of Ubuntu earlier than 18.04, you can install it with apt-get install sqliteman .

@K7AAY This answer could still have worked for non-18.04 users — remember 16.04 and 14.04 are still alive, so you need to keep that in mind. I would have left the apt-get lines in there rather than just yank it out, while making a note this only works for versions earlier than 18.04. (Remember also this answer was written almost 4 years ago, so you have to keep in mind the historical aspect of the answer too)

@K7AAY while at the same time making something that was an answer no longer an answer. I’ve done some revisions to the original message that preserve the original content but add that this only works for earlier verisons before 18.04. (If you edit an answer and remove the bits that make it a full standalone answer you make a historic answer no longer an answer — versus just editing the answer to indicate that the command only works for versions earlier than 18.04 which preserves the original answer but clarifies it doesn’t work for newer Ubuntu versions)

Источник