Importing XML dump of Stack Overflow questions and answers into SQLite3

Question

I have a 60gb+ XML file and, as you can see, I am using a Python script to extract the data and execute 'INSERT' statements to update my database.

Being that the file is so large, will I run into speed issues once all is migrated to the database? Or would I need to add more relationships between tables to make the schema more cohesive?

To note: the XML file includes all StackOverflow Questions and responses since 2008.

import sqlite3
from xml.etree.ElementTree import iterparse


# Takes the path of my XML file, parses line-by-line, returns a
# dictionary of a single posting that is checked and inserted into
# 1 of 3 tables and repeats 100 million + times for each posting

def parse_and_move(filename, path):

  doc = iterparse(filename, ('start', 'end'))
  next(doc)
  name = 'parents'
  con = sqlite3.connect("{}2.db".format(name))
  cur = con.cursor()

  # Below are the 3 tables. The first includes posts (from Stack
  # Overflow) that have accepted answers.
  # The second includes posts that do not have accepted answers.
  # And the third includes all responses posts categorized in the
  # former 2 tables.

  cur.executescript('''
    CREATE TABLE postsWithAnswers(
                                Id integer primary key,
                                PostTypeId,
                                AcceptedAnswerId,
                                CreationDate,
                                Score,
                                ViewCount,
                                Body,
                                OwnerUserId,
                                OwnerDisplayName,
                                LastEditorUserId,
                                LastEditorDisplayName,
                                LastEditDate,
                                LastActivityDate,
                                Title,
                                Tags,
                                AnswerCount,
                                CommentCount,
                                FavoriteCount,
                                ClosedDate,
                                CommunityOwnedDate,
                                FOREIGN KEY(
                                AcceptedAnswerId) REFERENCES
                                responses(Id)
                                );


    CREATE TABLE postsWithOutAnswers(
                                Id integer primary key,
                                PostTypeId,
                                CreationDate,
                                Score,
                                ViewCount,
                                Body,
                                OwnerUserId,
                                OwnerDisplayName,
                                LastEditorUserId,
                                LastEditorDisplayName,
                                LastEditDate,
                                LastActivityDate,
                                Title,
                                Tags,
                                AnswerCount,
                                CommentCount,
                                FavoriteCount,
                                ClosedDate,
                                CommunityOwnedDate
                                );

    CREATE TABLE responses(
                                Id integer primary key,
                                PostTypeId,
                                ParentId,
                                CreationDate,
                                Score,
                                Body,
                                OwnerUserId,
                                OwnerDisplayName,
                                LastEditorUserId,
                                LastEditorDisplayName,
                                LastEditDate,
                                LastActivityDate,
                                CommentCount,
                                CommunityOwnedDate,

                                FOREIGN KEY(ParentId) REFERENCES postsWithAnswers(Id)
                                );

                                ''')

i=0 # used to track the number of records added to database


# Parsing XML 'tree' - the document is not nested at all. It is line
#  by line.

for event, xml_element in doc:
    if event == 'start':
        dict_of_posting_data = xml_element.attrib

        if dict_of_posting_data:

            '''

            The following 3 blocks  of code check:

            a.) If the Post is a question or a response (PostTypeId = 1)
            b.) If the Post question does NOT have a response (
                   PostTypeId = 1, and the XML line includes an
                   "AcceptedAnswerId" category.
            c.) If the post is a response to a question (PostTypeId = 2)

            Finally, every possible field is set to None (info for a given
            posting in a given category differs) - the dict is then updated
            by the actual data pulled from the XML file.


            '''

            if int(dict_of_posting_data['PostTypeId']) == 1 and 'AcceptedAnswerId' in dict_of_posting_data:
                li1 = ['Id',
                       'PostTypeId',
                       'AcceptedAnswerId',
                       'CreationDate',
                       'Score',
                       'ViewCount',
                       'Body',
                       'OwnerUserId',
                       'OwnerDisplayName',
                       'LastEditorUserId',
                       'LastEditorDisplayName',
                       'LastEditDate',
                       'LastActivityDate',
                       'Title',
                       'Tags',
                       'AnswerCount',
                       'CommentCount',
                       'FavoriteCount',
                       'ClosedDate',
                       'CommunityOwnedDate'
                       ]

                li_non1 = [None]*len(li1)
                base_d1 = dict(zip(li1, li_non1))
                base_d1.update(dict_of_posting_data)


                cur.execute(
                    'INSERT INTO postsWithAnswers VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                                                        (base_d1.get('Id'),
                                                         base_d1.get('PostTypeId'),
                                                         base_d1.get('AcceptedAnswerId'),
                                                         base_d1.get('CreationDate'),
                                                         base_d1.get('Score'),
                                                         base_d1.get('ViewCount'),
                                                         base_d1.get('Body'),
                                                         base_d1.get('OwnerUserId'),
                                                         base_d1.get('OwnerDisplayName'),
                                                         base_d1.get('LastEditorUserId'),
                                                         base_d1.get('LastEditorDisplayName'),
                                                         base_d1.get('LastEditDate'),
                                                         base_d1.get('LastActivityDate'),
                                                         base_d1.get('Title'),
                                                         base_d1.get('Tags'),
                                                         base_d1.get('AnswerCount'),
                                                         base_d1.get('CommentCount'),
                                                         base_d1.get('FavoriteCount'),
                                                         base_d1.get('ClosedDate'),
                                                         base_d1.get('CommunityOwnedDate'),
                                                            )
                    )
            elif int(dict_of_posting_data['PostTypeId']) == 1 and 'AcceptedAnswerId' not in dict_of_posting_data:
                li2 = ['Id',
                       'PostTypeId',
                       'CreationDate',
                       'Score',
                       'ViewCount',
                       'Body',
                       'OwnerUserId',
                       'OwnerDisplayName',
                       'LastEditorUserId',
                       'LastEditorDisplayName',
                       'LastEditDate',
                       'LastActivityDate',
                       'Title',
                       'Tags',
                       'AnswerCount',
                       'CommentCount',
                       'FavoriteCount',
                       'ClosedDate',
                       'CommunityOwnedDate'
                       ]
                li_non2 = [None] * len(li2)
                base_d2 = dict(zip(li2, li_non2))
                base_d2.update(dict_of_posting_data)

                cur.execute(
                    'INSERT INTO postsWithOutAnswers VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                                                        (base_d2.get('Id'),
                                                         base_d2.get('PostTypeId'),
                                                         base_d2.get('CreationDate'),
                                                         base_d2.get('Score'),
                                                         base_d2.get('ViewCount'),
                                                         base_d2.get('Body'),
                                                         base_d2.get('OwnerUserId'),
                                                         base_d2.get('OwnerDisplayName'),
                                                         base_d2.get('LastEditorUserId'),
                                                         base_d2.get('LastEditorDisplayName'),
                                                         base_d2.get('LastEditDate'),
                                                         base_d2.get('LastActivityDate'),
                                                         base_d2.get('Title'),
                                                         base_d2.get('Tags'),
                                                         base_d2.get('AnswerCount'),
                                                         base_d2.get('CommentCount'),
                                                         base_d2.get('FavoriteCount'),
                                                         base_d2.get('ClosedDate'),
                                                         base_d2.get('CommunityOwnedDate'),
                                                         )
                    )

            else:   # dict will be referring to a response to one of the posts
                li3 = ['Id',
                       'PostTypeId',
                       'ParentId',
                       'CreationDate',
                       'Score',
                       'Body',
                       'OwnerUserId',
                       'OwnerDisplayName',
                       'LastEditorUserId',
                       'LastEditorDisplayName',
                       'LastEditDate',
                       'LastActivityDate',
                       'CommentCount',
                       'CommunityOwnedDate'
                       ]
                li_non3 = [None]*len(li3)
                base_d3 = dict(zip(li3, li_non3))
                base_d3.update(dict_of_posting_data)

                cur.execute(
                    'INSERT INTO responses VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                                                        (base_d3.get('Id'),
                                                         base_d3.get('PostTypeId'),
                                                         base_d3.get('ParentId'),
                                                         base_d3.get('CreationDate'),
                                                         base_d3.get('Score'),
                                                         base_d3.get('Body'),
                                                         base_d3.get('OwnerUserId'),
                                                         base_d3.get('OwnerDisplayName'),
                                                         base_d3.get('LastEditorUserId'),
                                                         base_d3.get('LastEditorDisplayName'),
                                                         base_d3.get('LastEditDate'),
                                                         base_d3.get('LastActivityDate'),
                                                         base_d3.get('CommentCount'),
                                                         base_d3.get('CommunityOwnedDate'),
                                                        )
                    )

            con.commit()
            i+=1
            print(i)




parse_and_move('/Users/BR05URF/Downloads/stackexchange/Posts.xml', 'row/row')

Answer 1

• The very first feeling I had by throwing a glance on your program is that there are code smells.
• This means there are several things you can do to clean this code, both in the general form and when it comes to details but I feel discouraged to mention any as long as you do not consider re-designing twice before coding ... In your case, I would not hesitate take time to re-structure the code in MVC terms (this can be a good start). Otherwise, as it is, your code would be hard to re-use or maintain.

Being that the file is so large, will I run into speed issues once all is migrated to the database?

• A general rule of thumb when dealing with large XML files: using lxml is recommended as it is faster than the library you are using (and yes, it also implements iterparse())
• When you use the iterative parsing approach (iterparse()) and process such a huge file, you can not allow yourself to skip reading this article: High-performance XML parsing in Python with lxml: Stretch the limits of this full-featured XML parsing and serializing suite.