#!/usr/bin/env python

import csv
from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select
from sqlalchemy.engine import reflection

passwordfn = "/nashome/j/jhugon/mylariatapp/lariatsoft_v06_15_00/localProducts_lariatsoft_v06_15_00_debug_e10/lariatsoft/v06_15_00/pwd/lardata_Utilities/lariat_prd_passwd"
tableName = "lariat_ifbeam_database"
outfileName = "dump.csv"
password = None
with open(passwordfn) as passwordfile:
    password = passwordfile.read()
password = password.strip("\n")
eng = create_engine("postgresql://lariat_prd_user:"+password+"@ifdbprod2.fnal.gov:5443/lariat_prd")

with open(outfileName,'wb') as csvfile:
  csvwriter = csv.writer(csvfile)

  insp = reflection.Inspector.from_engine(eng)
  print("Table Names: ")
  print(insp.get_table_names())
  print("Columns in %s:" % tableName )
  alltitles = []
  for col in insp.get_columns(tableName):
    alltitles.append(col["name"])
    print(col["name"])
  titles = ["runnumber","subrun","time","mid_f_mc7an"," mid_f_mc7anb"," mid_f_mcenrg"," end_f_mc7sc1","end_f_mc6ic"]
  csvwriter.writerow(titles)

  with eng.connect() as connection:
    meta = MetaData(eng)
    meta.reflect()
    table = Table(tableName,meta)
    s = select([table.c.runnumber,table.c.subrun,table.c.time,table.c.mid_f_mc7an, table.c.mid_f_mc7anb, table.c.mid_f_mcenrg, table.c.end_f_mc7sc1,table.c.end_f_mc6ic]).where(table.c.runnumber == 8798)
    result = connection.execute(s)
    for row in result:
      csvwriter.writerow(row)