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.