How to import CSV and TSV files into SQLite with Python

Introduction

I want to import CSV and TSV data into SQLite DB with Python! For such people, it is an explanation of how to import with one script.

Please refer to the following article for what SQLite is and how to set it up. Easy local SQL environment creation technique realized by Python and SQLite

Import method

See "Explanation" below for how to use it.

script

import_data_to_sqlite.py

import sqlite3
import csv
import os

#######↓ Change the parameters here ↓#######
dbname = ''
target_table_name = ''
import_table_name = ''
is_create_table = 
is_header_skip = 
#####################################


#######↓ Import destination table DDL ↓#######
sql_script = """

"""
#######################################

class ImportSQLite():
    def __init__(self, dbname, target_table_name, import_data_name, is_create_table, is_header_skip=False, sql_create_table=None):
        """
Import csv or tsv files into SQLite
        :param dbname:text Connection destination DB name
        :param target_table_name:text Table name on the DB to be imported
        :param import_data_name:text The name of the data you want to import
        :param is_create_table:boolean Whether to create a table to import to
        :param is_header_skip:boolean Whether to skip the header of the data to be imported
        :param sql_create_table:text DDL of the table to be imported
        """
        self.dbname = dbname
        self.target_table_name = target_table_name
        self.import_data_name = import_data_name
        self.is_create_table = is_create_table
        self.is_header_skip = is_header_skip
        _, raw_delimiter = os.path.splitext(import_data_name)
        if raw_delimiter == '.csv':
            self.delimiter = ','
        elif raw_delimiter == '.tsv':
            self.delimiter = '\t'
        else:
            raise ValueError('Import file should be csv or tsv.')

        if is_create_table:
            if not sql_create_table:
                raise ValueError('It\'s necessary of sql to create table')
            else:
                self.sql_create_table = sql_create_table


    def read_import_file(self):
        with open(self.import_data_name, 'r', encoding='utf-8') as f:
            reader = csv.reader(f, delimiter=self.delimiter)
            if self.is_header_skip:
                header = next(reader)

            return [i for i in reader]


    def pick_column_num(self, import_data):
        """
Calculate the number of columns in the import file
        :param import_data: array(two-dimensional)
        :return: int
        """
        columns = []
        for raw in import_data:
            columns.append(len(raw))
        if len(set(columns)) == 1:
            return columns[0]
        else:
            raise ValueError('this import files has diffrenect column numbers.')


    def insert_csv_file(self):
        input_file = self.read_import_file()
        column = self.pick_column_num(input_file)
        val_questions = ['?' for i in range(column)]
        cur.executemany("insert into {0} values ({1})".format(self.target_table_name, ','.join(val_questions)), input_file)


if __name__ == '__main__':

    sql = ImportSQLite(
        dbname=dbname,
        target_table_name=target_table_name,
        import_data_name=import_table_name,
        is_create_table=is_create_table,
        is_header_skip= is_header_skip,
        sql_create_table=sql_script
    )

    conn = sqlite3.connect(sql.dbname)
    cur = conn.cursor()

    if sql.is_create_table:
        cur.execute('drop table if exists {};'.format(target_table_name))
        cur.execute(sql.sql_create_table)

    sql.insert_csv_file()

    conn.commit()
    conn.close()

Commentary

Setting items

Notes

--When creating the table to be imported, if you do not set the create query, it will fail with an error. --If you specify a file other than csv or tsv as input, it will be played.

Usage and sample

For example, if you have the following CSV and there is no table in the connection destination DB yet

students.csv

id,name,class,blood_type
1,Mike,Moon,B
2,Bob,Song,A
3,Gonzalez,Star,AB
4,Alex,Moon,

The setting items are as follows.

(Opening) import_data_to_sqlite.py

#######↓ Change the parameters here ↓#######
dbname = 'test.db' # '/home/user/test.db'Can also be specified in the form of
target_table_name = 'students'
import_table_name = 'students.csv' # '/home/user/students.csv'Can be specified in the form of
is_create_table = True
is_header_skip = True
#################################


#######↓ Create table DDL for import destination ↓#######
sql_script = """
create table students(
    id integer,
    name text,
    class text,
    blood_type text
);
"""
#######################################

All you have to do is start it normally

kick.sh

$ python import_data_to_sqlite.py

That's it.