How to Use Sqlite Database Browser to Read Sms

Using DB Browser for SQLite

Overview

Teaching: 10 min
Exercises: 0 min

Questions

• What does the DB Browser for SQLite allow me to do?

Objectives

• Empathize the layout of the DB Browser for SQLite and the key facilities that information technology provides

• Connect to databases

• Create new databases and tables

• Run SQL queries

• Export the results of queries

Launching DB Browser

In Windows the installation of DB Browser does not create a desktop icon. To explicitly launch the application later on installing it, utilize the windows button (lesser left of screen) and type in 'DB Browser' in the search bar and selecting the application when it appears.

The Initial screen

The initial screen of DB Browser will look something similar this, the panes may be in a different configuration;

There is;

A pocket-size menu system consisting of File, Edit, View and Help. Beneath the carte arrangement is a toolbar with four options; New Database, Open Database, Write Changes and Revert Changes. Beneath the toolbar is a 4-tabbed pane for; Database Construction, Browse Data, Edit Pragmas and Execute SQL. Initially these volition be quite empty as we haven't created or opened a database yet. In general nosotros volition encounter how each of these are used as nosotros go through the lesson with the exception of the Edit Pragmas tab which deals with organisation wide parameters which nosotros won't want to modify.

On the right hand side there are ii further panes, at the pinnacle is the Edit Database Jail cell pane which is grayed out. Below information technology is a 3-tabbed pane for DB Schema, SQL log and Remote. We are simply really interested in the DB Schema tab.

Initial changes to the layout.

The overall layout of DB Browser is quite flexible. The panes on the right-manus side can be dragged and dropped into whatever position, the individual tabs on the bottom pane closed straight from the pane and re-opened from the menu View item.

We will make a couple of initial changes to the layout of the screen. These will exist retained beyond sessions.

1. From the View card detail un-select the 'Edit Database Cell' icon to the left of the text. This will make the pane close and the lesser pane will be expanded automatically to fill the space.
2. a) On Windows, From the View carte item select 'preferences' and select the Data Browser tab.
3. b) On Mac, From the "DB Browser for SQLite" carte du jour item select 'preferences' and select the Data Browser tab.

Towards the bottom at that place is a department dealing with Field colors. You will encounter 3 bars below the discussion Text, to the correct there are in fact three invisible confined for the Background. Click in the area for the Background color for NULL. A colour selector window will open, select Scarlet. The bar volition plough Reddish. This is now the default groundwork cell colour that volition exist used to display NULL values in you tables. Nosotros will hash out the meaning of NULL values in a table in a afterward episode.

You lot can now close the preference window by clicking OK.

Opening a database

For this lesson we will be making extensive utilise of the SQL_SAFI database. If you exercise non already take a copy of this database you can download it from hither.

To open the database in DB Browser do the following;

1. Click on the 'open database' button in the toolbar.
2. Navigate to where you have stored the database file on your local auto, select it and click open up.

When you open the database, the 'Database Construction' tab on the left and the 'DB Schema' pane on the right volition expect very similar. However the 'DB Schema' pane is only there to let you to see the details of the schema for the tables. In particular what tables are in the database and the fields and their types which are in each tabular array.

The 'Database Structure' tab on the left allows you lot to initiate actions on the tables. If you correct click on a table name in the 'DB Schema' pane, zip happens. All the same, if you do the same in the 'Database Structure' bill of fare y'all will be given a set of possible deportment. These are the aforementioned actions that are bachelor from the toolbar at the superlative of the tab.

If you select 'Browse Table', the data from the table is loaded into the 'Scan Data' pane from where it can exist examined or filtered. You can also select the table you wish to Browse straight from hither.

There are options for 'New Record' and 'Delete Record'. Equally our interest is in analysing existing information not creating or deleting data, it is unlikely that you volition want to use these options.

Running SQL Queries

We will be running queries extensively in future episodes. For now we will just provide an outline of the environs.

In the left hand pane if you select the Execute SQL tab, y'all volition be presented with a three paned window and a pocket-size toolbar. The top pane is itself tabbed with the initial tab labeled 'SQL one'. This is the SQL editor pane into which you will type your queries.

Beneath is a simple example query and the results.

Detect that the query has been written over multiple lines. This is normally done to assistance readability. The 2nd pane has the tabular results, and the bottom pane has a message indicating how many rows were returned, how long it took and a copy of the SQL argument that was executed.

On the toolbar at the top there are eight buttons. Left to correct they are:

• Open Tab (creates a new tab in the editor)
• Open SQL file (allows yous to load a prepared file of SQL into the editor - the tab takes the name of he file)
• Relieve SQL file (allows you to salvage the current contents of the agile pane to the local file system)
• Execute SQL (Executes all of the SQL statements in the editor pane)
• Execute current line (Really executes whatever is selected)
• Save Results (Either to a CSV file or every bit a database view. We volition wait at views in a later on episode)
• Find (Text in the editor window)
• Find & Replace (Text in the editor window)

Considering it is possible to take and execute multiple SQL statements in the same editor pane, each must be terminated with a ';'. If you but have a unmarried argument you don't need information technology, but it might be considered best practice to ever include it.

The pane below the editor is the Results pane. The results of running your query volition appear here in a simple tabular format. The bottom pane is for messages about the execution, either an error bulletin or an indication of how many rows were returned past the query.

Creating a database

Also every bit opening (connecting) to existing databases it is besides possible to create new SQLite databases and tables using DB Browser. To create a database click the New Database push button from the main toolbar (besides available from the File bill of fare). Y'all volition initially exist asked for a proper name for the database and where yous want to save information technology. It is saved as a unmarried file. You can choose your own extension but 'sqlite' is recommended. If you lot do non provide a default, then a '.db' extension volition be used. Although the new database is empty, in that there are no tables in it, the .sqlite file itself is not empty.

Once y'all have saved the database file the Create Tabular array magician will open allowing you to create a table. You tin cancel this as we will be going through the create table procedure in a afterwards episode.

Write Changes & Revert Changes

Much of our SQL piece of work involves looking at existing data using SQL queries and possibly writing out the results to a CSV file, in full general we will not be changing the contents of the database.

Even so if, during your DB Browser session, you lot were to create or delete a table or create a view, and so the changes are not automatically written to the database file.

When you try to end the session (i.eastward. close the application) in which you have made such changes, then you will be asked if you lot want to save the changes you have made. Alternatively you can explicitly salve changes or revert changes during a session by use of the Write Changes and Revert Changes buttons on the toolbar. One time written the changes are permanent (in that location is no concept of multiple 'disengage' similar you might accept in other programs). Revert Changes will accept you back to the concluding Written copy.

Key Points

• The DB Browser for SQLite awarding allows you to connect to an existing database or create a new database

• When connected to a database you can create new tables

• When connected to a database yous can write and run SQL queries and view the results

• Y'all can relieve the results of a query to a file

baumannanythe1962.blogspot.com

Source: https://datacarpentry.org/sql-socialsci/02-db-browser/index.html

Share this post