def updateSingleTable(tableName:str, newData:json, condationFeild:str , exceptionColumn = []): # tableName: the target to update table # newData: a jason or dict var that will handle the new data that need to be update with the feild names # condationFeild: This will be use in the WHERE close of the UDPATE SQL statment to limit the update # exceptionColumn: a list of feild names that the funcion should ignore when creating the SQL. Note that, the funcion will # use the newData for feild names and the table's feild that will be extracted form the data base while ignoreing # the ones in exceptionColumn. db = SqlConn.ConnectToDB() # Get employee tabel feilds name EmpTableFeildsName = SqlConn.SendSQL(db, "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE " \ + "`TABLE_SCHEMA`='" + SqlConn.getDatabaseName() + "' AND `TABLE_NAME`='" + tableName + "'") # Craft the UPDATE SQL SqlStatment = "UPDATE `" + tableName + "` SET " firstComa = False # Loop through the feilds for oneFeild in EmpTableFeildsName: # Remove any unwanted data if oneFeild[0] in exceptionColumn or oneFeild[0] == condationFeild: continue # Attach the new field if oneFeild[0] in newData: if str(newData[oneFeild[0]]).upper() == "NONE" or newData[oneFeild[0]] == "": continue # Handle the first adding comma to the sql if firstComa: SqlStatment = SqlStatment + "'," SqlStatment = SqlStatment + "`" + str(oneFeild[0]) + "`='" + str(newData[oneFeild[0]]) firstComa = True SqlStatment = SqlStatment.strip() if SqlStatment[len(SqlStatment)-1] != "'": SqlStatment = SqlStatment + "'" SqlStatment = SqlStatment + " WHERE `" + condationFeild + "`='" + str(newData[condationFeild]) + "'" print(SqlStatment) finalUpdateResult = SqlConn.SendSQL (db, SqlStatment, returnDate=False) db.close() if 'err' in finalUpdateResult: return ["err", "Update failed", finalUpdateResult, SqlStatment] else: if finalUpdateResult[1] == 0: # The Update was ok, but it affact no column. Meaning, there is new data need to be inserted return ["NO Effect", "No Rows were affected"] else: return ["OK", ""]
def insertNewData(tableName:str, newData:json, idFeildName:str, exceptionColumn = []): db = SqlConn.ConnectToDB() # Get tabel feilds name EmpTableFeildsName = SqlConn.SendSQL(db, "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE " \ + "`TABLE_SCHEMA`='" + SqlConn.getDatabaseName() + "' AND `TABLE_NAME`='" + tableName + "'") ColumnName:str = "" ValuesData:str = "" # Loop through the feilds for oneFeild in EmpTableFeildsName: print(oneFeild[0]) # Remove any unwanted data if oneFeild[0] in exceptionColumn or oneFeild[0] in idFeildName: continue # Attach the new field if oneFeild[0] in newData: if str(newData[oneFeild[0]]).lower() == "none": continue ColumnName = ColumnName + "`" + str(oneFeild[0]) + "`," ValuesData = ValuesData + "'" + str(newData[oneFeild[0]]) + "'," # Craft the UPDATE SQL SqlStatment = "INSERT INTO `" + tableName + "` (" + ColumnName[0:len(ColumnName)-1] + ") VALUES (" \ + ValuesData[0:len(ValuesData)-1] + ")" print(SqlStatment) db.close() return SqlConn.InsertSQL(db, SqlStatment) # -------------------------------------------------------------------------------- # print(updateFullInfo(getUserFullData("5973461"))) # print(insertNewData(tableName="departement", newData=getUserFullData("5973461"), idFeildName="departementid"))