SQLite Database Import/Export and Backup Recovery

(1) Data Import

After the SQLite database is created, you need to create table input data. In most cases, the data volume is large and manual input is not possible. You must use the Import Statement.

Import comma-separated CSV format data

First, create a table. For example, create a table test in test. DB. If the table already exists, run the ". schema" command to view the table structure, such

SQLite>. schema test. The result shows the structure of the test table. Because the data to be imported must have a similar structure, the structure of the target table must be defined.

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 with the following content:

5,560

6, 78

Use ". the import command can be used to import the data. the separator ''command converts the default Separator of SQLite, for example,". the separator is changed to a comma, which is consistent with the pre-imported data. Then, enter the following statement.

. Import test.csv Test

In this way, the test table has two more rows of records imported from the CSV file. The latest version of SQLite already uses events by default, so it is easy and efficient to import massive data.

You can import TXT files in the same way.

 

(2) Data Export

. Out out.txt

Select * from test;

. Output stdout

Create and import the out.txt file for the table test's full volume query result in the preceding three sentences. The last sentence is to locate the output to the screen and end the file export.

 

(3) back up the database

. Output [filename] is exported to a file. If the file does not exist, it is automatically created.
. Dump data export command
. Output stdout returns the output to the screen (for other operations)

 

(4) import (Restore) The database

Sqlite3 test. DB <test. SQL

In this way, the backup database is imported into the current database.