Transferring a CSV file to a database while merging some columns

Question

Say I am given a csv file that I want to import into an sqlite3 database. The first two columns in the csv file contain unique, important information while the rest of the columns are really a list of things. So the csv file with the header row could look like:

cucumber.csv

'important1', 'important2', 'listItem_1', 'listItem_2', 'listItem_3' '123', '9876', 'apple', 'orange', 'banana' '456', '7890', 'cabbage', 'onion', 'carrot' 

So, when I import into the database, I want to shove all but the first two columns into one column. The schema for the table in the database would then look like:

import csv import sqlite3 def main(): data_filename = 'cucumber.csv' db_filename = 'spam_mart.sqlite' SQL_create_table = """DROP TABLE IF EXISTS cucumber; CREATE TABLE cucumber ( important1 NUMBER PRIMARY KEY, important2 NUMBER, item_list TEXT ); """ SQL = """insert into cucumber(important1, important2, item_list) values (:important1, :important2, :item_list) """ with open(data_filename) as f, sqlite3.connect(db_filename) as conn: conn.executescript(SQL_create_table) cursor = conn.cursor() reader_object = csv.reader(f) next(reader_object, None) # Skip the headers row_count = 0 for row in reader_object: row_count += 1 row_dict = {} important1, important2, *item_list = row # unpack the row row_dict['important1'] = important1 row_dict['important2'] = important2 row_dict['item_list'] = repr(item_list) # convert list to string first cursor.execute(SQL, row_dict) print('Loaded {} of {} records'.format(str(row_count), str(reader_object.line_num - 1))) if __name__ == '__main__': main() 

I would normally use a csv.DictReader() object to transfer a csv file to a database, but since I was creating a list from some of the columns first, I am going with a normal csv.reader() object.

I used the repr() so that I can easily access the list again using the eval() if need be. Script works as expected. However, the whole technique seems a little clumsy to me. All honest critiques welcomed.

Answer 1

csv.DictReader accepts 'restKey' 3rd parameter.

with open(data_filename) as f, sqlite3.connect(db_filename) as conn: conn.executescript(SQL_create_table) cursor = conn.cursor() next(f, None) # Skip the headers reader_object = csv.DictReader(f,['important1','important2'],'item_list') row_count = 0 for row_dict in reader_object: row_count += 1 row_dict['item_list'] = repr(row_dict['item_list']) # convert list to string first cursor.execute(SQL, row_dict) 

Answer 2

You can use enumerate to do the counting:

for row_count, row in enumerate(reader_object, 1): 

Your printing:

print('Loaded {} of {} records'.format(str(row_count), str(reader_object.line_num - 1))) 

does not need the calls to str. Just do

print('Loaded {} of {} records'.format(row_count, reader_object.line_num - 1)) 

or, even,

print('Loaded', row_count, 'of', reader_object.line_num - 1, 'records') 

Personally the indentation of the strings is odd; I'd treat it more like a block of code and indent it singly. This is more my preference than anything.

According to PEP 8, the name SQL should be lowercase. Similarly with the name f, which should be longer. However, these are hardly big deals.