CSV dump from local database

gistfile1.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

'''
	This script extracts CSV dumps from my local database
	
	Author: Marian Steinbach, [email protected], http://www.sendung.de/japan-radiation-open-data/
'''

import csv
import os
import MySQLdb

DBHOST = '*******'
DBUSER = 'jpradiation'
DBPASS = '********'
DBNAME = 'jpradiation'

DBTABLE_MEASURES = 'measures'

fields = ['station_id', 'datetime_utc', 'sa', 'ra']

try:
    conn = MySQLdb.connect (host = DBHOST,
                               user = DBUSER,
                               passwd = DBPASS,
                               db = DBNAME)
    cursor = conn.cursor()
except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit (1)



def create_export_file(ftype):
	global cursor
	sql = 'SELECT '+ ', '.join(fields) +' FROM ' + DBTABLE_MEASURES
	if ftype == 'station_data_1h.csv':
		sql = sql + ' WHERE datetime_utc >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 HOUR)'
	sql = sql + ' ORDER BY datetime_utc ASC, station_id ASC'
	
	#print sql
	
	try:
		cursor.execute(sql)
	except MySQLdb.Error, e:
		print "Error %d: %s" % (e.args[0], e.args[1])


	# Extract complete data dump
	#
	csvwriter = csv.writer(open(ftype, 'wb'), delimiter='	', quotechar="|", quoting=csv.QUOTE_MINIMAL)
	csvwriter.writerow(fields)
	
	while (1):
		row = cursor.fetchone()
		if row == None:
			break
		#print row
		csvwriter.writerow(row)
	

	os.system('gzip ' + ftype)
	os.system('cp '+ ftype +'.gz /var/www/s.sendung.de/jpradiation/')
	os.system('rm '+ ftype + '.gz')

create_export_file('station_data_1h.csv')
create_export_file('station_data.csv')


cursor.close()
conn.close()