What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 128 Stack Exchange communities.

Possible Duplicate:
Scripts to convert data-dump to other formats

As suggested in another question, if you have scripts or small programs in any language that convert the monthly data dump into another format, please share them here.

share|improve this question

marked as duplicate by Joel Coehoorn Nov 2 '09 at 22:10

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Damnit, I just posted an mostly identical question, meta.stackexchange.com/questions/28221/… - to prevent us both deleting our questions at the same time, which one shall we keep? You could delete this, or I could move my excessive guidelines here and delete my question.. –  dbr Nov 2 '09 at 20:45

1 Answer 1

Python script to import/create SQLite3 database from SO data dump

Requires:

  • Python 2.5+
  • lxml

import sqlite3
import os
import xml.etree.cElementTree as etree
import logging

ANATHOMY = {
    'badges': {
        'Id':'INTEGER',
        'UserId':'INTEGER',
        'name':'TEXT',
        'date':'DATETIME',
    },
    'comments': {
        'Id':'INTEGER',
        'PostId':'INTEGER',
        'Score':'INTEGER',
        'Text':'TEXT',
        'CreationDate':'DATETIME',
        'UserId':'INTEGER',
    },
    'posts': {
        'Id':'INTEGER', 
        'PostTypeId':'INTEGER', # 1: Question, 2: Answer
        'ParentID':'INTEGER', # (only present if PostTypeId is 2)
        'AcceptedAnswerId':'INTEGER', # (only present if PostTypeId is 1)
        'CreationDate':'DATETIME',
        'Score':'INTEGER',
        'ViewCount':'INTEGER',
        'Body':'TEXT',
        'OwnerUserId':'INTEGER', # (present only if user has not been deleted) 
        'LastEditorUserId':'INTEGER',
        'LastEditorDisplayName':'TEXT', #="Rich B" 
        'LastEditDate':'DATETIME', #="2009-03-05T22:28:34.823" 
        'LastActivityDate':'DATETIME', #="2009-03-11T12:51:01.480" 
        'CommunityOwnedDate':'DATETIME', #(present only if post is community wikied)
        'Title':'TEXT',
        'Tags':'TEXT',
        'AnswerCount':'INTEGER',
        'CommentCount':'INTEGER',
        'FavoriteCount':'INTEGER',
        'ClosedDate':'DATETIME',
    },
    'votes': {
        'Id':'INTEGER',
        'PostId':'INTEGER',
        'UserId':'INTEGER',
        'VoteTypeId':'INTEGER',
           # -   1: AcceptedByOriginator
           # -   2: UpMod
           # -   3: DownMod
           # -   4: Offensive
           # -   5: Favorite
           # -   6: Close
           # -   7: Reopen
           # -   8: BountyStart
           # -   9: BountyClose
           # -  10: Deletion
           # -  11: Undeletion
           # -  12: Spam
           # -  13: InformModerator
        'CreationDate':'DATETIME',
    },
    'users': {
        'Id':'INTEGER',
        'Reputation':'INTEGER',
        'CreationDate':'DATETIME',
        'DisplayName':'DATETIME',
        'LastAccessDate':'DATETIME',
        'WebsiteUrl':'TEXT',
        'Location':'TEXT',
        'Age':'INTEGER',
        'AboutMe':'TEXT',
        'Views':'INTEGER',
        'UpVotes':'INTEGER',
        'DownVotes':'INTEGER',
  },
}

def dump_files(file_names, anathomy, 
                dump_path='c:\\temp\\', 
                dump_database_name = 'so-dump.db',
                create_query='CREATE TABLE IF NOT EXISTS [{table}]({fields})',
                insert_query='INSERT INTO {table} ({columns}) VALUES ({values})',
                log_filename='so-parser.log'):

    logging.basicConfig(filename=os.path.join(dump_path, log_filename),level=logging.INFO)
    db = sqlite3.connect(os.path.join(dump_path, dump_database_name))

    for file in file_names:
        print "Opening {0}.xml".format(file)
        with open(os.path.join(dump_path, file + '.xml')) as xml_file:
            tree = etree.iterparse(xml_file)
            table_name = file

            sql_create = create_query.format(
                                table=table_name, 
                                fields=", ".join(['{0} {1}'.format(name, type) for name, type in anathomy[table_name].items()]))
            print('Creating table {0}'.format(table_name))

            try:
                logging.info(sql_create)
                db.execute(sql_create)
            except Exception, e:
                logging.warning(e)

            for events, row in tree:
                try:
                    logging.debug(row.attrib.keys())

                    db.execute(insert_query.format(
                                table=table_name, 
                                columns=', '.join(row.attrib.keys()), 
                                values=('?, ' * len(row.attrib.keys()))[:-2]),
                                row.attrib.values())
                    print ".",
                except Exception, e:
                    logging.warning(e)
                    print "x",
                finally:
                    row.clear()
            print "\n"
            db.commit()
            del(tree)

if __name__ == '__main__':
    dump_files(ANATHOMY.keys(), ANATHOMY)
share|improve this answer

Not the answer you're looking for? Browse other questions tagged .