SQLite - Writing database

This Article at OpenGenus teaches how to write data to an SQLite database using SQL statements. Explains the basic syntax and usage of INSERT INTO statement. This includes examples for inserting data into a table with and without specifying columns.

SQLite allows importing data from and exporting data to CSV files for convenient data management.

Table of content

1. Introduction
2. Database Creation
   • writing to database
   • Auto id insertion
   • Adding multiple values
3. Writing from a .cvs file
   • csv data example
4. Alternative method to write data

Introduction

In our previous article on designing databases, we learned how to design a database and create tables using a schema.sql file. The design process is crucial for improving the understanding, fetching, and storage of data. However, after designing the database, we need to write data into its structure.

Writing data into the structure of the database we have created is the next step in the process. This involves inserting, updating, and deleting data as needed. It is important to ensure that the data is properly formatted and follows the rules and constraints set forth in the database schema. This will help maintain data integrity and ensure that the database operates smoothly.

Overall, designing and writing to a database are both important steps in creating an effective and efficient data management system. By following best practices and paying attention to the details, we can ensure that our database is reliable and meets our needs.

Database creation

sqlite3 test.db

First create a new database

Now write a schema.sql file that should have commands for table creation.
you can create as many table as you like, in the similer format.

CREATE TABLE "collections" (
	"id" INTEGER,
	"title" TEXT NOT NULL,
	"accession_number" TEXT NOT NULL UNIQUE,
	"acquired" NUMERIC,
	PRIMARY KEY("id")
);

In the given scenario, certain conditions have been specified for the columns in the database table. These conditions are related to the type of content that can be stored in each column. For example, the "NOT NULL" constraint specifies that a particular column cannot be left empty or contain null values. This ensures that the data stored in the column is complete and accurate

.read schema.sql
SELECT * FROM "collections"

Now type this command to create the tables you written in schema.sql.
SELECT command would not show anything because we have not added anything to the database.

---

Writing to database

Now we have to add data to our database.

sqlite> INSERT INTO "collections" ("id", "title", "accession_number", "acquired")
   ...> VALUES (1, 'Profusion of flowers', '56.257', '1956-04-12');

The "INSERT" command is utilized to insert data into a database table. To use this command, you must first specify the name of the columns that you want to insert the data into. After that, you can use the "VALUES" command to add the values that correspond to the columns you specified. It is essential to ensure that the values are entered in the correct sequence, as they will be stored in the table accordingly.

In the given example, the "INSERT" command is used to add data to the "collections" table. The values being inserted correspond to the "id", "title", "accession_number", and "acquired" columns. The data being inserted is '1' for the "id" column, 'Profusion of flowers' for the "title" column, '56.257' for the "accession_number" column, and '1956-04-12' for the "acquired" column.

SELECT * FROM "collections"

Now fetch the data with. It will show you the data you have added inside collection.

Auto Id insertion

Here problem is we have to add id manually everytime, the problem with is you have to remember the id so you do not write the same id which will give error.
you can remove the id variable from INSERT and VALUES and let the sqlite3 insert it automatically for you.

sqlite> INSERT INTO "collections" ("title", "accession_number", "acquired")
   ...> VALUES ('Spring outing', '14.76', '1914-01-08');
sqlite> SELECT * FROM "collections"

after doing that now if you see the id is automatically added.

---

Adding multiple values

we cannot just keep adding data like this for every new data. you can use ',' to add more values into this table.

sqlite> INSERT INTO "collections" ("title", "accession_number", "acquired") 
   ...> VALUES
   ...> ('Imaginative landscpae', '56.496', NULL),
   ...> ('Peonies and butterfuly', '06.1899', '1906-01-01');
sqlite> SELECT * FROM "collections";

---

Writitng from a .csv file

To add data from a .csv file to a SQL database, we need to first remove the old database and create a new .csv file that contains the data to be imported. The data in the .csv file should be written in a comma-separated format, with each row representing a single record to be added to the database.

Once we have created the .csv file with the data we want to import, we can use SQL commands to load the data into the database. We can use the "LOAD DATA" command to read the .csv file and insert the data into the appropriate database table.

It is important to ensure that the data in the .csv file is properly formatted and matches the structure of the database table. Any errors or inconsistencies in the data could lead to issues when trying to import the data into the database.

csv data example

id, title, accession_number, acquired
1, Profusion of flowers, 56.257, 1956-04-12
2, Farmers working at dawn, 11.6152, 1911-08-03
3, Spring outing, 14.76, 1914-01-08
4, Imaginative landscape, 56.496,
5, Peonies and butterfly, 06.1899, 1906-01-01

import this csv file into your table.

.import --csv --skip 1 mfa.csv collections

To import data from a .csv file into a SQL database quickly and easily, we can use the ".import" command followed by the file type and file name. Additionally, we can include the "--skip" parameter to indicate that the first row of the .csv file should be skipped, as it may not be necessary for the database.

---

Alternative method to write data.

But what if we want to import without using --skip, because we do not have id in .csv file, we first import that data into temporary file and then and then Insert the data into desired table with INSERT and SELECT command.

.import mfa.csv --csv mfa.db temp

This will create a new table and insert the data into temp.

sqlite> INSERT INTO "collections" ("title", "accession_number", "acquired")
   ...> SELECT "title", "accession_number", "acquired" FROM "temp";

It will insert the data in temp to the collections with id auto generated.