def update(self,xdatapred,sfield):
roar_db=pdb.cls_sql_db(self.dbname,self.dbuser,self.dbhost, \
self.dbpassword)
roar_db.connect()
roar_db.cursor()
## update table
m=len(xdatapred[0])
for i in range(m):
datavalue=float(xdatapred[2][i])
dcondition={}
trow=self.drowinv[xdatapred[0][i]]
tcol=self.dcolinv[xdatapred[1][i]]
iitem=0
for sitem in self.table_row:
dcondition[sitem]=trow[iitem]
iitem+=1
iitem=0
for sitem in self.table_column:
dcondition[sitem]=tcol[iitem]
iitem+=1
print(sfield)
print(datavalue)
print(dcondition)
if datavalue==15.0:
roar_db.update(self.dbtable,sfield,datavalue,dcondition)
## roar_db.commit()
roar_db.close_cursor()
roar_db.close_connect()
def load_table(self):
roar_db=pdb.cls_sql_db(self.dbname,self.dbuser,self.dbhost, \
self.dbpassword)
roar_db.connect()
roar_db.cursor_dict()
## read the dbtable
## sfields='object, object_feature, value'
sfields=''
for sitem in self.table_row:
sfields=sfields+sitem+', '
for sitem in self.table_column:
sfields=sfields+sitem+', '
sfields=sfields[:-2]+' '
self.rows=roar_db.select(self.dbtable,sfields)
## roar_db.commit()
roar_db.close_cursor()
roar_db.close_connect()
## convert db rows into mmmvr list of 1D arrays
xdata_rel=self.rows2dtable()
## covert py dictionary into sparse array of (row,column,value)
## coo_ijv=self.dtable2ijv_coo()
return(xdata_rel)
def db_load(self,stable,suser,shost,spassword,):
## dobject2index={}
## dindex2object={}
## dfeature2index={}
## dindex2feature={}
## load database roar with table roar of fields object,object_feature,value
## roar_db=pdb.cls_sql_db('roar','szs777','localhost','cso7ba8nc')
sdatabase='salad_scenario'
roar_db=pdb.cls_sql_db(sdatabase,suser,shost,spassword)
roar_db.connect()
roar_db.cursor()
## sfields='object, object_feature, value'
sfields='*'
rows=roar_db.select(self.dtable_store[stable].table_name,sfields)
## roar_db.commit()
roar_db.close_cursor()
roar_db.close_connect()
self.dtable_store[stable].rows=rows
return()
def db_read_join(self,table1,field1,table2,field2):
roar_db=pdb.cls_sql_db(self.dbname,self.user,self.host,self.password)
roar_db.connect()
roar_db.cursor()
ssql='SELECT'+' '+'*' \
+'FROM'+table1+' ' \
+'INNER JOIN'+' '+table2+' ' \
+'ON'+' '+table1+'.'+field1+' '+'+'+' '+table2+'.'+field2 \
+';'
rows=roar_db.execute_select(self,ssql)
roar_db.cursor_close()
roar_db.connect_close()
return(rows)
def update_table(self,dbtable,lfields,datavalues):
"""
datavalues = tuple of dictionaries ( { column_name : value, ... } )
"""
roar_db=pdb.cls_sql_db(self.dbname,self.dbuser,self.dbhost, \
self.dbpassword)
roar_db.connect()
roar_db.cursor()
## update(insert) table
roar_db.update_many_2(dbtable,lfields,datavalues)
## roar_db.commit()
roar_db.close_cursor()
roar_db.close_connect()
def update_table(self, dbtable, lfields, datavalues):
"""
datavalues = tuple of dictionaries ( { column_name : value, ... } )
"""
roar_db=pdb.cls_sql_db(self.dbname,self.dbuser,self.dbhost, \
self.dbpassword)
roar_db.connect()
roar_db.cursor()
## update(insert) table
roar_db.update_many_2(dbtable, lfields, datavalues)
## roar_db.commit()
roar_db.close_cursor()
roar_db.close_connect()
def load_table_raw(self,dbtable,lfieldsin):
self.dbtable=dbtable ## name of the table
roar_db=pdb.cls_sql_db(self.dbname,self.dbuser,self.dbhost, \
self.dbpassword)
roar_db.connect()
roar_db.cursor_dict()
lfields=lfieldsin.copy()
self.dbrows=roar_db.select(self.dbtable,lfields)
## roar_db.commit()
roar_db.close_cursor()
roar_db.close_connect()
return
def load_table_raw(self, dbtable, lfieldsin):
self.dbtable = dbtable ## name of the table
roar_db=pdb.cls_sql_db(self.dbname,self.dbuser,self.dbhost, \
self.dbpassword)
roar_db.connect()
roar_db.cursor_dict()
lfields = lfieldsin.copy()
self.dbrows = roar_db.select(self.dbtable, lfields)
## roar_db.commit()
roar_db.close_cursor()
roar_db.close_connect()
return
def create_table(self,dbtable,dfields):
"""
datavalues = tuple of dictionaries ( { column_name : value, ... } )
"""
roar_db=pdb.cls_sql_db(self.dbname,self.dbuser,self.dbhost, \
self.dbpassword)
roar_db.connect()
roar_db.cursor()
## update(insert) table
iret=roar_db.create_table(dbtable,dfields)
## roar_db.commit()
roar_db.close_cursor()
roar_db.close_connect()
return(iret)
def create_table(self, dbtable, dfields):
"""
datavalues = tuple of dictionaries ( { column_name : value, ... } )
"""
roar_db=pdb.cls_sql_db(self.dbname,self.dbuser,self.dbhost, \
self.dbpassword)
roar_db.connect()
roar_db.cursor()
## update(insert) table
iret = roar_db.create_table(dbtable, dfields)
## roar_db.commit()
roar_db.close_cursor()
roar_db.close_connect()
return (iret)
def db_insert(self,stable,suser,shost,spassword,datavalues):
## load database roar with table roar of fields object,object_feature,value
## roar_db=pdb.cls_sql_db('roar','szs777','localhost','cso7ba8nc')
sdatabase='salad_scenario'
roar_db=pdb.cls_sql_db(sdatabase,suser,shost,spassword)
roar_db.connect()
roar_db.cursor()
## sfields='object, object_feature, value'
sfields='*'
rows=roar_db.insert_many(stable,sfields,datavalues)
## args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", \
## x) for x in tup)
## cur.execute("INSERT INTO table VALUES " + args_str)
roar_db.commit()
roar_db.close_cursor()
roar_db.close_connect()
return(rows)
