diff --git a/lsst_queries.py b/lsst_queries.py new file mode 100644 index 0000000000000000000000000000000000000000..bddc42a84b0b855e232a1f5fa1aee5aecaa8d45d --- /dev/null +++ b/lsst_queries.py @@ -0,0 +1,95 @@ +import time, logging +from os.path import basename + +from pymonetdb.exceptions import Error as DBError + +def loadcsv_drop_pkey(conn, csvfile, q): + """ + """ + tabname = basename(csvfile)[:-4].lower() + pkey = tabname + "_id_pkey" + + with open('loadcsv_drop_pkey.sql', 'r') as f: + qcsv = f.read() + params = {'tabname': tabname + ,'pkey': pkey} + query = qcsv % (params) + #print "Query:\n%s" % (query) + + try: + cursor = conn.cursor() + t0 = time.time() + cursor.execute(query) + t = round(1000 * (time.time() - t0), 3) + conn.commit() + print "# AL: (Q%s: %s ms) Altered table %s dropped pkey %s" % (q, t, tabname, pkey) + logging.info("L;I?;Q%s;T%s;N0" % (q, t)) + cursor.close() + except DBError, dbe: + print "Failed for reason %s on query %s" % (dbe, query) + raise + +def alter_tabname_add_pkey(conn, csvfile, q): + """Set a primary key for the table + """ + tabname = basename(csvfile)[:-4].lower() + if tabname.startswith("source_"): + pkey = "id" + elif tabname.startswith("forcedsource_"): + pkey = "deepSourceId,scienceCcdExposureId" + elif tabname.startswith("object_"): + pkey = "deepSourceId" + elif tabname.startswith("objectfulloverlap_"): + return + + with open('sql/alter_tabname_add_pkey.sql', 'r') as f: + qcsv = f.read() + params = {'tabname': tabname + ,'pkey': pkey} + query = qcsv % (params) + #print "query = %s" % (query) + + try: + cursor = conn.cursor() + t0 = time.time() + cursor.execute(query) + t = round(1000 * (time.time() - t0), 3) + conn.commit() + print "# AT: (Q%s: %s ms) Altered table %s added pkey" % (q, t, tabname) + logging.info("L;I0;Q%s;T%s;N0" % (q, t)) + cursor.close() + except DBError, dbe: + print "Failed for reason %s on query %s" % (dbe, query) + raise + +def load_csv_copyinto(conn, csvfile, q): + """Use copy into to load the csv file + """ + tabname = basename(csvfile)[:-4].lower() + with open('sql/loadcsv_copyinto.sql', 'r') as f: + qcsv = f.read() + params = {'tabname': tabname + ,'csvfile': csvfile} + query = qcsv % (params) + #print "Query:\n%s" % (query) + + try: + cursor = conn.cursor() + t0 = time.time() + n = cursor.execute(query) + t = round(1000 * (time.time() - t0), 3) + conn.commit() + print "# CI: (Q%s: %s ms) Copied %s records into table %s" % (q, t, n, tabname) + logging.info("L;I0;Q%s;T%s;N%s" % (q, t, n)) + cursor.close() + except DBError, dbe: + print "Failed for reason %s on query %s" % (dbe, query) + raise + +def load_csv(conn, csvfile, q): + """Load the csvfile, then set the primary key + """ + #loadcsv_drop_pkey(conn, csvfile, q+=?) + load_csv_copyinto(conn, csvfile, q) + alter_tabname_add_pkey(conn, csvfile, q+1) +