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()