def insert_many(self, columns, rows): ncols = 0 if columns is not None and len(columns) > 0: ncols = len(columns) for col in columns: if not self.has_column(col): raise ValueError("No column [%s] in table %s" % (col, sql(self))) else: ncols = max(len(row) for row in rows) colnames = [] for col in columns: col = (col if isinstance(col, str) else sql(col)).split(".") if len(col) == 3 and col[0] == self.pg_schema and col[1] == (self.alias or self.name): colnames.append(col[2]) elif len(col) == 2 and col[1] == (self.alias or self.name): colnames.append(col[1]) elif len(col) == 1: colnames.append(col[0]) else: raise ValueError("Cannot parse column: %s" % col) sql_str = "INSERT INTO %s%s VALUES (%s);" % ( sql(self), " (%s)" % (",".join(colnames)) if len(colnames) > 0 else "", ",".join("%s" for i in range(ncols)) ) cursor = self._exmany(sql_str, rows) if len(rows) > 1 else self._ex(sql_str, next(iter(rows))) rc = cursor.rowcount cursor.close() return rc
def __sql__(self, **kwargs): stmt = "DELETE FROM " + sql(self._src, usage="table") for join in self._joins: stmt += " " + sql(join) + " " if self._constraint is not None: stmt += " WHERE " stmt += sql(self._constraint) if self._having is not None: stmt += " HAVING " stmt += sql(self._having) return stmt
def column(self, colname): if self._join_alias is None: logging.warn( "Referencing query column without query alias - this will be EXTREMELY slow (if it works at all)" ) return Column(Literal(sql(self)), colname)
def has_column(self, col): if isinstance(col, Column): if sql(col.table) == sql(self): return col.colname in self.schema else: col = str(col).split(".") if len(col) == 3 and col[0] == self.pg_schema and col[1] == (self.alias or self.name): return col[2] in self.schema elif len(col) == 2 and col[1] == (self.alias or self.name): return col[1] in self.schema elif len(col) == 1: return col[0] in self.schema else: raise ValueError("Cannot parse column: %s" % col)
def exists(self): cursor = self._ex( "SELECT name FROM %s.sqlite_master WHERE type='table' AND name=%s" % (escape_value(self.db), escape_value(self.name))) nfound = len(cursor.fetchall()) cursor.close() if nfound > 1: _LOGGER.warn("Found more than one existing table with the name %s", sql(self)) return nfound > 0
def exists(self): cursor = self._ex("SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = %s AND tablename = %s;", ( self.pg_schema, self.name )) nfound = len(cursor.fetchall()) cursor.close() if nfound > 1: _LOGGER.warn("Found more than one existing table with the name %s", sql(self)) return nfound > 0
def execute(self, asdict=False): cursor = sqlexec(Query._LOGGER, self._src.conn, sql(self)) if asdict: lst = [{k[0]: v for k, v in zip(cursor.description, x)} for x in cursor.fetchall()] else: lst = [x if len(x) > 1 else x[0] for x in cursor.fetchall()] cursor.close() return lst
def __sql__(self, usage=None, **kwargs): stmt = "SELECT " \ + ",".join(sql(c, usage="value") for c in self._cols) \ + " FROM " \ + sql(self._src, usage="table-like") for join in self._joins: stmt += " " + sql(join) + " " if self._constraint is not None: stmt += " WHERE " stmt += sql(self._constraint) if self._group_by is not None: stmt += " GROUP BY " stmt += sql(self._group_by) if self._having is not None: stmt += " HAVING " stmt += sql(self._having) if self._order_by is not None: stmt += " ORDER BY " stmt += sql(self._order_by) if self._order_dir is not None: stmt += " " + self._order_dir if self._size is not None: stmt += " LIMIT %s" % sql(self._size) elif self._offset is not None: stmt += " LIMIT -1" if self._offset is not None: stmt += " OFFSET %s" % sql(self._offset) if usage == "value": return "(%s)" % stmt elif usage == 'table-like': return "(%s)%s" % (stmt, " AS %s" % sql(self._join_alias) if self._join_alias else "") else: if self._join_alias is not None: return self._join_alias else: return stmt
def foreign_key(self, column, table: Table, name=None, tcolumns=None): if not isinstance(column, (set, list)): column = [column] if isinstance(table, Table): if table.db != self._table.db: raise RuntimeError("Must be in the same database") table = sql(table, usage="table-noalias") for col in column: self._columns[col]["fk"] = { "name": name, "table": table, "tcols": list(tcolumns) if tcolumns else None } return self
def __sql__(self, **kwargs): stmt = "UPDATE %s SET " % sql(self._src, usage="table") stmt += " %s " % (", ".join( "%s = %s" % (sql(dst, usage="column"), sql(src, usage="value")) for (dst, src) in self._updates)) for join in self._joins: stmt += " %s " % sql(join) if self._constraint is not None: stmt += " WHERE " stmt += sql(self._constraint) if self._having is not None: stmt += " HAVING " stmt += sql(self._having) return stmt
def create(self): s = [("%s;" % x) for x in sql(self).split(";") if len(x.strip()) > 0] for _x in s: execute(_LOGGER, self._table.conn, _x).close() del self._table.schema
def drop(self): self._ex("DROP TABLE %s;", (sql(self),)).close()
def __repr__(self): return "Condition[%s]" % skip_empty_join(" ", sql(self.lhs), sql(self.op), sql(self.rhs))
def create(self): execute(_LOGGER, self._table.conn, sql(self)).close() del self._table.schema
def __str__(self): return "Table[%s]" % sql(self, usage="table-like")
def __sql__(self, **kwargs): s = "CREATE SCHEMA IF NOT EXISTS %s; CREATE TABLE %s (" % ( self._table.pg_schema, sql(self._table, usage="table-noalias") ) chunks = [] constraints = {} for (name, col) in self._columns.items(): constraint = "" if "pk" in col: pkname = col["pk"]["name"] if pkname is None: pkname = "%s_%s_pk" % (self._table.pg_schema, self._table.name) c = computeIfAbsent(constraints, pkname, lambda k: { "type": "pk", "params": [], "sql": lambda name, self: "CONSTRAINT \"%s\" PRIMARY KEY (%s)" % (name, ",".join(self["params"])) }) if c["type"] != "pk": raise ValueError("Constraint %s is not a primary key" % pkname) c["params"].append(name) if "fk" in col: fk = col["fk"] fkname = fk["name"] if fkname is None: x = 1 while True: fkname = ("%s_%s_fk_%d" % (self._table.pg_schema, self._table.name, x)) if fkname not in constraints: break x += 1 c = computeIfAbsent(constraints, fkname, lambda k: { "type": "fk", "table": fk["table"], "tcols": fk["tcols"], "params": [], "sql": lambda name, self: "CONSTRAINT \"%s\" FOREIGN KEY (%s) REFERENCES %s%s" % ( name, ",".join(self["params"]), self["table"], ("(%s)" % (",".join(self["tcols"]))) if bool(self["tcols"]) else "" ) }) if c["type"] != "fk": raise ValueError("Constraint %s is not a foreign key" % pkname) c["params"].append(name) if col.get("unique", False): constraint += " UNIQUE" if col.get("nullable") == True: constraint += " NULL" if col.get("nullable") == False: constraint += " NOT NULL" chunks.append("%s %s%s" % (name, sql(col["type"]), constraint)) for name, c in constraints.items(): chunks.append(c["sql"](name, c)) s += ", ".join(chunks) s += ");" return s
def __sql__(self, **kwargs): return "(%s)" % skip_empty_join(" ", sql(self.lhs, usage="value"), sql(self.op), sql(self.rhs, usage="value"))
def column(self, name): if not self.has_column(name): raise RuntimeError("No column [%s] in table %s" % (name, sql(self))) return Column(self, name)
def __sql__(self, **kwargs): return "%s JOIN %s ON %s" % (self.join_type.upper(), sql(self.tbl, usage="table-like"), sql(self.join_condition))
def execute(self): cursor = sqlexec(Delete._LOGGER, self._src.conn, sql(self)) rc = cursor.rowcount cursor.close() return rc
def columns(self): return Navigator( self, "ColumnList", lambda: [sql(x, usage="column-like") for x in self._cols], lambda x: self.column(x))
def is_in(self, values): return Condition(self, Literal("IN"), Literal("(%s)" % (",".join(sql(x) for x in values))))
def __sql__(self, usage=None): if usage in ("column", "column-like"): return self.colname else: return "%s.%s" % (sql(self.table), self.colname)
def __repr__(self): return "Column[%s.%s]" % (sql(self.table), self.colname)