def insert(self, dict_values, external_agent=True):
# Connect to db
self.connect_to_db()
self.query_error=''
self.fields[self.name_field_id].required=False
try:
fields, values, update_values=self.check_all_fields(dict_values, external_agent)
except:
self.query_error='Cannot insert the new row'
return False
sql="insert into `"+self.name+"` (`"+"`, `".join(fields)+"`) VALUES ("+", ".join(values)+")"
cursor=SqlClass.query(SqlClass, sql, self.conditions[1], self.connection_id)
if cursor.rowcount>0:
return True
else:
self.query_error='Cannot insert the new row'
return False
def delete(self):
self.connect_to_db()
#Need delete rows from other related tables save in self.related_models_deleted
sql="delete from `"+self.name+"` "+self.conditions[0]
result=SqlClass.query(SqlClass, sql, self.conditions[1], self.connection_id)
if self.yes_reset_conditions:
self.reset_conditions()
return result
def update(self, dict_values, external_agent=True):
# Connect to db
self.fields[self.name_field_id].required=False
if self.name_field_id in dict_values:
del dict_values[self.name_field_id]
self.connect_to_db()
self.query_error=''
#try:
self.updated=True
try:
fields, values, update_values=self.check_all_fields(dict_values, external_agent, True, 'update')
except:
return False
sql="update `"+self.name+"` SET "+", ".join(update_values)+" "+self.conditions[0]
cursor=SqlClass.query(SqlClass, sql, self.conditions[1], self.connection_id)
if self.yes_reset_conditions:
self.reset_conditions()
return True
"""
if cursor.rowcount>0:
if self.yes_reset_conditions:
self.reset_conditions()
return True
else:
self.query_error='Cannot update the row'
return False
"""
"""
def select_count(self, field_to_count='id', raw_query=1):
# Connect to db
self.connect_to_db()
conditions=self.conditions
#First table selecction
tables_to_select=['`'+self.name+'`']
fields=list(self.fields.keys())
#Creating the fields
for field in fields:
#Check if foreignkeyfield
if type(self.fields[field]).__name__=="ForeignKeyField" and raw_query==0:
table_name=self.fields[field].table_name
tables_to_select.append('`'+table_name+'`')
# Add a condition to sql query for join the two tables.
conditions[0]+=" AND `"+table_name+"`.`"+self.fields[field].identifier_field+"`=`"+self.name+"`.`"+field+"`"
sql= "select count(`"+field_to_count+"`) from "+", ".join(tables_to_select)+conditions[0]
cursor=SqlClass.query(SqlClass, sql, conditions[1], self.connection_id)
count=list(cursor.fetchone().values())[0]
if self.yes_reset_conditions:
self.reset_conditions()
return count
def close():
WebModel.make_connection=SqlClass.connect_to_db
SqlClass.close(SqlClass)
def select(self, arr_select=[], raw_query=0):
# Connect to db
self.connect_to_db()
conditions=self.conditions
final_fields=[]
extra_fields=[]
self.query_error=''
#First table selecction
tables_to_select=['`'+self.name+'`']
keys=list(self.fields.keys())
if len(arr_select)==0:
arr_select=keys
# Array intersect for obtain the valid fields
fields = list(set(keys) & set(arr_select))
#Creating the fields
for field in fields:
#Check if foreignkeyfield
if type(self.fields[field]).__name__=="ForeignKeyField" and raw_query==0:
table_name=self.fields[field].table_name
tables_to_select.append('`'+table_name+'`')
# Add field from related table
# as "+table_name+"_"+self.fields[field].named_field
extra_fields.append("`"+table_name+"`.`"+self.fields[field].named_field+"` as "+field)
# Add a condition to sql query for join the two tables.
conditions[0]+=" AND `"+table_name+"`.`"+self.fields[field].identifier_field+"`=`"+self.name+"`.`"+field+"`"
# Add extra fields from related table from select_fields ForeignKeyField class member
for extra_field in self.fields[field].select_fields:
extra_fields.append("`"+table_name+"`.`"+extra_field+"` as `"+table_name+"_"+extra_field+"`")
else:
# Add normal field to sql query
final_fields.append("`"+self.name+"`.`"+field+"`")
extra_sql_field=""
if len(extra_fields)>0:
extra_sql_field=", "+", ".join(extra_fields)
if len(final_fields)==0:
self.query_error="Error: without fields to search"
return False
sql= ("select "+", ".join(final_fields)+extra_sql_field+" from "+", ".join(tables_to_select)+' '+conditions[0]+' '+self.order_by+' '+self.limit).strip()
self.last_query=sql
if self.yes_reset_conditions:
self.reset_conditions()
cursor=SqlClass.query(SqlClass, sql, conditions[1], self.connection_id)
if cursor==False:
self.query_error=SqlClass.error_connection
return False
else:
return cursor
def query(WebModel, str_query, args=[], connection_id='default'):
WebModel.connect_to_db(WebModel)
return SqlClass.query(SqlClass, str_query, args, connection_id)
