def run_command(datafile):
t1 = dtm.now()
sql = "truncate table temp_bulkload;"
self.c.execute(sql)
self.conn.commit()
infile = "-i%s/%s" % (self.datadir, datafile)
spr.call([pg_bulkload, ctl_file, db, infile, outtable])
print "Elapsed time: " + str(dtm.now() - t1)
sql = "SELECT min(ints),max(ints) FROM temp_bulkload;"
self.c.execute(sql)
jd1, jd2 = self.c.fetchall()[0]
tablename = self.tablejds(jd1)
runid = self.generate_runid(jd1=jd1,
jd2=jd2,
filename=datafile,
tablename=tablename)
print "Elapsed time: " + str(dtm.now() - t1)
self.db_create_partition(self.tablename, tablename)
sql1 = "INSERT INTO %s (runid,ints,ntrac,x,y,z) " % tablename
sql2 = " SELECT %i as runid,ints,ntrac,x,y,z " % runid
sql3 = " FROM temp_bulkload;"
self.c.execute(sql1 + sql2 + sql3)
self.conn.commit()
print "Elapsed time: " + str(dtm.now() - t1)
batch.purge()
def run_command(datafile):
t1 = dtm.now()
sql = "truncate table temp_bulkload;"
self.c.execute(sql)
self.conn.commit()
infile = "-i%s/%s" % (self.datadir, datafile)
spr.call([pg_bulkload,ctl_file,db,infile,outtable])
print "Elapsed time: " + str(dtm.now()-t1)
sql = "SELECT min(ints),max(ints) FROM temp_bulkload;"
self.c.execute(sql)
jd1,jd2 = self.c.fetchall()[0]
tablename = self.tablejds(jd1)
runid = self.generate_runid(jd1=jd1, jd2=jd2,
filename=datafile,
tablename=tablename)
print "Elapsed time: " + str(dtm.now()-t1)
self.db_create_partition(self.tablename, tablename)
sql1 = "INSERT INTO %s (runid,ints,ntrac,x,y,z) " % tablename
sql2 = " SELECT %i as runid,ints,ntrac,x,y,z " % runid
sql3 = " FROM temp_bulkload;"
self.c.execute(sql1 + sql2 + sql3)
self.conn.commit()
print "Elapsed time: " + str(dtm.now()-t1)
batch.purge()
def db_create_indexes(self):
""" Create all missing indexes """
sql = "SELECT distinct(tablename) FROM runs;"
self.c.execute(sql)
rowlist = self.c.fetchall()
for row in rowlist:
t1 = dtm.now()
print row[0]
db_add_primary_keys(row[0])
print "Time passed: ",dtm.now()-t1
db_add_index(self, row[0], 'ints', 'ints')
print "Time passed: ",dtm.now()-t1
db_add_index(self, row[0], 'runtrac', 'runid,ntrac')
print "Time passed: ",dtm.now()-t1
batch.purge()
def insert_sat_to_db(self,field,jd1,jd2=None):
"""Insert field data into a table. """
self.create_fieldtable(field)
def insertload(jd):
self.select(ints=jd)
if not hasattr(self,'x'): return
self.get_satdata(field,jd=jd)
mask = ~np.isnan(self.__dict__[field])
plist = zip(self.runid[mask], self.ints[mask], self.ntrac[mask],
self.__dict__[field][mask])
tablename = self.tablename + '__' + field
sql = ("INSERT INTO " + tablename + " (runid,ints,ntrac,val) " +
" VALUES (%s,%s,%s,%s)")
self.c.executemany(sql,plist)
self.conn.commit()
if not jd2:
insertload(jd1)
else:
for jd in np.arange(jd1,jd2+1):
print jd2-jd
insertload(jd)
batch.purge()
def insert_sat_to_db(self,field,jd1,jd2=None):
"""Insert field data into a table. """
self.create_fieldtable(field)
def insertload(jd):
self.select(ints=jd)
if not hasattr(self,'x'): return
self.get_satdata(field,jd=jd)
mask = ~np.isnan(self.__dict__[field])
plist = zip(self.runid[mask], self.ints[mask], self.ntrac[mask],
self.__dict__[field][mask])
tablename = self.tablename + '__' + field
sql = ("INSERT INTO " + tablename + " (runid,ints,ntrac,val) " +
" VALUES (%s,%s,%s,%s)")
self.c.executemany(sql,plist)
self.conn.commit()
if not jd2:
insertload(jd1)
else:
for jd in np.arange(jd1,jd2+1):
print jd2-jd
insertload(jd)
batch.purge()
