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
See "Explanation" below for how to use it.
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()
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 |
--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.
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