0
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ Why do you want to store a serialized blob in a database? \$\endgroup\$ – 200_success Mar 29 '15 at 7:02
  • \$\begingroup\$ Thanks @200_success. Your Why question is very valid and one I am putting some serious thought to. The answer last night was "hey, those things are a list, let's keep them as a list", but not sure that's good enough for me this morning. Anyway, I will post my revised thoughts. In the meantime, could I toss the question back to you and ask Why not? As a beginner, I would really appreciate your thoughts. Thanks! \$\endgroup\$ – Christopher Pearson Mar 29 '15 at 16:10
2
\$\begingroup\$

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)
\$\endgroup\$
  • \$\begingroup\$ Using this approach, one would need to remove the next(f, None) statement, correct? \$\endgroup\$ – Christopher Pearson Mar 29 '15 at 20:34
  • \$\begingroup\$ can you explain why? \$\endgroup\$ – Nizam Mohamed Mar 29 '15 at 20:58
  • \$\begingroup\$ nevermind... just being a bonehead. I was going to say "because you skipped the headers", ignoring the fact that you defined them explicitly. Gonna play with this. \$\endgroup\$ – Christopher Pearson Mar 29 '15 at 21:05
1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.