Backup and Import tables for SQLite database data under Linux

Source: Internet
Author: User
Tags sqlite sqlite database

(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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.