How to import CSV and TSV files into SQLite with Python

Introduction

sqlite-183454_640.png

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

Variable name Mold Setting method
dbname Text DB name of the connection destination. Specify in the form of a path.
target_table_name Text The name of the table on the DB to be imported
import_table_name Text The name of the data you want to import. Specify in the form of a path.
is_create_table Boolean(True or False) Whether to create a table to be imported
is_header_skip Boolean(True or False) Whether to skip the header of the data to be imported

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.

Recommended Posts

How to import CSV and TSV files into SQLite with Python
Import tsv with Python
How to read a CSV file with Python 2/3
Scraping tabelog with python and outputting to CSV
Summary of how to import files in Python 3
Download and import files with Splunk external python
How to convert JSON file to CSV file with Python Pandas
[Python] How to split and modularize files (simple, example)
Write to csv with Python
[Python] How to play with class variables with decorator and metaclass
How to upload files to Cloud Storage with Firebase's python SDK
How to do Bulk Update with PyMySQL and notes [Python]
How to get into the python development environment with Vagrant
How to log in to AtCoder with Python and submit automatically
Python: How to use async with
[Python] Write to csv file with Python
How to use SQLite in Python
Output to csv file with Python
How to get started with Python
[Python] How to import the library
Handle Excel CSV files with Python
How to use FTP with Python
How to calculate date with python
Reading and writing CSV with Python
[Python / Ruby] Understanding with code How to get data from online and write it to CSV
How to change Django's SQLite3 uploaded to python anywhere with GUI only
How to use functions in separate files Perl and Python versions
How to make a surveillance camera (Security Camera) with Opencv and Python
How to deal with errors when installing Python and pip with choco
How to use Python with Jw_cad (Part 2 Command explanation and operation)
How to build Python and Jupyter execution environment with VS Code
[Python] How to convert db file to csv
[Linux] How to subdivide files and folders
How to package and distribute Python scripts
How to convert csv to tsv in CLI
Put protocol buffers into sqlite with python
Fractal to make and play with Python
How to work with BigQuery in Python
[Python] How to use import sys sys.argv
Read and write csv files with numpy
How to do portmanteau test with python
Reading and writing JSON files with Python
How to display python Japanese with lolipop
Trying to handle SQLite3 with Python [Note]
Read Python csv and export to txt
python: How to use locals () and globals ()
[Part1] Scraping with Python → Organize to csv!
How to enter Japanese with Python curses
[Python] How to calculate MAE and RMSE
How to use Python zip and enumerate
Convert HEIC files to PNG files with Python
Compress python data and write to sqlite
[Python] How to deal with module errors
How to read CSV files in Pandas
How to use is and == in Python
How to install python3 with docker centos
[Introduction to Python] How to judge authenticity with if statement (True and None)
How to get the date and time difference in seconds with python
Read CSV file with Python and convert it to DataFrame as it is
Connection between flask and sqlite3
Create Heroku, Flask, Python, Nyanko bulletin boards with "csv files"
How to upload with Heroku, Flask, Python, Git (4)