Data import and export of SQLite

The source of the data import can be output from other applications, or it can be a specified text file, where the specified text file is used.
1. First, determine the imported data source, here is the text file to be imported, in a fixed format.
2. Then, according to the imported file format, determine the target data table that you want to import, if not, you can create a corresponding data table according to the text file format to be imported.
3. Finally, execute the. Import command to import the data from the text file into the data table.

1. Data sources

Under/home/ywx/yu/sqlite/, create a text file named Data.txt and enter the following data, separated by commas

1. Id,name,age,address,hobby
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 Sheet
Here you create a Target data table, which, by parsing the text format, requires 3 fields, each of which is id,name,age. However, there is a problem with data type selection, and the ID and age are stored in the text file by character type, and it is actually best to represent the integer in the data table, so there is a question about converting a character data type to an integer data type.
When the table is created, the type of ID and age are defined as integral types, cast, and if the conversion fails when the data is imported, you can change the ID and age type to text type.

1. [Email protected]:~/yu/sqlite$ sqlite3 test.db
2. SQLite version 3.7.7.1 2011-06-28 17:39:05
3. Enter '. Help ' for instructions
4. Enter SQL statements terminated with a ";"
5. sqlite> CREATE TABLE data_txt_table (id char), name Char (TEN), Age char (TEN), address varchar (15), hobby varchar ));
6. Sqlite>

3. Import commands

1. sqlite> . Separator ","
   
2. sqlite> . Import data.txt data_txt_table
3. Sqlite> select * from Data_txt_table;
4. Id,name,age,address,hobby
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>

It is important to note that before the data is imported, it is necessary to set the data import spacer according to the data's specific format, for example, to use the ', ' to interval data in the text data, so it should be called first. Seperator set ', ' as the spacer.

2. View commands

The. 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), name Char (TEN), Age char (TEN), address varchar (+), hobby varchar (15));
3. Sqlite>

2.. tables command to view all data tables for the current database

1. sqlite> . Tables
   
2. Data_txt_table
3. Sqlite>

3. Databases command 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. Data export

Data export is also a common operation, you can export the data in the specified table into SQL script for other databases to use, but also the data in the specified data table can be fully targeted to the standard output, you can also import the data in the specified database into another specified database, etc.

1. Export to the specified SQL script
Exports the data table specified in SQLite as a SQL creation script, with specific commands

1. [Email protected]:~/yu/sqlite$ sqlite3 test.db
2. SQLite version 3.7.7.1 2011-06-28 17:39:05
3. Enter '. Help ' for instructions
4. Enter SQL statements terminated with a ";"
5. sqlite> . Output Data.sql
6. sqlite> . Dump
7. Sqlite>

1. [Email protected]:~/yu/sqlite$ ll
2. Total 16
3. Drwxr-xr-x 2 ywx ywx 4096 2011-08-13 23:15./
4. Drwxr-xr-x 7 ywx ywx 4096 2011-08-13 20:53.. /
5. -rw-r--r--1 ywx ywx 602 2011-08-13 23:17 Data.sql
6. -rw-r--r--1 ywx ywx 2048 2011-08-13 22:44 test.db

2. Database Export

1. Data.sql test.db
2. [Email protected]:~/yu/sqlite$ sqlite3 test.db ". Dump" | Sqlite3 test2.db
3. [Email protected]:~/yu/sqlite$ ll
4. Total 20
5. Drwxr-xr-x 2 ywx ywx 4096 2011-08-13 23:20./
6. Drwxr-xr-x 7 ywx ywx 4096 2011-08-13 20:53.. /
7. -rw-r--r--1 ywx ywx 602 2011-08-13 23:17 Data.sql
8. -rw-r--r--1 ywx ywx 2048 2011-08-13 23:20 test2.db
9. -rw-r--r--1 ywx ywx 2048 2011-08-13 22:44 test.db

3. Other formats, such as: HTM format output

1. [Email protected]:~/yu/sqlite$ sqlite3 -html test.db  "SELECT * from data_txt_table"  > liu.htm
   
2. [email  protected]:~/yu/sqlite$ ls
3. data.sql liu.htm test2.db test.db
4. http://blog.chinaunix.net/ uid-22666248-id-2182334.html