This project is developed in Python 3.7.7 with Visual Studio Code on Windows 10.
The format of import source file should be csv, tsv, or psv.
And, the data in source file will be imported to SQLite database.
You download this project files from GitHub repository.
And put the dowonload project to your any directory.
> cd your-directory
> git clone https://github.com/ks-tec/XsvToSQLite
You can change setting value according to your environment.
They are set directly in "XsvToSQLite.bat".
| short keyword | normal keyword | defalut value | note |
|---|---|---|---|
| -s | --source_file | ..\prefecture_code.csv | import source XSV file path |
| -o | --output_database | ..\prefecture_code.db | output database file path |
| -t | --output_table | prefecture_code | table name to creating |
| -hs | --is_header_skip | (specified) | action flag for skip header, specified is true |
| -cs | --is_create_table | (specified) | action flag for create table, specified is true |
| -d | --ddl_create_table | "hoge" | create table DDL, "hoge" is dummy |
The following example is in batch file.
python XsvToSQLite.py ^
--source_file=..\prefecture_code.csv ^
--output_database=..\prefecture_code.db ^
--output_table=prefecture_code ^
--is_header_skip ^
--is_create_table ^
--ddl_create_table="hoge"
The following is supported format of XSV data file.
| file type | file extension | data delimiter | ASCII code |
|---|---|---|---|
| comma separated value | csv | , | 0x2c |
| tab separated value | tsv | \t | 0x09 |
| pipe separated value | psv | | | 0x7c |
In addition, if you set is_create_table set True and is_header_skip set True, sql_create_table is built from header row, and is automatic droped and created.
But, if you set is_create_table set True and is_header_skip set False, sql_create_table must set DDL (Data Definition Language) with SQL (Structured Query Language).
| is_create_table | is_header_skip | sql_create_table | creating a table to import to |
|---|---|---|---|
| True | True | not necessary and not valid | automatic table creation from header row |
| True | False | necessary | should be specified a table creation DDL |
| False | True | not valid | use existing table and append data |
| False | False | not valid | use existing table and append data |
The imported table has no key columns.
This is because the key columns of the source file to be imported cannot be determined.
And, In general, key columns cannot be represented in XSV file.
This is by design.
The following is an example of this project run.
> cd C:\VSCode\Projects\XsvToSQLite
> dir ../
directory: C:\VSCode\Projects
Mode LastWriteTime Length Name
---- ------------- ------ ----
d----- 2020/12/17 12:20 XsvToSQLite
-a---- 2020/12/16 18:44 689 prefecture_code.csv
> python XsvToSQLite.py ^
--source_file=..\prefecture_code.csv ^
--output_database=..\prefecture_code.db ^
--output_table=prefecture_code ^
--is_header_skip ^
--is_create_table ^
--ddl_create_table="hoge"
XsvToSQLite initializing, and instantiating.
begin: Begining Transaction. (IMMEDIATE)
insert_from_file: Inserting all data from file.
read_import_file: Reading data from import source.
make_create_query: Creating the table creation DDL.
create_from_ddl: Droping and creating table.
count_column_nums: Counting columns number.
insert_from_file: Complete of insert all data.
Work is complete.
> dir ../
directory: C:\VSCode\Projects
Mode LastWriteTime Length Name
---- ------------- ------ ----
d----- 2020/12/17 12:20 XsvToSQLite
-a---- 2020/12/16 18:44 689 prefecture_code.csv
-a---- 2020/12/17 12:31 8192 prefecture_code.db
Changed file name 'main.py' to 'XsvToSQLite.py'.
And, appended batch file for executing.
Defined IsolationLevel by namedtuple.
Explicitly start a transaction.
And, that was achieved by adding method 'begin' to XsvToSQLite class.
First release.
This project is developed in Python 3.7.7.
This project is under MIT license.
Copyright (c) 2020, ks-tec.