def insertDataKmeanInBD(nameTable,data,time):
numTaxis = 0
query = 'INSERT INTO `{0}`' \
'(dtime, areanum, statusf, aggregate, queuelength, minwait, meanwait, ' \
'maxwait, meanrun, maxrun, meansearch, maxsearch) VALUES '.format(nameTable)
for s in data:
if int(s['areanumPick'])==9999:
continue
# convierte el timestamp en un datetime para insertar en formato estring en la tabla
# time = datetime.utcfromtimestamp(float(s['dtime']))
sTime = datetime.utcfromtimestamp(float(time))
query += '("{0:%Y-%m-%d %H:%M:%S}",{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11}), '.format(
sTime,
# s['dtime'],
s['areanumPick'],
s['statusf'],
s['aggregate'],
s['queuelength'],
s['minwait'],
s['meanwait'],
s['maxwait'],
s['meanrun'],
s['maxrun'],
s['meansearch'],
s['maxsearch']
)
numTaxis+=1
if numTaxis > 0:
query = query.rstrip(' ,')
mysql.execute(query)
print "Se han insertado "+str(numTaxis)+" filas en la tabla "+nameTable
def insertDataMoneyInBD(nameTable,data,time):
numTaxis = 0
query = 'INSERT INTO `{0}`' \
'(dtime, areanum, statusf, aggregate, meanRunDistance) VALUES '.format(nameTable)
for s in data:
if int(s['areanumPick'])==9999:
continue
# convierte el timestamp en un datetime para insertar en formato estring en la tabla
# sTime = datetime.utcfromtimestamp(float(s['dtime']))
sTime = datetime.utcfromtimestamp(float(time))
query += '("{0:%Y-%m-%d %H:%M:%S}",{1},{2},{3},{4}), '.format(
sTime,
# s['dtime'],
s['areanumPick'],
s['statusf'],
s['aggregate'],
s['meanRunDistance']
)
numTaxis+=1
if numTaxis > 0:
query = query.rstrip(' ,')
mysql.execute(query)
print "Se han insertado "+str(numTaxis)+" filas en la tabla "+nameTable
def post(self):
old = self.get_argument('old', None)
password = self.get_argument('password', None)
if old is None or password is None:
response = {'status': 'error'}
self.write(json_encode(response))
return
role = self.get_cookie('role')
fid = self.get_cookie('fid')
old = json_decode(old)
password = json_decode(password)
faculty = mysql.get('faculty', {
'fid': fid,
'password': old,
'role': role
})
if faculty is None or len(faculty) != 1:
response = {'status': 'error'}
self.write(json_encode(response))
return
sql = 'update faculty set password = "******" where fid = "%s"' % (
password, fid)
result = mysql.execute(sql)
if result:
response = {'status': 'ok'}
else:
response = {'status': 'error'}
self.write(json_encode(response))
def step_impl(context, dup_id1, dup_id2):
"""
:type context behave.runner.Context
"""
del_query = "delete from xmart.load_exception where sourcelistingid in (%s, %s);" % (dup_id1, dup_id2)
del_exec = mysql.execute(context.dev_xmart, del_query)
mysql.close_connection(context.dev_xmart)
def insertDataInBDExtends(nameTable,data,time):
numTaxis = 0
query = 'INSERT INTO `{0}`' \
'(dtime, areanum, statusf, aggregate, queuelength, minwait, meanwait, ' \
'maxwait, meanrun, maxrun, meansearch, maxsearch, minareasearchingtime,meanareasearchingtime,maxareasearchingtime, ' \
'minareatime,meanareatime,maxareatime, minareainioccupiedtime, meanareainioccupiedtime,maxareainioccupiedtime, ' \
'minareapercentage,meanareapercentage,maxareapercentage) VALUES '.format(nameTable)
for s in data:
if int(s['areanum'])==9999:
continue
# time = datetime.utcfromtimestamp(float(s['dtime']))
sTime = datetime.utcfromtimestamp(float(time))
query += '("{0:%Y-%m-%d %H:%M:%S}",{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23}), '.format(
sTime,
# s['dtime'],
s['areanum'],
s['statusf'],
s['aggregate'],
s['queuelength'],
s['minwait'],
s['meanwait'],
s['maxwait'],
s['meanrun'],
s['maxrun'],
s['meansearch'],
s['maxsearch'],
s['minareaSearchingTime'],
s['meanareaSearchingTime'],
s['maxareaSearchingTime'],
s['minareaTime'],
s['meanareaTime'],
s['maxareaTime'],
s['minareaIniOccupiedTime'],
s['meanareaIniOccupiedTime'],
s['maxareaIniOccupiedTime'],
s['minareaPercentage'],
s['meanareaPercentage'],
s['maxareaPercentage'],
)
numTaxis+=1
if numTaxis > 0:
query = query.rstrip(' ,')
mysql.execute(query)
print "Se han insertado "+str(numTaxis)+" filas en la tabla "+nameTable
def step_impl(context, agent_test1, agent_test2, office_test1, office_test2):
"""
:type context behave.runner.Context
"""
print("\n **** Scenario 3 **** \n\n")
context.agent_test1 = agent_test1
context.agent_test2 = agent_test2
context.office_test1 = office_test1
context.office_test2 = office_test2
del_agent = "delete from xmart.agent where sourceagentid in ('%s', '%s');" % (agent_test1, agent_test2)
da = mysql.execute(mysql.dev_mart, del_agent)
del_office = "delete from xmart.office where sourceofficeid in ('%s', '%s');" % (office_test1, office_test2)
do = mysql.execute(mysql.dev_mart, del_office)
mysql.close_connection(mysql.dev_mart)
def get_app_name(appid):
for i in range(2):
try:
sql = "select name from app where id=%s"
cursor = mysql.execute(sql, appid)
obj = cursor.fetchone()
return obj["name"]
except Exception, e:
logging.info("exception:%s", str(e))
continue
def get_certificate(appid):
for i in range(2):
try:
sql = '''select cc.cer as cer, cc.pkey as pkey
from app, client as c, client_certificate as cc where c.app_id=%s and c.id=cc.client_id'''
cursor = mysql.execute(sql, appid)
obj = cursor.fetchone()
cer = obj["cer"]
key = obj["pkey"]
return cer, key
except Exception, e:
logging.info("exception:%s", str(e))
continue
def post(self):
fid = self.get_cookie('fid')
passwd1 = self.get_argument('passwd1')
passwd2 = self.get_argument('passwd2')
result = mysql.get('password', {'fid': fid})
#print result
response = {'status': 'failure'}
if result and result[0] and result[0]['passwd'] == passwd1:
sql = 'update password set passwd = "%s" where fid = "%s"' % (passwd2, fid)
#print sql
r = mysql.execute(sql)
#print r
if r:
response = {'status': 'success'}
self.write(json_encode(response))
def step_impl(context, stg_attr_del, stg_list_del, perm_attr_del, perm_list_del):
"""
:type context behave.runner.Context
"""
cur_count = str(mysql.select(context.conn, "Select count(*) from xmart.listing;"))
cur_count = cur_count[2]
stg_attr_del = "delete from xmart.stg_listing_attribute_value;"
stg_list_del = "delete from xmart.stg_listing;"
perm_attr_del = "delete from xmart.listing_attribute_value;"
perm_list_del = "delete from xmart.listing;"
if int(cur_count) >= 1:
mysql.execute(con=context.conn, statement= stg_attr_del)
mysql.execute(con=context.conn, statement= stg_list_del)
mysql.execute(con=context.conn, statement= perm_attr_del)
mysql.execute(con=context.conn, statement= perm_list_del)
mysql.close_connection(context.conn)
else:
mysql.close_connection(context.conn)
pass
def execute(dbopt, query, data):
if dbopt.engine == "postgres":
pgsql.execute(dbopt, query, data)
elif dbopt.engine == "mysql":
mysql.execute(dbopt, query, data)
def add_pid():
global global_pid
global_pid += 1
sql = 'update id set num = %s where name = "pid"' % global_pid
mysql.execute(sql)
# Image is of size nx x ny: nx = 400 ny = 400 # Query database for painting image strings: host = 'localhost' user = '' password = '' conn = MySQLdb.Connection(db=database, host=host, user=user, passwd=password) mysql = conn.cursor() sql = r"select * from annotations" mysql.execute(sql) fields = mysql.fetchall() mysql.close() conn.close() print fields # -------------------------------------------------------------------- # Reformat array of strings into portfolio of paintings: # -------------------------------------------------------------------- # Make mean and weight images:
# Query database for painting image strings:
host = 'localhost'
user = ''
password = ''
conn = MySQLdb.Connection(db=database,
host=host,
user=user,
passwd=password)
mysql = conn.cursor()
sql = r"select * from annotations"
mysql.execute(sql)
fields = mysql.fetchall()
mysql.close()
conn.close()
print fields
# --------------------------------------------------------------------
# Reformat array of strings into portfolio of paintings:
# --------------------------------------------------------------------
# Make mean and weight images: