Attempting a generic SQL INSERT statement in Python

(The main version of this discussion is posted at my GitHub notebook, where it may be updated in future.)

I am working with SQLite3 in Python but am not yet at the stage of using an ORM such as SQLAlchemy. But I had the idea that I could save trouble by writing a function to produce INSERT statements generically — without having to write them out manually.

Below is what I came up with.

An example of my original inline INSERT statements

The same insertion carried out by a generic function

1
2
3
4
5
6
7

kanji_fields = OrderedDict(
        (
        ('kanji_traditional', kanji_traditional),
        ('kanji_simplified', kanji_simplified),
        ('time_of_commit', timestamp)
        ))
    insert(cursor, 'kanji', kanji_fields)

The insert() function called above

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

class CustomException(Exception):
        pass

    def insert(cursor, table_name, field_ordereddict, raise_error_or_not=True):
        """Constructs and executes generic INSERT function."""

        the_string = [str(i) for i in range(1, len(field_ordereddict)+1)]
        # prepare replacement fields for SQL field names
        the_string = '{' + '},{'.join(the_string) + '}'
        # prepare question marks for VALUES
        question_marks = '?' + ',?' \* (len(field_ordereddict) - 1)
        # construct string
        the_string = ('''INSERT INTO {0} (''' + the_string + ''')
                VALUES (''' + question_marks + ''')''')
        the_string = the_string.format(table_name, 
                *tuple(field_ordereddict.keys()))
        # execute as 
        #   cursor.execute(the_string, *tuple(field_ordereddict.values()))
        try:
            cursor.execute(the_string, tuple(field_ordereddict.values()))
        except sqlite3.IntegrityError as e:
            if raise_error_or_not:
                print(e, 'at {}'.format(tuple(field_ordereddict.values())))
                raise CustomException()
            else:
                pass

Notes on parameters:

• cursor is instantiated as:

  1 2 3

  con = sqlite3.connect() with con: cursor = con.cursor()

  and passed to this function.

• table_name is the name of the table where INSERT is to take place

• field_ordereddict is a collections.OrderedDict of (field_name, field_contents) tuples. The field_name keys populate the arguments of the INTO expression and the field_contents values populate the arguments of the VALUES expression. • raise_error_or_not is used to decide whether or not to log details about a sqlite3.IntegrityError and then raise a CustomException or not.

Results

1. Virtues. Chiefly modularity and readability. This design worked, and I was able to replace a number of long, messy in-line INSERT statements with neat insert() calls.
2. Drawbacks. However, The time required increased by a factor of 3.2, which I judge fatal to my project.
3. Going forward. Using OrderedDict to keep field-names and their contents organized improved readability:

[end]