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

Here is a small script that I needed, and feel that I should share the community. I'm sorry if you find something extremely unpythonic or a bad practice, but it's a quick and dirty script and I should have gone to sleep a few hours ago.

It creates a new SQLite db if needed, creates all the tables, and dumps the dump in them.

Without more presentation, my WTF of the date:

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)

Feel free to make any suggestions. I'll try to improve on it, but as it is now, it Works on my machineTM.

I'll go to bed now, with my lovely snake.


Correctly working, never using more than 200 MB for the dump.

timeit pending, but fast enough.

Thanks to Kyle Cronin for making me wonder if I was on the right path. My previous algorithm had important problems.

This script uses lxml, that allows you to have only one node in memory at a time, avoiding the need of using regexes for parsing XMLcommitting a sin.

share|improve this question
    
What sort of performance do you get? How long does it take to run? –  Kyle Cronin Nov 2 '09 at 5:49
    
@Kyle Cronin: it's still running. For the users table, about an hour. It's NOT optimized for speed. As you can see, I loop over with the insert. An insertmany should speed things up a little. Not printing the inserts speeds up a little. I'd say that you'd have to keep it running overnight (what I'll do) in a slow spec machine (my EEE 1000HA netbook). I wanted to keep the least necessary data in memory. Tomorrow will timeit. –  perbert Nov 2 '09 at 5:54
    
@voyager's mask: Yikes! I'm not familiar with the code you're using to insert into the DB, but it doesn't look like you're wrapping your writes into 1 transaction. If you don't, SQLite writes to the file with every since insert. If you stick a "begin" at the beginning" and an "end" at the end, you'll probably discover at least a 10x speedup, possibly more. –  Kyle Cronin Nov 2 '09 at 6:06
    
@Kyle Cronnin: I'm aware of that, but I wanted first to make sure that the script didn't blow up because of some non-foreseen situation with the xml files. Right now, the script is going the lazy, ugly way: commit and write to disk on every insert. –  perbert Nov 2 '09 at 6:11
    
Remember that I'm running this on a netbook, RAM comes at a premium. On the desktop will try dumping all the xml to memory and inserting in bulk. –  perbert Nov 2 '09 at 6:14
    
@voyager: OK, OK, it was just a tip. I wrote a similar script in Python several months ago to produce my SQLite versions of the early data dumps (April and May, I think). IIRC it took about half an hour to load the data from a dump into a SQLite file, and that was with the 1-transaction trick. –  Kyle Cronin Nov 2 '09 at 6:24
    
@Kyle Cronin: thanks for the tip anyway. Do you remember what parser you used then? Also, keep in mind that the dump has doubled in the last months ;) Will change the script now anyway, as I'm seeing that I'm inserting at the wooping speed of 1 Kb per second... –  perbert Nov 2 '09 at 6:36
    
Damn! That was a big change! But I think that I have problems with oone of the files. Could it be that the badges file is malformed at he end? –  perbert Nov 2 '09 at 6:50
    
That's a pretty disturbing photo. I've seen videos of pythons swallowing prey larger than that kid! –  pavium Nov 2 '09 at 11:12
    
@pavium: check the history of the non-answer bellow, or just google image python... –  perbert Nov 2 '09 at 11:23
    
@voyager: Actually, I used regular expressions. I read the file in small chunks (I think 16MB?) and did regular expression matching on that. It wasn't pretty, but it worked well enough. –  Kyle Cronin Nov 2 '09 at 14:12
    
@Kyle Cronin: don't let Welbog hear you! lxml turned out to be much more flexible than I knew. I could take the dump in a few dozen minutes and less than 200 MB. –  perbert Nov 2 '09 at 22:23

You must log in to answer this question.

Browse other questions tagged .