SQLite Data Import and Export

The data import source can be output by other applications or a specified text file. The specified text file is used here.

1. First, determine the data source to be imported, which is a text file in a fixed format.
2. Then, determine the target data table to be imported according to the imported file format. If the data table does not exist, you can create a corresponding data table according to the text file format to be imported.
3. Finally, run the. Import command to import data from the text file to the data table.

1. Data Source

Create a text file named data.txt under/home/ywx/YU/SQLite/, and enter the following data, separated by commas

1. ID, name, age, address, holobby
   
2. 1, Tom, 24, Beijing, football
3. 2, Liu, 27, Heibei, Fotball
4. 3, Jim, 26, Shandong, football
5. 4, Han, 28, Beijing, football
6. 5, Meng, 25, Beijing, tennis

 
2. Target data table
Create a target data table and analyze the text format. Three fields are required: ID, name, and age. However, there is a problem when selecting the data type. The ID and age are stored in the text file according to the sequence type, and it is best to represent an integer in the actual data table, therefore, the conversion from the dense data type to the integer data type is involved.
When creating a table, define the ID and age types as integer type for forced conversion. If the conversion fails during data import, you can change the ID and age types to text type.

1. Ywx @ ywx :~ /YU/SQLite $ sqlite3 test. DB
   
2. SQLite version 3.7.7.1 17:39:05
3. Enter ". Help" for instructions
4. Enter SQL statements terminated with ";"
5. SQLite> Create Table data_txt_table (ID char (10), name char (10), age char (10), address varchar (15), holobby varchar (15 ));
6. SQLite>

3. Import command

1. SQLite>. separator ","
   
2. SQLite>. Import data.txt data_txt_table
3. SQLite> select * From data_txt_table;
4. ID, name, age, address, holobby
5. 1, Tom, 24, Beijing, football
6. 2, Liu, 27, Heibei, Fotball
7. 3, Jim, 26, Shandong, football
8. 4, Han, 28, Beijing, football
9. 5, Meng, 25, Beijing, tennis
10. SQLite>

Note that before importing data, you must set the data import delimiter based on the specific shard format. For example, if you use ', 'To separate data, so you should call it first. seperator setting ',' is the delimiter.

2. View commands

. Schema command to view the structure of the specified data table

1. SQLite>. schema data_txt_table
   
2. Create Table data_txt_table (ID char (10), name char (10), age char (10), address varchar (15), holobby varchar (15 ));
3. SQLite>

2. The tables command is used to view all data tables of the current database.

1. SQLite>. Tables
   
2. Data_txt_table
3. SQLite>

3. The databases command is used to view all current databases.

1. SQLite>. Databases
   
2. SEQ name file
3. ----------------------------------------------------------------------------
4. 0 main/home/ywx/YU/SQLite/test. DB
5. 1 temp

3. Export data

Data export is also a common operation. You can export data from a specified table to an SQL script for use by other databases. You can also completely locate the data in the specified data table to the standard output, you can also import data from a specified database to another specified database,

1. Export to the specified SQL script
Export the specified data table in SQLite in the form of an SQL creation script. Specific commands

1. Ywx @ ywx :~ /YU/SQLite $ sqlite3 test. DB
   
2. SQLite version 3.7.7.1 17:39:05
3. Enter ". Help" for instructions
4. Enter SQL statements terminated with ";"
5. SQLite>. output data. SQL
6. SQLite>. Dump
7. SQLite>

 

1. Ywx @ ywx :~ /YU/SQLite $ LL
   
2. Total 16
3. Drwxr-XR-x 2 ywx 4096 ./
4. Drwxr-XR-x 7 ywx 4096 ../
5. -RW-r -- 1 ywx 602 data. SQL
6. -RW-r -- 1 ywx 2048 test. DB

2. Export the database

1. Data. SQL test. DB
   
2. Ywx @ ywx :~ /YU/SQLite $ sqlite3 test. DB ". Dump" | sqlite3 test2.db
3. Ywx @ ywx :~ /YU/SQLite $ LL
4. Total 20
5. Drwxr-XR-x 2 ywx 4096 ./
6. Drwxr-XR-x 7 ywx 4096 ../
7. -RW-r -- 1 ywx 602 data. SQL
8. -RW-r -- 1 ywx 2048 test2.db
9. -RW-r -- 1 ywx 2048 test. DB

3. Other formats, such as HTM output

1. Ywx @ ywx :~ /YU/SQLite $ sqlite3-HTML test. DB "select * From data_txt_table"> liu.htm
   
2. Ywx @ ywx :~ /YU/SQLite $ ls
3. Data. SQL liu.htm test2.db test. DB
5. Http://blog.chinaunix.net/uid-22666248-id-2182334.html