| 22 | return "'%s'" % value.replace("'", "''") |
| 23 | |
| 24 | def parseQuery(self, query, params): |
| 25 | query_type = query.split(" ", 1)[0].upper() |
| 26 | if isinstance(params, dict) and "?" in query: # Make easier select and insert by allowing dict params |
| 27 | if query_type in ("SELECT", "DELETE", "UPDATE"): |
| 28 | # Convert param dict to SELECT * FROM table WHERE key = ? AND key2 = ? format |
| 29 | query_wheres = [] |
| 30 | values = [] |
| 31 | for key, value in params.items(): |
| 32 | if type(value) is list: |
| 33 | if key.startswith("not__"): |
| 34 | field = key.replace("not__", "") |
| 35 | operator = "NOT IN" |
| 36 | else: |
| 37 | field = key |
| 38 | operator = "IN" |
| 39 | if len(value) > 100: |
| 40 | # Embed values in query to avoid "too many SQL variables" error |
| 41 | query_values = ",".join(map(helper.sqlquote, value)) |
| 42 | else: |
| 43 | query_values = ",".join(["?"] * len(value)) |
| 44 | values += value |
| 45 | query_wheres.append( |
| 46 | "%s %s (%s)" % |
| 47 | (field, operator, query_values) |
| 48 | ) |
| 49 | else: |
| 50 | if key.startswith("not__"): |
| 51 | query_wheres.append(key.replace("not__", "") + " != ?") |
| 52 | elif key.endswith("__like"): |
| 53 | query_wheres.append(key.replace("__like", "") + " LIKE ?") |
| 54 | elif key.endswith(">"): |
| 55 | query_wheres.append(key.replace(">", "") + " > ?") |
| 56 | elif key.endswith("<"): |
| 57 | query_wheres.append(key.replace("<", "") + " < ?") |
| 58 | else: |
| 59 | query_wheres.append(key + " = ?") |
| 60 | values.append(value) |
| 61 | wheres = " AND ".join(query_wheres) |
| 62 | if wheres == "": |
| 63 | wheres = "1" |
| 64 | query = re.sub("(.*)[?]", "\\1 %s" % wheres, query) # Replace the last ? |
| 65 | params = values |
| 66 | else: |
| 67 | # Convert param dict to INSERT INTO table (key, key2) VALUES (?, ?) format |
| 68 | keys = ", ".join(params.keys()) |
| 69 | values = ", ".join(['?' for key in params.keys()]) |
| 70 | keysvalues = "(%s) VALUES (%s)" % (keys, values) |
| 71 | query = re.sub("(.*)[?]", "\\1%s" % keysvalues, query) # Replace the last ? |
| 72 | params = tuple(params.values()) |
| 73 | elif isinstance(params, dict) and ":" in query: |
| 74 | new_params = dict() |
| 75 | values = [] |
| 76 | for key, value in params.items(): |
| 77 | if type(value) is list: |
| 78 | for idx, val in enumerate(value): |
| 79 | new_params[key + "__" + str(idx)] = val |
| 80 | |
| 81 | new_names = [":" + key + "__" + str(idx) for idx in range(len(value))] |