Backup and Import tables for SQLite database data under Linux

(1) Data import

After the establishment of the SQLite database to establish table input data, most of the time the large amount of data, manual input is not possible, you must use the import statement

Import comma-delimited CSV format data

First create a table, such as the Test.db table test, if the table already exists, you can use the command ". Schema" to view the table structure, such as

Sqlite>.schema test, the result is the structure of the test table, because the data to be imported must have a similar structure, so you must understand the structure of the target table.

CREATE TABLE test (ID int primary key,value);

For example, the test table has the following data

1|34

2|99

3|990

4|390

Another CSV text file is test.csv, which reads as follows:

5,560

6,78

Use the ". Import" command to import the data, and before importing the data, use the ". Separator" command to convert the SQLite default delimiter, such as ". Separator," which changes the delimiter to a comma, consistent with the pre-import data for a smooth import, Then type the following statement

. Import Test.csv Test

So the table test has more than two lines of records imported from the CSV file, the latest version of SQLite has been used by default events, so massive data import is also easy and efficient.

The same method can be used to import txt text files.

(2) Data export and import

. Out OUT.txt

SELECT * from Test;

. Output stdout

The above three statements create and import the file OUT.txt for the full content query result of the table test, and the last sentence is to reposition the output to the screen and end the file export.

. Separator ","//Set delimiter to comma, consistent with previous export settings
. import List.txt eee//Import the contents of List.txt into the table eee

(3) Backing up the database

. output [filename] is exported to a file, and if the file does not exist, it is automatically created
. Dump Export Data command
. Output stdout back to the screen (For additional action)

(4) Import (Restore) database

Sqlite3 Test.db < Test.sql

This imports the backup database into the current database.

Backup and Import tables for SQLite database data under Linux