def new_raw_entry(file_paths, types):
'''
Takes a file_path list and creates the raw_files table if it does not already exist.
Then, adds the data from the given file to the raw_files table.
Params:
- file_paths: The list of files to be uploaded to the raw_files table
- types: The list of file types generated by gen_types()
'''
print('STARTING UPLOAD TO RAW_FILES')
# Establish database connection
connection = sql.MySQLConnection()
connection.connect(buffered=True, host=host, port=port, user=user, passwd=passwd)
cursor = connection.cursor()
cursor.execute("USE WIRC_POL")
file_paths.sort()
# Create the raw_files table, if it does not exist
create_raw_table(cursor, file_paths[0])
# Upload all of the files in file_paths to the database
add_raw_file(cursor, file_paths, types)
connection.commit()
connection.close()
print('UPLOADED FILES TO RAW_FILES TABLE')
def update_job_status( job_index, job_status = None , index_number =0 ):
job_index = int(job_index)
if True: # link to database
cnx = mc.MySQLConnection( user=user, password=password, host=host, database=database )
cursor = cnx.cursor()
if job_status != None:
query = """UPDATE job_status
SET
status = "%s"
WHERE job_index = %d
"""%(job_status,job_index)
print(query)
cursor = cnx.cursor()
cursor.execute( query,() )
for result in cursor.stored_results():
a = result.fetchall()
if index_number != 0:
query = """UPDATE job_status
SET
index_number = %d
WHERE job_index = %d
"""%(index_number,job_index)
cursor = cnx.cursor()
cursor.execute( query,() )
for result in cursor.stored_results():
a = result.fetchall()
return True
def receiveMsg():
conn = mc.MySQLConnection(**database)
c = conn.cursor()
msgs = bot.get_updates()
for msg in msgs:
chat_text = ""
file_id = ""
file_name = ""
mime_type = ""
channel_post = msg.channel_post
if (channel_post != None):
update_id = msg.update_id
doc = channel_post.document
chat_id = channel_post.chat.id
if (doc == None):
attachment = 'false'
chat_text = channel_post.text
else:
attachment = 'true'
file_id = doc.file_id
file_name = doc.file_name
mime_type = doc.mime_type
if (not isExist(update_id)):
query = '''INSERT INTO `in`(
update_id, chat_id, chat_text,
attachment, file_id, file_name,
mime_type) VALUES (%i,%i,'%s','%s','%s','%s','%s')
''' % (
update_id, chat_id, chat_text,
attachment, file_id, file_name,
mime_type)
c.execute(query)
conn.commit()
def startConnection(self):
self.connection = mysqlconn.MySQLConnection(username=self.username,
password=self.password,
database=self.databaseName,
host=self.databaseAddress,
port=self.databasePort)
self.logger.info('connection established.', topic=topDatabase)
def new_flats(masterFlats, BPMap):
'''
Adds flats to database.
Params:
- masterFlats: The list of master flats files to be uploaded
- BPMap: The associated list of bad piel maps
'''
print('STARTING UPLOAD TO MASTER_FLATS')
# Establish the database connection
connection = sql.MySQLConnection()
connection.connect(buffered=True, host=host, port=port, user=user, passwd=passwd)
cursor = connection.cursor()
cursor.execute('USE WIRC_POL')
# Create the master_flats table
create_flat_table(cursor)
# Upload each of the master flats
for i in range(len(masterFlats)):
hdu = fits.open(masterFlats[i])
header = hdu[0].header
add_flat(cursor, masterFlats[i], header['UTSHUT'], header['EXPTIME'],
header['FORE'], header['AFT'], BPMap[i])
hdu.close()
connection.commit()
connection.close()
print('UPLOADED MASTER FLATS TO DATABASE')
def new_skies(masterSkies):
'''
Adds skies to database.
Params:
- masterSkies: The list of master skies to be uploaded
'''
print('STARTING UPLOAD TO MASTER_SKIES')
# Establish the database connection
connection = sql.MySQLConnection()
connection.connect(buffered=True, host=host, port=port, user=user, passwd=passwd)
cursor = connection.cursor()
cursor.execute('USE WIRC_POL')
# Create the master_skies table
create_sky_table(cursor)
# Upload each of the master skies
for i in range(len(masterSkies)):
hdu = fits.open(masterSkies[i])
header = hdu[0].header
add_sky(cursor, masterSkies[i], header['UTSHUT'], header['EXPTIME'])
hdu.close()
connection.commit()
connection.close()
print('UPLOADED MASTER SKIES TO DATABASE')
def select_from(self, select, table, add=""):
result = []
select_string = ""
for word in select:
if len(select_string) > 0:
select_string += ','
select_string += word
query = "SELECT {0} FROM {1} {2}".format(select_string, table, add)
try:
connect = sql.MySQLConnection(**self.config)
if connect.is_connected():
cursor = connect.cursor()
cursor.execute(query)
row = cursor.fetchone()
while row is not None:
result.append(row)
row = cursor.fetchone()
cursor.close()
connect.close()
else:
print "Connection error"
except sql.Error as error:
print error
return result
def single_product_detail(productid):
product = AdminModel.Product.query.filter_by(productid=productid).first()
if request.method == 'POST':
userid = session['userid']
quantity = request.form['quantity']
try:
conn = connector.MySQLConnection(**db)
cursor = conn.cursor(buffered=True)
sql = 'select userid,productid from cart where userid = %s' % (
userid)
cursor.execute(sql)
exist = cursor.fetchone()
print(exist)
if exist != None:
if exist[1] == productid:
sql = 'Update cart set quantity="' + quantity + '" WHERE userid="' + userid + '" AND productid="' + productid + '"'
cursor.execute(sql)
else:
print('d')
cursor.execute(
"INSERT INTO cart (userid,productid,quantity) VALUES (%s,%s,%s)",
(userid, productid, quantity))
conn.commit()
return redirect(url_for('catalog'))
except connector.Error as errors:
print(errors)
return render_template('single_product_details.html', product=product)
def __init__(self):
self.conn = sql.MySQLConnection(user="******",
password="******",
database="bankdb",
charset='utf8')
print("Connected to Database...")
Tk.__init__(self)
self.geometry("900x600")
self.b1 = Button(self, text="open", command=self.open)
self.b2 = Button(self, text="deposit", command=self.deposit)
self.b3 = Button(self, text="withdraw", command=self.withdraw)
self.b4 = Button(self, text="search", command=self.search)
self.b5 = Button(self, text="gettrans", command=self.gettrans)
self.b6 = Button(self, text="close", command=self.close)
self.b7 = Button(self, text="listacc", command=self.listacc)
self.b8 = Button(self, text="interest", command=self.interest)
self.b9 = Button(self, text="clear", command=self.clear)
self.b1.pack(side=TOP, fill=X)
self.b2.pack(side=TOP, fill=X)
self.b3.pack(side=TOP, fill=X)
self.b4.pack(side=TOP, fill=X)
self.b5.pack(side=TOP, fill=X)
self.b6.pack(side=TOP, fill=X)
self.b7.pack(side=TOP, fill=X)
self.b8.pack(side=TOP, fill=X)
self.b9.pack(side=TOP, fill=X)
self.info = StringVar()
self.lbl = Label(self, bg="yellow", textvariable=self.info)
self.lbl.pack(side=TOP, fill=BOTH, expand=True)
self.lst = Listbox(self, bg="yellow")
self.lst.pack(side=BOTTOM, fill=BOTH, expand=True)
def __init__(self):
self.cnx = connection.MySQLConnection(user='******',
password='******',
host='localhost',
database='Dispenser')
self.cnx.autocommit = True
self.cursor = self.cnx.cursor(dictionary=True, buffered=True)
def account(username, session):
conn = connector.MySQLConnection(**db)
mycursor = conn.cursor()
sql = 'SELECT*FROM users u INNER JOIN acc_vul a ON u.userid=a.uid WHERE username="******"'
mycursor.execute(sql)
acc = mycursor.fetchone()
print(acc[0])
return render_template('account.html', account=acc, r=session)
def get_connection():
cnx = mysql.MySQLConnection(host=os.getenv('DB_HOST'),
port=os.getenv('DB_PORT'),
user=os.getenv('DB_USER'),
password=os.getenv('DB_PASSWORD'),
database=os.getenv('DB_NAME'),
auth_plugin='mysql_native_password')
return cnx
def __init__(self, top=None):
try:
self.conn = sql.MySQLConnection(user="******",
password="******",
database="bankdb",
charset='utf8')
print("Connected to Database...")
except Exception as e:
print("error::", e)
def mysql():
return closing(mysql_connector.MySQLConnection(**config.MYSQL_DSN))
global MYSQL_POOL
if MYSQL_POOL is None:
MYSQL_POOL = mysql_connector.pooling.MySQLConnectionPool(
pool_name='mysql_pool', pool_size=10, **config.MYSQL_DSN)
return closing(MYSQL_POOL.get_connection())
def profile(username, session):
conn = connector.MySQLConnection(**db)
mycursor = conn.cursor()
sql = 'SELECT*FROM users WHERE username="******"'
mycursor.execute(sql)
acc = mycursor.fetchone()
print(acc[0])
conn.close()
mycursor.close()
return render_template('profile.html', account=acc, r=session)
def __init__(self):
self.config = {
"host": "localhost",
"user": "******",
"passwd": "password123",
"database": "loan_calc_db"
}
# We'll use the MySQLConnection object, so that the connection itself can be passed
self.connection = mySQL.MySQLConnection(**self.config)
def conexion():
cnx = mysql.MySQLConnection(
host="10.1.112.11",
port=3306,
user="******",
password="******",
database="evergreen_con",
auth_plugin="mysql_native_password"
)
return cnx
def __init__(self, **kwargs):
if kwargs:
self.set_config(kwargs)
else:
config_path = os.path.abspath(
os.path.join(THIS_DIR, '../../config/config.json'))
with open(config_path, 'r', encoding='utf-8') as f:
config_ = json.load(f)
self.set_config(config_['db'])
self.db = connector.MySQLConnection()
def replyMsg():
conn = mc.MySQLConnection(**database)
c = conn.cursor()
query = '''SELECT * FROM `out` WHERE sttus ='ready' '''
c.execute(query)
for row in c.fetchall():
sendMsg(row[1], row[2])
query = '''UPDATE `out` SET sttus='sent' WHERE id = %i''' % row[0]
c.execute(query)
conn.commit()
def deleteCart(productid):
if request.method == 'POST':
userid = session['userid']
conn = connector.MySQLConnection(**db)
sql = 'DELETE FROM cart WHERE userid=%s AND productid=%s' % (
userid, productid)
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
return redirect(url_for('cart'))
def isExist(update_id):
conn = mc.MySQLConnection(**database)
c = conn.cursor()
query = '''SELECT * FROM `in` WHERE `update_id`='%s' ''' % update_id
c.execute(query)
count = 0
for row in c.fetchall(): count += 1
if (count > 0):
return True
else:
return False
def get_db():
'''
Create the user database connection and cursor for it.
'''
if 'db' not in g:
g.db = sql.MySQLConnection()
g.db.connect(host='localhost',
user=''' Removed for security reasons ''',
passwd='''# Removed for security reasons #''',
database='wirc_pol_flask')
return g.db.cursor()
def updateCart(productid):
if request.method == 'POST':
q = request.form['quantity']
userid = session['userid']
conn = connector.MySQLConnection(**db)
sql = 'Update cart set quantity=%s WHERE userid=%s AND productid=%s' % (
q, userid, productid)
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
return redirect(url_for('cart'))
def fetch_query(pdbid,chain,ligname):
if True: # link to database
cnx = mc.MySQLConnection( user=user, password=password, host=host, database=database )
cursor = cnx.cursor()
if True: # fetch from system_information
update = True
query = """SELECT index_number, pdb_id,chain,binding_site_definition, resolution, number_of_hydration_sites, register_date
FROM system_information
WHERE pdb_id = '%s' AND chain = '%s' AND binding_site_definition = '%s'
"""
query = query%(pdb_id,chain,ligname)
cursor.execute( query,() )
answer = cursor.fetchall()
N_result = len(answer)
if N_result <= 0 :
sys.stderr.write( "##### ERROR, no entry %s %s %s \n"%(pdb_id,chain,ligname))
sys.exit()
else:
a = answer[-1]
index = a[0]
water_number = a[5]
if True: # get water information
query = ('''
SELECT index_number, water_index, occ, vdw_sol, vdw_rec, ele_sol, ele_rec, t_s, o_s, spa_g
FROM water_cluster
WHERE index_number = %d
'''
)
query = query%( index, )
cursor.execute( query,() )
answer = cursor.fetchall()
if len(answer) <= 0 :
sys.stderr.write( "##### ERROR, no entry %s %s %s \n"%(pdb_id,chain,ligname))
sys.exit()
waters = dict()
keys = list()
for a in answer :
key = a[1]
waters[key] = a
if key not in keys:
keys.append(key)
for key in keys:
print(key,waters[key])
if True: # close database
cursor.close()
cnx.close()
def reset_password():
if request.method == 'POST':
username = session['username']
p = request.form['password']
conn = connector.MySQLConnection(**db)
mycursor = conn.cursor()
sql = 'UPDATE users SET password_hash="' + p + '" where username ="******"'
mycursor.execute(sql)
conn.commit()
return redirect(url_for('login'))
return render_template('reset_password.html')
def reset():
if request.method == 'POST':
username = request.form['username']
conn = connector.MySQLConnection(**db)
mycursor = conn.cursor()
sql = 'SELECT username FROM users where username ="******"'
mycursor.execute(sql)
username = mycursor.fetchone()
conn.commit()
if username:
session['username'] = username
return redirect(url_for('reset_email'))
return render_template('reset.html')
def get_from_darkdb(raw_file):
'''
Retrieve a master dark and hpmap from the database. Uses the magnitude of distance
in time and exposure time to find the most suitable dark.
Params:
- raw_file: The raw file to be calibrated with the master dark.
Return:
- masterDark: The closest master dark
- hp_map: The corresponding hot pixel map
'''
# Establish database connection
connection = sql.MySQLConnection()
connection.connect(buffered=True, host=host, port=port, user=user, passwd=passwd)
cursor = connection.cursor()
cursor.execute('USE WIRC_POL')
masterDark = ''
hp_map = ''
cursor.execute('SELECT COUNT(*) FROM information_schema.tables WHERE table_name = \"master_darks\"')
if cursor.fetchone()[0] > 0:
cursor.execute('SELECT File_Path, HP_MAP, UTSHUT, EXPTIME FROM master_darks')
files = cursor.fetchall()
if len(files) > 0:
# Retreive the shutter times in modified julian form, for comparison
# Also retrieve the exposure times
time_0 = Time([fits.getheader(raw_file)['UTSHUT']])
time_0 = time_0.mjd[0]
exp_0 = fits.getheader(raw_file)['EXPTIME']
times = [x[2] for x in files]
times = Time(times)
times = times.mjd
exps = [float(x[3]) for x in files]
# Create the magnitude metric to be used in finding the best master dark
diff = []
for i in range(len(times)):
diff.append((times[i] - time_0)**2 + (exps[i] - exp_0)**2)
diff = np.array(diff)
ind = np.where(diff == min(diff))[0][0]
connection.commit()
connection.close()
# Gather the filenpaths
masterDark = files[ind][0]
hp_map = files[ind][1]
return masterDark, hp_map
def register_job(dir_name,name):
if True: # link to database
cnx = mc.MySQLConnection( user=user, password=password, host=host, database=database )
cursor = cnx.cursor()
index = int(dir_name)
query = """SELECT job_index
FROM job_status
WHERE job_index = %d
"""%index
cursor.execute( query,() )
answer = cursor.fetchall()
N_result = len(answer)
for a in answer:
pass
if N_result >= 1:
cursor.close()
cnx.commit()
cnx.close()
return False
else:
cnx = mc.MySQLConnection( user=user, password=password, host=host, database=database )
query = ('''
INSERT INTO job_status
(job_index, name )
VALUES
(%d, "%s")
'''%(index,name) )
cursor = cnx.cursor()
cursor.execute( query,() )
for result in cursor.stored_results():
a = result.fetchall()
cursor.close()
cursor.commit()
cnx.close()
return True
def reset_email():
if session.get('username'):
username = session['username']
if request.method == 'POST':
conn = connector.MySQLConnection(**db)
mycursor = conn.cursor()
sql = 'SELECT security_qns FROM users where username ="******"'
mycursor.execute(sql)
sec = mycursor.fetchone()
conn.commit()
a = request.form['answer']
if a == sec[0]:
return redirect(url_for('reset_password'))
return render_template('reset_email.html')
def downloadFile():
conn = mc.MySQLConnection(**database)
c = conn.cursor()
query = "SELECT * FROM `in` WHERE `attachment`='true' AND `downloaded`='false'"
c.execute(query)
for row in c.fetchall():
file_info = bot.get_file(row[5])
r = requests.get('https://api.telegram.org/file/bot{0}/{1}'.format(TOKEN, file_info.file_path))
with open('download/' + row[6], 'wb') as f:
for chunk in r.iter_content(chunk_size=1024):
if chunk:
f.write(chunk)
query = '''UPDATE `in` SET `downloaded`='true' WHERE id = %i''' % row[0]
c.execute(query)
conn.commit()
