Inspired by this question, perhaps we could collect together a bunch of scripts to convert the XML data-dump files into other formats, such as SQLite, MySQL and the likes.

To keep things consistent, some guidelines:

If something changes in the data-dump, and the old script no longer works, make a new post for the updated script.

Don't depend on the machine having lots of memory, reading the entire set of XML files into memory is probably going to cause a lot of machines to run out of memory - some of these questions should help with this..

Mention version numbers of things you are using, well, relevant things, like the programming language and any libraries. Linking to non-standard modules and such would be nice too

Use the following template for all posted scripts:

# {New format name}

{Implementation language}

Tested and working on release(s):

- {List of releases the script is known to work on}



For example:


Python 2.6.4

Tested and working on release(s):

  • Oct 2009
  • Nov 2009

import sqlite3
import magic
share|improve this question
add comment

5 Answers

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


  • Python 2.5+
  • lxml
import sqlite3
import os
import xml.etree.cElementTree as etree
import logging

 'badges': {
 'comments': {
 'posts': {
  'PostTypeId':'INTEGER', # 1: Question, 2: Answer
  'ParentID':'INTEGER', # (only present if PostTypeId is 2)
  'AcceptedAnswerId':'INTEGER', # (only present if PostTypeId is 1)
  'OwnerUserId':'INTEGER', # (present only if user has not been deleted) 
  '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)
 'votes': {
           # -   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
 'users': {

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

 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(
        fields=", ".join(['{0} {1}'.format(name, type) for name, type in anathomy[table_name].items()]))
   print('Creating table {0}'.format(table_name))

   except Exception, e:

   for events, row in tree:

        columns=', '.join(row.attrib.keys()), 
        values=('?, ' * len(row.attrib.keys()))[:-2]),
     print ".",
    except Exception, e:
     print "x",
   print "\n"

if __name__ == '__main__':
 dump_files(ANATHOMY.keys(), ANATHOMY)
share|improve this answer
Could someone with edit permission please update the ANATHOMY declaration in the script above with the following: - Under 'votes', add 'BountyAmount':'INTEGER', - Under 'users', change 'DisplayName':'DATETIME' to 'DisplayName':'TEXT' - Under 'users', add 'EmailHash':'TEXT' - Under 'badges', change 'name' to 'Name' and 'date' to 'Date' Not sure if the badges change affects anything, but the others definitely affect what ends up in the database. –  Bryce Thomas Aug 27 '10 at 12:58
Nearly one year later, I did so. :) On Linux, change dump_path='c:\\temp\\' to something else - I would suggest "." for all systems. Executing it, I get an error about missing 'vote.xml' - guess I have to find it somewhere. :) –  user unknown Jul 29 '11 at 3:33
add comment

C++ (SQLite3, PostgreSQL)

Tested with the 2009-11-01, 2009-12-01, 2010-01-01 dumps of the trilogy.

Columns containing numeric values are converted to numbers, columns containing dates are converted to Unix timestamps and stored as numbers. Optionally indexes are added for all numeric columns. The import takes about 5-10 minutes for the pure data and about 10 minutes more if generating indexes.


To generate a SQLite database, start the sqliteimport program in a directory containing the data dumps XML files and it creates a dump.db in the same directory. If you don't want indexes (creating them takes some time), start the program with sqliteimport -I.

PostgreSQL support is still a little bit experimental. To generate a PostgreSQL database, start the pgcopyimport program in a directory containing the data dumps XML files. Pass a -c flag with the necessary database information, like pgcopyimport -c "dbname=sodump user=so password=abc". The database needs to exist. Warning: This program creates big temporary files in /var/tmp, about as big as the single XML files. This is done to quickly load the data into the database with COPY FROM. The the temporary files are named badges.pgdata,...

If you don't want to create temporary files use the pgimport program instead, but this will be a lot slower. PostgreSQL import also skips the generation of indexes if the -I flag is added.

Other uses

The design should also be fairly flexible if you want to extract special data from the XML files, parse different XML files, or create something else than a SQLite/PostgreSQL database from the input.

share|improve this answer
add comment

I have a short C# program to help import the Posts table into SQL Server. You can see an old version here:

If I remember and have time at home later I'll post updated code. The other tables were imported via Brent Ozar's instructions (with some modifications for schema changes):

share|improve this answer
add comment

So-Slow, is by far the fastest way to get data into SQL Server. It is still able to process current dumps in under 10 minutes, including tag splitting.

share|improve this answer
add comment

MS SQL Server 2000/05/08 - MySQL 5.1 - SQLite3


Tested and working on release(s):

  • March 2010 - all sites, any way you want it. 5 to 17 minutes depending on options

Here is a Windows application that will import to MySQL, MS SQL Server and SQLite:

share|improve this answer
add comment

You must log in to answer this question.

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