How to Use Sqlite Database Browser in Windows
SQLite Database Basics with DB Browser for SQLite
Contents
- Introduction
- DB Browser User Interface
- Creating a Database Table
- Adding Rows
- Summary
Introduction to 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.
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.
don't worry about "Edit Pragmas" yet.
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.
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.
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!
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.
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.
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.
How to Use Sqlite Database Browser in Windows
Source: https://holypython.com/python-sqlite-tutorial/sqlite-database-basics-with-db-browser-for-sqlite/