for field in columnFields: v = sqlexec(conn,'select distinct %s from %s' % (field,tablename)); cfvalues[field] = v sql = "create or replace view %s as" %(viewname) sql +=""" select """ + ','.join(rowFields) sql += maxValue(columnFields,valueFields,cfvalues) sql += """ from (select """ + ','.join(rowFields) sql += casewhen(columnFields,valueFields,cfvalues) sql += """ from %s) group by %s""" % (tablename,','.join(rowFields)) return sql if __name__ == '__main__': f = codecs.open(sys.argv[1],'r','utf-8') dbdesc = ujson.load(f) f.close() conn = opendb(dbdesc) tablename = 'RPT_Tran_History' viewname = 'view_tran_history1' rowFields = ['PortfolioID','SECURITYID'] valueFields = ['TradeAmount','TradePrice'] columnFields = ['TradeBusinessType','TradeType'] sql = createPivotView(conn,viewname,tablename,rowFields,columnFields,valueFields) print( sql) conn.close()
def getTablename(fn):
return fn[:-len('_data.csv')]
if __name__ == '__main__':
if len(sys.argv) < 3:
print("Usage:\n%s dbdef csvfile" % sys.argv[0])
sys.exit(1)
f = codecs.open(sys.argv[1], 'r', 'utf8')
dbdef = json.load(f)
f.close()
tabname = getTablename(os.path.basename(sys.argv[2]))
conn = opendb(dbdef)
sqlor = sqlorFactory(dbdef['driver'], conn)
sqlor.setConvertFunction('date', str2Date)
fields = sqlor.fields(tabname)
if len(fields) == 0:
print("table not existed", tabname)
sys.exit(1)
# print( fields)
nf = {}
[nf.update({f['name']: f}) for f in fields]
csv = CSVData(sys.argv[2])
writer = DBInserter(sqlor=sqlor, table=tabname)
first = 1
for rec in csv: