We must first install the SQLite3 shell application before we can use it. If SQLite is installed, then check that the program’s location has been added to our local PATH environment variable since this will make it easier to access the database file as well as other files we might need. Then, cd to the database file’s folder from a command prompt. Make sure the file is in the same directory folder as the SQLite database you generated.”
Use sqlite3 as a command; this should bring up the SQLite shell, with a screen similar to the one seen below. First, we have specified the path of the folder where our file is then created a database name “hospital.db” by using the sqlite3 command.
The database is successfully created in SQLite; you can see it by using a .databases command in the shell. Now, we have a CREATE statement which is used to create a table in the given database. We created a table with the name “patients.” This table keeps the record of the patients, which includes ID as the primary key with the data type INT, NAME with the CHAR data type, and WARD with the type TEXT.
ID INT ,
NAME CHAR(10) ,
WARD FLAOT
);
The table is being built, and the columns are being defined. Using the INSERT command, we have now included the data for each column.
INSERT INTO patients VALUES (2, 'Sadia','cardiology' );
INSERT INTO patients VALUES (3, 'Aman','neurosurgery');
INSERT INTO patients VALUES (4, 'Balaj','skin specialist');
INSERT INTO patients VALUES (5, 'Raima','urology');
Example 1
We are running SQL commands here, and the results are printed in the file. The following commands must be used to accomplish this: The .header on the command is used to turn on the result set’s heading.
To direct the sqlite3 tool to provide the result in CSV mode, specify the output mode to CSV.
The .output FILENAME command is used to save the outcome of a query to a file. Following the .output command, all subsequent queries’ results will be stored in the file you selected in the FILENAME parameter. The .once FILENAME command is used if you just wish to save the outcome of another single query to a file.
We can use the .output command without any arguments to re-display the query’s result to the standard output.
.mode csv
.output File.txt
After using the above command, we have a SELECT statement that displays the table and the table record within our file.
The SQL command runs here when we open our file, and the table record is displayed.
Example 2
As in the above example, we have shown how to insert the record from the shell to the file. Here, we have a SQL select command in the file. This command fetches all the records in the table patients. We have used the method .read file name.
The File.txt script reads from the current folder/directory in this case. If in a separate directory, specify the entire path. By using the .read File.txt in the SQLite shell, the table is viewable here as we have executed the command from the file.
FROM patients
ORDER BY NAME
LIMIT 4;
Example 3
Here also, we are executing the SQL command from the file but with the .timer command in the shell. You can use the .timer command to activate or deactivate CPU time measurements. SQLite will monitor and report the operating system duration needed to process each query if this option is enabled. Firstly, we have the UPDATE command, which sets the NAME = UZAMA with the WHERE CLAUSE, which has the ID = 1 in the File.txt.
Now, we have set the header on command and also the timer on command. Then, use the .read File.txt to display the results of the command from the File.txt. Notice that the timer is on and showing the record with the updated values as follows:
.mode COLUMN
.timer ON
.read File.txt
Example 4
Here’s a different approach to using the .read command. The following SQL command from the File.txt, which we are executing in the SQLite shell.
Without having to open SQLite, you can use the .read command. The contrast between this case and the last one is that the last one was done after you’d already connected to SQLite from within SQLite. This example, however, is executed from outside of SQLite. We have given the command “sqlite3 hospital.db “.read File.txt” within the folder where the database and file are present. The results are represented in the image below.
Example 5
This is our third method for executing SQL commands from the file. The command in File.txt is as follows:
WHERE ID < 3
ORDER BY NAME ASC;
When connecting to SQLite and opening a database with sqlite3, the following approach forwards the information to the database file.
Example 6
Another way to execute SQL commands from the file is by using the init method. Below is the SQL command, which will be executed by the init method.
Hence, the command “sqlite3 hospital.db -init File.txt” also executes the SQL command from the file as shown in the snapshot.
Conclusion
In the end, we have shown the four different ways which execute SQL commands from the file. First, we have to place the file and the database on the same path; then, we can execute the several SQL commands successfully. Each method is implemented by displaying the output.
