Collection of scripts and small programs used by the EVN Support Scientists at JIVE during the regular data processing of EVN observations.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 

57 lines
2.0 KiB

  1. #!/usr/bin/python2
  2. #### ampcal-db.py
  3. #### Jay Blanchard 2017 - python script to take an ampcal.dat file and add each line to the database.
  4. import MySQLdb
  5. import time
  6. from datetime import datetime,timedelta
  7. from warnings import filterwarnings
  8. def insertDB(exp, date, source, telescope, freq, cor):
  9. #basic SQL insert. If the below starts to fail this can be used (HOWEVER DUPLICATE CHECKING WILL HAVE TO BE DONE!)
  10. #sql = """INSERT INTO ampcal(exp, obs_date, source, telescope, frequency, correction) VALUES ('%s', '%s', '%s', '%s', '%s', '%s');""" %(exp, date, source, telescope, freq, cor)
  11. #somewhat complicated SQL here to try to avoid duplicates.
  12. sql = """INSERT INTO ampcal(exp, obs_date, source, telescope, frequency, correction) SELECT * FROM(SELECT '%s', '%s', '%s', '%s', '%s', '%s') AS tmp
  13. WHERE NOT EXISTS (SELECT * FROM ampcal WHERE exp='%s' AND source='%s' AND telescope='%s')
  14. LIMIT 1;""" % (exp, date, source, telescope, freq, cor, exp, source, telescope)
  15. # print sql
  16. try:
  17. cursor.execute(sql)
  18. db.commit()
  19. except (MySQLdb.Error, MySQLdb.Warning) as e:
  20. print(e)
  21. db.rollback()
  22. #MAIN STARTS HERE
  23. #open db con
  24. db = MySQLdb.connect("db0.jive.eu","ampcal-w","klsge[09w4hj","ampcal")
  25. cursor = db.cursor()
  26. #ignore warnings about SQL above...
  27. filterwarnings('ignore', category = MySQLdb.Warning)
  28. print("Inserting ampcal.dat into database")
  29. fin = open('ampcal.dat','rt')
  30. for line in fin:
  31. col = line.split()
  32. year = int(float(col[1]))
  33. rem = float(col[1]) - year
  34. base = datetime(year, 1, 1)
  35. date = base + timedelta(seconds=(base.replace(year=base.year + 1) - base).total_seconds() * rem)
  36. unix_date = int((time.mktime(date.timetuple())+date.microsecond/1000000.0))
  37. try: # neeed to do this in a try for bad rows (easy way to avoid unreadable rows
  38. if (float(col[5]) < 1.0): #we ignore any corrections > 1 as they are likely a bad source
  39. insertDB(col[0],date, col[2], col[3], col[4], col[5])
  40. except:
  41. pass;
  42. fin.close()
  43. #end - close db and file
  44. db.close()