hygdata_v3 to sqlite3

hyg2sqlite.py

# Public domain
# Created by Jari Pennanen
# September 2011
# Adapted by sigma001 
# April 2016
#
# For viewing SQLite, try one of these
# - http://sqliteadmin.orbmu2k.de/ (only win32, better than sqlitebrowser)
# - http://sqlitebrowser.sourceforge.net/

import sqlite3
import os
import csv

# Create HYG table if not existing
def create_hyg(conn):
	cur = conn.cursor()
	
	# Create table if not existing
	cur.execute('''
		CREATE TABLE IF NOT EXISTS HYG(
			id INTEGER PRIMARYKEY ASC,
			hip INTEGER,
			hd INTEGER,
			hr INTEGER,
			gl INTEGER,
			bf TEXT,
			ra REAL,
			dec REAL,
			proper TEXT,
			dist REAL,
			pmra REAL,
			pmdec REAL,
			rv REAL,
			mag REAL,
			absmag REAL,
			spect TEXT,
			ci REAL,
			x REAL,
			y REAL,
			z REAL,
			vx REAL,
			vy REAL,
			vz REAL,
			rarad REAL,
			decrad REAL,
			pmrarad REAL,
			prdecrad REAL,
			pmdecrad REAL,
			bayer TEXT,
			flam TEXT,
			con TEXT,
			comp INTEGER,
			comp_primary INTEGER,
			base INTEGER,
			lum REAL,
			var TEXT,
			var_min REAL,
			var_max REAL
		);
	''')
	
	conn.commit()

# Insert single row to table (does not commit!)			
def insert_row(conn, headers, row, dry_run=False):
	cur = conn.cursor()
	
	sql = """INSERT OR IGNORE INTO HYG (%(columns)s) VALUES (%(prep)s)""" % dict(
			columns=",".join(headers), 
			prep=",".join(['?' for t in headers])
		)
	if dry_run:
		print sql
		return
		
	cur.execute(sql, row)

# Import HYG CSV data
def import_hygcsv(conn, csvfile, verbose=False, dry_run=False):
	file = open(csvfile, 'rb')
	rdr = csv.reader(file)
	
	# Get headers row
	headers = rdr.next()
	
	# Debug / preview verbose
	if verbose:
		print "Headers:"
		print headers
	
	for n, row in enumerate(rdr):
		insert_row(conn, headers, row, dry_run=dry_run)
		
		# Commit on each 5000th row
		if n % 5000 == 0:
			print ".",  # Note "," ensures that no newline is printed
			conn.commit()
			
	print "."
	conn.commit()
		
# Main command line program
if __name__ == '__main__':
	conn = sqlite3.connect('hygdata_v3.db');
	create_hyg(conn)
	import_hygcsv(conn, 'hygdata_v3.csv', verbose=False, dry_run=False)