MySQLdb Example

update_hodo_channels_daq.py

#!/usr/bin/env python

import MySQLdb

f = open('hodo_channels_daq.csv', 'r')
lines = f.readlines()
lines = lines[1:]
records = []
for l in lines:
    rec = l.strip().split(',')
    rec = [int(v) for v in rec]
    records.append(rec)
print("got %d channel records" % len(records))

# mysql -h hpsdb.jlab.org -ujeremym -ppuG2xqed -D hps_conditions
db = MySQLdb.connect(host="hpsdb.jlab.org",    # your host, usually localhost
                     user="jeremym",         # your username
                     passwd="puG2xqed",  # your password
                     db="hps_conditions")        # name of the data base
cur = db.cursor()
for r in records:
    crate = r[0]
    slot = r[1]
    channel = r[2]
    ix = r[3]
    iy = r[4]
    layer = r[5]
    hole = r[6]

    qry = "select id from hodo_channels where x = %d and y = %d and layer = %d and hole = %d and channel = %d" % \
          (ix, iy, layer, hole, channel)

    #print(qry)

    cur.execute(qry)

    res = cur.fetchall()
    if len(res) == 0:
        print("No channel found for ix/iy/layer/hole = %d/%d/%d/%d" % (ix, iy, layer, hole))
        raise Exception("No channel found!")
    else:
        for row in res:
            print("found ID %d for ix/iy/layer/hole = %d/%d/%d/%d" % (row[0], ix, iy, layer, hole))
            id = row[0]
            update = "update hodo_channels set crate = %d, slot = %d where id = %d" % (crate, slot, id)
            print(update)
            cur.execute(update)

db.commit()
db.close()