def checkInsert(dbName, tableName, columns, values): lstErr.clear() table = S.extractTable(dbName, tableName) if table == 0: syntaxPostgreErrors.append("Error: 42000: La base de datos " + str(dbName) + " no existe") return ["Error: No existe la base de datos"] elif table == 1: syntaxPostgreErrors.append("Error: 42P01: La tabla " + str(tableName) + " no existe") return ["Error: No existe la tabla"] if columns != None: if len(columns) != len(values): syntaxPostgreErrors.append( "Error: 42611: definicion en numero de columnas invalida ") return ["Columnas fuera de los limites"] else: if len(values) != len(table["columns"]): syntaxPostgreErrors.append( "Error: 42611: definicion en numero de columnas invalida ") return ["Columnas fuera de los limites"] values = S.getValues(table, columns, values) if not values: syntaxPostgreErrors.append("Error: 42P10: Columnas no identificadas ") return ["Error: Columnas no identificadas"] pks = [] indexCol = 0 for col in table["columns"]: x = Type.get(col["type"]) value = values[indexCol] if not isinstance(value, Primitive): value = Primitive(x, value, 0, 0, 0) values[indexCol] = value if col["PK"]: pks.append(indexCol) indexCol += 1 # Validar la llave primaria if pks: validatePrimary(dbName, tableName, values, pks) indexCol = 0 for value in values: column = table["columns"][indexCol] if value.value != None and value.type != TYPE.NULL: value.value = convertDateTime(value.value, column["type"]) if column["Unique"]: validateUnique(dbName, tableName, value.value, indexCol) if column["FK"] != None: validateForeign(dbName, column["FK"], value.value) if column["Constraint"] != None: validateConstraint(column["Constraint"], values, dbName, tableName, column["type"]) select(column, value) else: value.value = None validateNotNull(column["NN"], column["name"]) indexCol += 1 return [listError(), values]
def execute(self, environment): col = self.colData.execute(environment) df = self.subquery.execute(environment)[0] # TODO: Falta agregar la verificacion de types if len(list(df.columns)) != 1: expression.list_errors.append( "Error: XX000: Error interno (Exist Relational Operation)" + "\n En la linea: " + str(self.row) ) value = col.value.isin(df.iloc[:, 0]) if self.optNot == "NOT": value = ~value return Primitive(TYPE.BOOLEAN, value, self.temp, self.row, self.column)
def checkValue(dbName, tableName): lstErr.clear() table = S.extractTable(dbName, tableName) if table == 0 and table == 1: return for col in table["columns"]: if col["Default"] != None: if col["Default"][1] != 9: value = Primitive(TypeNumber.get(col["Default"][1]), col["Default"][0], 0, 0, 0) select(col, value) if len(lstErr) != 0: col["Default"] = None else: col["Default"] = None return listError()
def execute(self, environment): try: df1 = environment.dataFrame.copy() names = {} for n in list(df1.columns): names[n] = n.split(".")[1] df1.rename(columns=names, inplace=True) df2 = self.subquery.execute(environment)[0] y = df1.columns.intersection(df2.columns) lst = list(y) if len(lst) < 1: expression.list_errors.append( "Error: 42P10: Referencia de columnas invalidas EXIST" + "\n En la linea: " + str(self.row)) value = df1[lst].apply(tuple, 1).isin(df2[lst].apply(tuple, 1)) return Primitive(TYPE.BOOLEAN, value, self.temp, self.row, self.column) except: expression.list_errors.append( "Error: XX000: Error interno (Exist Relational Operation)" + "\n En la linea: " + str(self.row))
def execute(self, environment): try: if self.val == "CURRENT_DATE": value = datetime.now().strftime("%Y/%m/%d") elif self.val == "CURRENT_TIME": value = datetime.now().strftime("%H:%M:%S") elif self.val == "TIMESTAMP": if self.optStr == "now": value = datetime.now().strftime("%Y/%m/%d %H:%M:%S") else: value = self.optStr else: # ERROR expression.list_errors.append( "Error: 22007: Formato de fecha invalido " + str(self.str)) value = self.val return Primitive(TYPE.STRING, value, self.temp, self.row, self.column) except: expression.list_errors.append( "Error: P0001: Error en expresiones de fechas") pass
def invokeFunction(id, *params): temp = None list_ = params params = [] for p in list_: if isinstance(p, str): p = p.strip('"') p = p.strip("'") params.append(p) if id == "extract": temp = ExtractDate(params[0], params[1], params[2], 0, 0) temp = temp.execute(None) elif id == "date_part": temp = DatePart(params[0], params[1], params[2], 0, 0) temp = temp.execute(None) else: parameters = [] for p in params: parameters.append(Primitive(TYPE.NULL, p, p, 0, 0)) temp = FunctionCall(id, parameters, 0, 0) temp = temp.execute(None) if temp: return temp.value return temp
def execute(self, environment): type_ = TYPE.NUMBER try: valores = [] types = [] for p in self.params: obj = p.execute(environment) val = obj.value t = obj.type if isinstance(val, pd.core.series.Series): val = val.tolist() valores.append(val) types.append(t) # Se toma en cuenta que las funcines matematicas # y trigonometricas producen un tipo NUMBER type_ = TYPE.NUMBER if self.function == "abs": value = mf.absolute(*valores) elif self.function == "cbrt": value = mf.cbrt(*valores) elif self.function == "ceil": value = mf.ceil(*valores) elif self.function == "ceiling": value = mf.ceiling(*valores) elif self.function == "degrees": value = mf.degrees(*valores) elif self.function == "div": value = mf.div(*valores) elif self.function == "exp": value = mf.exp(*valores) elif self.function == "factorial": value = mf.factorial(*valores) elif self.function == "floor": value = mf.floor(*valores) elif self.function == "gcd": value = mf.gcd(*valores) elif self.function == "lcm": value = mf.lcm(*valores) elif self.function == "ln": value = mf.ln(*valores) elif self.function == "log": value = mf.log(*valores) elif self.function == "log10": value = mf.log10(*valores) elif self.function == "mod": value = mf.mod(*valores) elif self.function == "pi": value = mf.pi() elif self.function == "power": value = mf.pow(*valores) elif self.function == "radians": value = mf.radians(*valores) elif self.function == "round": value = mf.round_(*valores) elif self.function == "sign": value = mf.sign(*valores) elif self.function == "sqrt": value = mf.sqrt(*valores) elif self.function == "trunc": value = mf.truncate_col(*valores) elif self.function == "width_bucket": value = mf.with_bucket(*valores) elif self.function == "random": value = mf.random_() elif self.function == "acos": value = trf.acos(*valores) elif self.function == "acosd": value = trf.acosd(*valores) elif self.function == "asin": value = trf.asin(*valores) elif self.function == "asind": value = trf.asind(*valores) elif self.function == "atan": value = trf.atan(*valores) elif self.function == "atand": value = trf.atand(*valores) elif self.function == "atan2": value = trf.atan2(*valores) elif self.function == "atan2d": value = trf.atan2d(*valores) elif self.function == "cos": value = trf.cos(*valores) elif self.function == "cosd": value = trf.cosd(*valores) elif self.function == "cot": value = trf.cot(*valores) elif self.function == "cotd": value = trf.cotd(*valores) elif self.function == "sin": value = trf.sin(*valores) elif self.function == "sind": value = trf.sind(*valores) elif self.function == "tan": value = trf.tan(*valores) elif self.function == "tand": value = trf.tand(*valores) elif self.function == "sinh": value = trf.sinh(*valores) elif self.function == "cosh": value = trf.cosh(*valores) elif self.function == "tanh": value = trf.tanh(*valores) elif self.function == "asinh": value = trf.asinh(*valores) elif self.function == "acosh": value = trf.acosh(*valores) elif self.function == "atanh": value = trf.atanh(*valores) elif self.function == "length": value = strf.lenght(*valores) elif self.function == "substring": type_ = TYPE.STRING value = strf.substring(*valores) elif self.function == "trim": type_ = TYPE.STRING value = strf.trim_(*valores) elif self.function == "get_byte": value = strf.get_byte(*valores) elif self.function == "md5": type_ = TYPE.STRING value = strf.md5(*valores) elif self.function == "set_byte": type_ = TYPE.STRING value = strf.set_byte(*valores) elif self.function == "sha256": type_ = TYPE.STRING value = strf.sha256(*valores) elif self.function == "substr": type_ = TYPE.STRING value = strf.substring(*valores) elif self.function == "convert_date": type_ = TYPE.DATETIME value = strf.convert_date(*valores) elif self.function == "convert_int": value = strf.convert_int(*valores) elif self.function == "encode": type_ = TYPE.STRING value = strf.encode(*valores) elif self.function == "decode": type_ = TYPE.STRING value = strf.decode(*valores) # Se toma en cuenta que la funcion now produce tipo DATE elif self.function == "now": type_ = TYPE.DATETIME value = datetime.now().strftime("%Y/%m/%d %H:%M:%S") else: # TODO: Agregar un error de funcion desconocida value = valores[0] if isinstance(value, list): if len(value) <= 1: value = value[0] else: value = pd.Series(value) return Primitive(type_, value, self.temp, self.row, self.column) except TypeError: expression.list_errors.append("Error: 42883: La funcion " + str(self.function) + "(" + str(type_) + ") no existe" + "\n En la linea: " + str(self.row)) except: expression.list_errors.append("Error: P0001: Error en funciones")
def execute(self, environment): try: if self.type == "TIMESTAMP": if self.str[0] == "now": self.str = datetime.now().strftime( "%Y/%m/%d %H:%M:%S").split() if self.opt == "years": val = self.str[0][:4] elif self.opt == "months": val = self.str[0][5:7] elif self.opt == "days": val = self.str[0][8:10] elif self.opt == "hours": val = self.str[1][:2] elif self.opt == "minutes": val = self.str[1][3:5] elif self.opt == "seconds": val = self.str[1][6:8] else: expression.list_errors.append( "Error: 22007: Formato de fecha invalido " + str(self.str)) # ERROR val = self.str elif self.type == "DATE": if self.opt == "years": val = self.str[0][:4] elif self.opt == "months": val = self.str[0][5:7] elif self.opt == "days": val = self.str[0][8:10] else: expression.list_errors.append( "Error: 22007: Formato de fecha invalido " + str(self.str)) # ERROR val = self.str elif self.type == "TIME": if self.opt == "hours": val = self.str[0][:2] elif self.opt == "minutes": val = self.str[0][3:5] elif self.opt == "seconds": val = self.str[0][6:8] else: expression.list_errors.append( "Error: 22007: Formato de fecha invalido " + str(self.str)) # ERROR val = self.str elif self.type == "INTERVAL": if self.opt == "years": idx = self.str.index("years") val = self.str[idx - 1] elif self.opt == "months": idx = self.str.index("months") val = self.str[idx - 1] elif self.opt == "days": idx = self.str.index("days") val = self.str[idx - 1] elif self.opt == "hours": idx = self.str.index("hours") val = self.str[idx - 1] elif self.opt == "minutes": idx = self.str.index("minutes") val = self.str[idx - 1] elif self.opt == "seconds": idx = self.str.index("seconds") val = self.str[idx - 1] else: expression.list_errors.append( "Error: 22007: Formato de fecha invalido " + str(self.str)) # ERROR val = self.str elif self.type == "NOW": self.str = datetime.now().strftime("%Y/%m/%d %H:%M:%S").split() if self.opt == "years": val = self.str[0][:4] elif self.opt == "months": val = self.str[0][5:7] elif self.opt == "days": val = self.str[0][8:10] elif self.opt == "hours": val = self.str[1][:2] elif self.opt == "minutes": val = self.str[1][3:5] elif self.opt == "seconds": val = self.str[1][6:8] else: expression.list_errors.append( "Error: 22007: Formato de fecha invalido " + str(self.str)) # ERROR val = self.str else: val = self.str expression.list_errors.append( "Error: 22007: Formato de fecha invalido " + str(self.str)) # ERROR return Primitive(TYPE.NUMBER, int(val), self.temp, self.row, self.column) except TypeError: expression.list_errors.append( "Error: 42804: discrepancia de tipo de datos ") pass except ValueError: # cuando no tiene el valor INTERVAL expression.list_errors.append( "Error: 22007:sintaxis de entrada no válida para el tipo 'interval' " ) pass
def execute(self, environment): try: valores = self.colData.execute(environment) if isinstance(valores.value, pd.core.series.Series): lst = valores.value.tolist() lst = [v.split() for v in lst] else: lst = [valores.split()] if valores.type == TYPE.TIMESTAMP or valores.type == TYPE.DATETIME: if self.opt == "YEAR": val = [date[0][:4] for date in lst] elif self.opt == "MONTH": val = [date[0][5:7] for date in lst] elif self.opt == "DAY": val = [date[0][8:10] for date in lst] elif self.opt == "HOUR": val = [date[1][:2] for date in lst] elif self.opt == "MINUTE": val = [date[1][3:5] for date in lst] elif self.opt == "SECOND": val = [date[1][6:8] for date in lst] else: # ERROR expression.list_errors.append( "Error: 22007: Formato de fecha invalido " + str(self.str)) val = self.str elif valores.type == TYPE.DATE: if self.opt == "YEAR": val = [date[0][:4] for date in lst] elif self.opt == "MONTH": val = [date[0][5:7] for date in lst] elif self.opt == "DAY": val = [date[0][8:10] for date in lst] else: val = self.str raise Exception elif valores.type == TYPE.TIME: if self.opt == "HOUR": val = [date[0][:2] for date in lst] elif self.opt == "MINUTE": val = [date[0][3:5] for date in lst] elif self.opt == "SECOND": val = [date[0][6:8] for date in lst] else: val = self.str raise Exception else: val = self.str raise Exception if isinstance(val, list): if len(val) <= 1: val = val[0] else: val = pd.Series(val) return Primitive(TYPE.NUMBER, val, self.temp, self.row, self.column) except TypeError: expression.list_errors.append( "Error: 42804: discrepancia de tipo de datos ") except ValueError: # cuando no tiene el valor INTERVAL expression.list_errors.append( "Error: 22007:sintaxis de entrada no válida para el tipo 'interval' " ) except: raise expression.list_errors.append( "Error: 22007: Formato de fecha invalido " + str(self.str))
def execute(self, environment): countGr = environment.groupCols if countGr == 0: if self.colData != "*": c = self.colData.execute(environment).value if self.func == "sum": newDf = c.sum() elif self.func == "count": newDf = c.count() elif self.func == "prom": newDf = c.mean() else: newDf = None expression.list_errors.append( "Error: 42725: Error en la funcion " + str(self.func) + "\n En la linea: " + str(self.row)) else: c = environment.dataFrame.iloc[:, -1:] if self.func == "count": newDf = len(c) else: newDf = None expression.list_errors.append( "Error: 42725: Error en la funcion " + str(self.func) + "\n En la linea: " + str(self.row)) return Primitive(TYPE.NUMBER, newDf, self.temp, self.row, self.column) if self.colData != "*": # Obtiene las ultimas columnas metidas (Las del group by) df = environment.dataFrame.iloc[:, -countGr:] c = self.colData.execute(environment) x = c.value x = pd.DataFrame(x) x.rename(columns={x.columns[0]: c.temp}, inplace=True) if len(list(x.columns)) > 1: df = pd.concat([df, x.iloc[:, :1]], axis=1) else: df = pd.concat([df, x], axis=1) cols = list(df.columns)[:-1] if self.func == "sum": newDf = df.groupby(cols).sum().reset_index() elif self.func == "count": newDf = df.groupby(cols).count().reset_index() elif self.func == "prom": newDf = df.groupby(cols).mean().reset_index() else: newDf = None expression.list_errors.append( "Error: 42725: Error en la funcion " + str(self.func) + "\n En la linea: " + str(self.row)) value = newDf.iloc[:, -1:] else: # Obtiene las ultimas columnas metidas (Las del group by) df = environment.dataFrame.iloc[:, -countGr:] x = df.iloc[:, -1:] x = pd.DataFrame(x) x.rename(columns={x.columns[0]: "count(*)"}, inplace=True) df = pd.concat([df, x], axis=1) cols = list(df.columns)[:-1] if self.func == "count": newDf = df.groupby(cols).count().reset_index() else: newDf = None expression.list_errors.append( "Error: 42725: Error en la funcion " + str(self.func) + "\n En la linea: " + str(self.row)) value = newDf.iloc[:, -1:] return Primitive(TYPE.NUMBER, value, self.temp, self.row, self.column)