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)
