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)