Db browser for sqlite как пользоваться

Db browser for sqlite как пользоваться

Одним из популярных графических клиентов для SQLite является программа DB Browser for SQLite , которая доступна по адресу https://sqlitebrowser.org/.

Для установки графического клиента перейдем на страницу загрузок https://sqlitebrowser.org/dl/, где мы можем найти различые варианты пакетов для разных операционных систем и архитектур:

Например, в моем случае ОС — Windows 64x, поэтому я выбираю пакет DB Browser for SQLite — Standard installer for 64-bit Windows , который представляет установщик программы для 64-битной Windows.

После установки запустим программу:

Нам откроется следующее окно

В рамках данной программы для управления базой данных мы можем использовать как графические возможности, так и запросы SQL. Так, создадим базу данных. Для этого нажмем на кнопку New Database (или через меню File -> New Database ). Далее нам будет предложено указать место и имя новой бд. Например, в моем случае файл базы данных будет называться people.db

Впоследствии созданную базу данных можно будеть открыть с помощью кнопки Open Database или через меню File -> Open Database .

После этого нам будет предложено создать первую таблицу

На этом окне в поле Table введем в качестве названия таблицы Users .

На вкладке Fields , которая содержит определения столбцов, нажмем на кнопку Add . И после нажатия в таблицу чуть ниже кнопки будут добавлять определения столбцов таблицы. Для первого столбца укажем в качестве имени Name , а в качестве типа — TEXT . Для второго столбца укажем в качестве имени Age , а в качестве типа — INTEGER .

То есть у нас будет таблица User со столбцами Name и Age.

После определения столбцов нажмем на кнопку OK, и программа сгенерирует новую таблицу:

Для управления данными перейдем на вкладку Browse Data . По умолчанию у нас нет никаких данных

Добавим в таблицу одну строку. Для этого на панели инструментов нажмем на кнопку и в добаленную строку введем какие-нибудь данные:

После добавления данных (впрочем как и после их изменения и удаления) для подтверждения изменений нажмем на панели инструментов на кнопку Write Changes (или на пункт меню File -> Write Changes ). И данные будут записаны в базу данных.

Выполнение запросов SQL

Также для упавления таблицами и данными можно использовать запросы SQL. Для написания и выполнения запроса SQL перейдем на вкладку Execute SQL

После этого в центральной части программы откроется окно для ввода скрипта SQL. Введем в него следующую команду:

Здесь создается таблица users2, которая фактически аналогична ранее созданной таблице users. Она также имеет два столбца name и age. И в нее добавляюся две строки.

Для выполнения этого кода нажмем на синюю стрелку на панели инструментов. И после запуска кода SQL мы сможем увидеть отчет о его выполнения

После добавления мы можем получить данные. Для этого введем следующий код:

Using DB Browser for SQLite

Below the toolbar is a 4-tabbed pane for; Database Structure, Browse Data, Edit Proagmas and Execute SQL . Initially theese will be quite empty as we haven’t created or opened a database yet.

In general we will see how each of these are used as we go through the lesson.

except of the Edit Pragmas tab which deals with system wide parameters which we won’t want to change.

We are only really interested in the DB Schema tab

Initial change to the layout

• Layout is quite flexible
• Drag and drop into positions
• individual tabs on the bottom pane closed directly from the pane and re-opened from the menu View item.

Modify panes for lessons

• We will make a couple of initial changes to the layout of the screen.
• These will be retained across sessions.

From the View menu item un-select the Edit Database Cell icon to the left of the text. This will make the pane close and the bottom pane will be expanded automatically to fill the space.

From the View menu item select preferences and select the Data Browser tab.

• Towards the botton there is a section dealing with Field colors.
• You will see three bars below the word Text, to the right there are in fact three invisible bars for the Background.
• Click in the area for the Backgroung color for NULL .
• A color selector window will open, select Red .
• The bar will turn Red.

This is now the default background cell colour that will be used to display NULL values in you tables.

close the preference window by clicking OK .

Opening a database

• For this lesson we will be making extensive use of the SN7577 database.
• If you do not already have a copy of this database you can download it from here.

Download data

Database sn7577 info

https://discover.ukdataservice.ac.uk/#7577 * Title: Audit of Political Engagement 11, 2013 * Political behaviour and attitudes — Politics

To open the database in DB Browser do the following;

clichk on the open database button in the toolbar.

Navigate to where you have stored the database file on your local machine, select it and click open.

In particular what tables are in the database and the fields and their types which are in each table.

These are the same actions that are available from the toolbar at the top of the tab.

You can also select ther table you wish to Browse directly from here.

Our interest is in analysing exiosting data not creating or deleting data.

Running SQL Queries

We will be running queries extensively in future episodes.

For now we will just provide an outline of the environment.

This is the SQL editor pane into which you will type your queries.

On the toolbar there are five buttons. Left to right they are:

• Open Tab (creates a new tab in the editor)
• Open SQL file (allows you to load a prepared file of SQL into the editor — the tab takes the name of he file)
• Save SQL file (allows you to save the current contents of the active pane to the local file system)
• Execute SQL (Executes all of the SQL statements in the editor pane)

Execute current line (Actually executes whatever is selected)

If you only have a single statement you don’t need it, but it might be considered best practice to always include it.

The bottom pane is for messages about the execution, either an error message or an indication of how many rows were returned by the query.

Below is a simple examople query and the results.

The second 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 statement that was executed.

This includes saving it to a csv file.

Creating a database

As well as opening (connecting) to existing databases it is also possible to create new SQLite databases and tables using DB Browser.

Although the new database is empty, in that there are no tables in it, the .sqlite file itself is not empty.

You can cancel this as we will be going through the create table process in a later episode.

Write Changes & Revert Changes

• During your DB Browser session, if you create or delete any tables the changes are not automatically written to the database file.
• When you try to end the session (i.e. close the application) you will be asked if you want to save the changes you have made.
• Alternatively you can explicitly save changes or revert changes during a session by use of the Write Changes and Revert Changes buttons on the toolbar.
• Once written the changes are permanent (there is no concept of multiple undo like you might have in other programs).
• Revert Changes will take you back to the last Written copy.

Key Points

The DB Browser for SQLite application 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 you can write and run SQL queries and view the results

Db browser for sqlite как пользоваться

• SQLite
• Базы данных

• blog
• it
• programming

Db browser for sqlite как пользоваться

Using DB Browser for SQLite

In Windows the installation of DB Browser does not create a desktop icon. To explicitly launch the application after installing it, use the windows button (bottom 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 like this, the panes may be in a different configuration;

A small menu system consisting of File, Edit, View and Help. Below the menu system is a toolbar with four options; New Database, Open Database, Write Changes and Revert Changes. Below the toolbar is a 4-tabbed pane for; Database Structure, Browse Data, Edit Pragmas and Execute SQL. Initially these will be quite empty as we haven’t created or opened a database yet. In general we will see how each of these are used as we go through the lesson with the exception of the Edit Pragmas tab which deals with system wide parameters which we won’t want to change.

On the right hand side there are two further panes, at the top is the Edit Database Cell pane which is grayed out. Below it is a 3-tabbed pane for DB Schema, SQL log and Remote. We are only 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-hand side can be dragged and dropped into any position, the individual tabs on the bottom pane closed directly 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 be retained across sessions.

1. From the View menu item un-select the ‘Edit Database Cell’ icon to the left of the text. This will make the pane close and the bottom pane will be expanded automatically to fill the space.
2. a) On Windows, From the View menu item select ‘preferences’ and select the Data Browser tab.
3. b) On Mac, From the “DB Browser for SQLite” menu item select ‘preferences’ and select the Data Browser tab.

Towards the bottom there is a section dealing with Field colors. You will see three bars below the word Text, to the right there are in fact three invisible bars for the Background. Click in the area for the Background color for NULL. A colour selector window will open, select Red. The bar will turn Red. This is now the default background cell colour that will be used to display NULL values in you tables. We will discuss the meaning of NULL values in a table in a later episode.

You can now close the preference window by clicking OK.

Opening a database

For this lesson we will be making extensive use of the SQL_SAFI database. If you do not already have a copy of this database you can download it from here.

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 machine, select it and click open.

When you open the database, the ‘Database Structure’ tab on the left and the ‘DB Schema’ pane on the right will look very similar. However the ‘DB Schema’ pane is only there to allow 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 table.

The ‘Database Structure’ tab on the left allows you to initiate actions on the tables. If you right click on a table name in the ‘DB Schema’ pane, nothing happens. However, if you do the same in the ‘Database Structure’ menu you will be given a set of possible actions. These are the same actions that are available from the toolbar at the top of the tab.

If you select ‘Browse Table’, the data from the table is loaded into the ‘Browse Data’ pane from where it can be examined or filtered. You can also select the table you wish to Browse directly from here.

There are options for ‘New Record’ and ‘Delete Record’. As our interest is in analysing existing data not creating or deleting data, it is unlikely that you will 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 environment.

In the left hand pane if you select the Execute SQL tab, you will be presented with a three paned window and a small toolbar. The top pane is itself tabbed with the initial tab labeled ‘SQL 1’. This is the SQL editor pane into which you will type your queries.

Below is a simple example query and the results.

Notice that the query has been written over multiple lines. This is commonly done to aid readability. The second 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 statement that was executed.

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

• Open Tab (creates a new tab in the editor)
• Open SQL file (allows you to load a prepared file of SQL into the editor — the tab takes the name of he file)
• Save SQL file (allows you to save the current contents of the active pane to the local file system)
• Execute SQL (Executes all of the SQL statements in the editor pane)
• Execute current line (Actually executes whatever is selected)
• Save Results (Either to a CSV file or as a database view. We will look at views in a later episode)
• Find (Text in the editor window)
• Find & Replace (Text in the editor window)

Because it is possible to have and execute multiple SQL statements in the same editor pane, each must be terminated with a ‘;’. If you only have a single statement you don’t need it, but it might be considered best practice to always include it.

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

Creating a database

As well as opening (connecting) to existing databases it is also possible to create new SQLite databases and tables using DB Browser. To create a database click the New Database button from the main toolbar (also available from the File menu). You will initially be asked for a name for the database and where you want to save it. It is saved as a single file. You can choose your own extension but ‘sqlite’ is recommended. If you do not provide a default, then a ‘.db’ extension will be used. Although the new database is empty, in that there are no tables in it, the .sqlite file itself is not empty.

Once you have saved the database file the Create Table wizard will open allowing you to create a table. You can cancel this as we will be going through the create table process in a later episode.

Write Changes & Revert Changes

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

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

SQLite Database Basics with DB Browser for SQLite

DB Browser for SQLite is a useful, free, open-source software that can be used to manage, process and explore databases.

I think it will be very useful in this SQL tutorial series and we will use it as a complimentary tool to understand databases and database operations.

Let’s get to know DB Browser for SQLite and create a very simple database which we will also use in other tutorials in this tutorial series.

1- Exploring the User Interface

DB Browser for SQLite is a useful, free, open-source software that can be used to manage, process and explore databases.

I think it will be very useful in this SQL tutorial series and we will use it as a complimentary tool to understand databases and database operations.

Let’s get to know DB Browser for SQLite and create a very simple database which we will also use in other tutorials in this tutorial series.

DB Browser for SQLite Overview

Let’s focus on the left side of the DB Browser window. (For what’s going on on the right side as well as plotting you can see Native Database Visualization Tutorial.)

Database management can be nicely done through three tabs on top left of the pass:

• Database Structure: You can create, list and delete database tables. This is where the journey begins.
• Browse Data: Here, you can browse values in tables. Add rows and change values.
• Execute SQL: You can execute SQL scripts from this tab.

2- Creating Table on the Database

Realize how database is a file in your system and not a server. This is how SQLite works unlike MySQL which works on a server. Let’s create a table on your local database.

Click New Database, if you haven’t already. Enter file name and path. You can go with any extension between sqlite, sqlite3, db or db3. In this context extension is just an indicator and it won’t affect your databases content or the way it works but going with sqlite3 is of tradition as it signals we’ll be using sqlite3.

DB Browser for SQLite Creating Database

We can then give our first table a name and add fields to it. Let’s add Name and Age fields and name the table Fam as below. It makes sense to enter Name column as TEXT data type and Age column as INTEGER data type in the database.

SQLite uses a super straightforward data type system which makes lots of sense if you like simplicity. These data types are:

• INTEGER: reserved for integers
• TEXT: reserved for strings
• BLOB: reserved for big files
• REAL: reserved for floats (numbers with decimals)
• NUMERIC: exists to increase compatibility and can accept any remaining data type that’s used by other database systems.

DB Browser for SQLite Table Settings

You can also see the query that will be used to create this table below the user form. This is very useful for getting familiar with SQL. It might seem strange at first if you’ve never used it but after a few database practices you get used to it very quickly. SQL syntax is very small but powerful compared to other languages so it can be learned in a short amount of time.

When you click OK, first table will be created. Congratulations!

DB Browser for SQLite Database Structure

Under Database Structure tab you can now see the table named Fam and two columns in it: Name and Age. You can also see their data types which is convenient.

3- Adding Rows and Inserting Values to Database Table

If you look at Browse Data tab we can also see the columns and headers here. But there are no rows yet. Let’s go ahead and enter some values manually.

All you have to do is click Insert New Record on the Table button usually next to the little printer icon. You can also see in the pic below.

When you click this button new rows will be added but they will be NULL which means no value. You can simply click on a cell once wait one second and then click again. This will enable it for entering values.

DB Browser for SQLite Browse Data

Note: You can also use Edit Database Cell tab on the right side of the window to enter values manually. Don’t forget to click Apply after typing the values in that window.

Either way, let’s enter about 5 rows and fill them accordingly. You can also add a Profession column. As well.

Realize that to add or remove columns you have to go back to the Database Structure tab, click on the table and then click modify table. This is a little nuance about databases and it shows that adding columns affects database table structure while adding rows is an operation about adding values and doesn’t affect the structure of the table.

Sample SQLite Database Overview

Summary

That’s all it really takes to create a database. But databases really shine in heavy lifting and automation which we will cover in the upcoming tutorials in this series.

I remember learning SQL from an inconsiderate source where one massive database project was thrown on the table and project instructions were as explicit as “go figure!”.

That’s why we tried to make these database and SQL tutorial series as clean and gradual as possible so you can master database operations, learn how to use them in Python and have most of your SQL questions answered in shortest amount of time without getting too frustrated.

In the following tutorial we will enter values to this table and continue building up.