def into(self, table, schema=None):
"""
Sets table name and schema
if table is object, it will also set fields and values
:param table: string or record object
:param schema: optional string
:return: self
"""
if isinstance(table, str):
pass
elif isinstance(table, object):
schema = getattr(table, ATTR_SCHEMA, schema)
tname = getattr(table, ATTR_TABLE, None)
if tname is None:
raise SqlError("into(): invalid type for table name")
if not isclass(table):
self.values(table)
table = tname
else:
raise SqlError("into(): invalid type for table name")
if schema is not None and not isinstance(schema, str):
raise SqlError("into(): Invalid type for schema name: %s" %
str(type(schema)))
self._table = table
self._schema = schema
return self
def values(self, values: Union[list, dict, object]):
"""
Set fields and/or values for insertion
This method can be called multiple times; new field/value pairs will be added to the internal structure;
Existing fields will have their value overridden
:param values: list, dict or record object
:return: self
"""
# if list, replace values
if isinstance(values, (list, tuple)):
self._values = values
elif isinstance(values, collections.abc.Mapping):
self._fields = values.keys()
self._values = list(values.values())
elif isinstance(values, object):
# support any object that has a method "asrecord"
if not callable(getattr(values, 'asrecord', None)):
raise SqlError(
"values(): invalid object type for data parameter")
values = values.asrecord()
self._fields = values.keys()
self._values = list(values.values())
else:
raise SqlError("values(): Invalid data type")
return self
def from_(self, table, schema=None):
"""
DELETE FROM table name and schema
:param table: string or record object
:param schema: optional string
:return: self
Possible values for table:
'table' -> string with table name
<object_or_class> -> record class or object
"""
if isinstance(table, str):
pass
elif isinstance(table, object):
schema = getattr(table, ATTR_SCHEMA, schema)
table = getattr(table, ATTR_TABLE, None)
if table is None:
raise SqlError("from_(): invalid type for table name")
else:
raise SqlError("from_(): invalid type for table name")
if schema is not None and type(schema) is not str:
raise SqlError("from_(): Invalid type for schema name: %s" %
str(type(schema)))
self._table = table
self._schema = schema
return self
def field(self, field, field_alias=None, table=None, schema=None):
"""
Quotes a field name, optimizing for PostgreSQL syntax
:param field: field name or Literal
:param field_alias: optional alias and/or cast information
:param table: optional table
:param schema: optional schema
:return: str
Examples:
field('field', None) -> "field"
field('field', 'alias') -> "field" AS "alias"
field('field', ['text']) -> "field"::text
field('field', ['text', 'alias']) -> "field"::text AS "alias"
field(Literal('COUNT(*)'), ['int', 'total']) -> COUNT(*)::int AS "total"
field('field', 'alias', 'table') -> "table"."field" AS "alias"
field('field', 'alias', 'table', 'public') -> "public"."table"."field" AS "alias"
"""
if table is not None:
table = self._quote_table.format(table=table) + self._separator
if schema is not None:
table = self._quote_schema.format(
schema=schema) + self._separator + table
else:
table = ""
if isinstance(field, Literal):
field = str(field)
table = ""
elif field != "*":
field = self._quote_field.format(field=field)
field = table + field
if field_alias is None:
return field
elif isinstance(field_alias, str):
return self._as.join(
[field, self._quote_field.format(field=field_alias)])
elif isinstance(field_alias, (list, tuple)):
_len = len(field_alias)
if _len == 0:
raise SqlError("Alias for field %s cannot be empty" % field)
# generate pg-style cast with ::<type>
cast = self._cast + field_alias[0]
if _len > 1:
return self._as.join([
field + cast,
self._quote_field.format(field=field_alias[1])
])
else:
return field + cast
else:
raise SqlError("Cannot parse fields")
def _where(self, field, operator=None, value=None, is_and=True):
"""
Internal where handler
:param field: expression
:param operator: clause operator
:param value: optional value
:param is_and: True to interleave with AND, False to OR
:return: self
"""
concat = Sql.SQL_AND
if is_and is False:
concat = Sql.SQL_OR
if isinstance(field, str):
field = self._dialect.field(field)
elif isinstance(field, Literal):
field = str(field)
else:
raise SqlError("_where(): invalid field name type")
if value is None:
if operator is None:
expression = "{fld}".format(fld=field)
else:
expression = "{fld} {op}".format(fld=field, op=operator)
self._clauses.append([expression, concat])
else:
# sanity check, as we actually may have value list if subquery is in use
if isinstance(value, (list, tuple, dict)):
raise SqlError("_where(): invalid value type: %s" %
str(type(value)))
if operator is None:
expression = "{fld} {ph}".format(fld=field,
ph=self._dialect.placeholder)
else:
if isinstance(value, Select):
sql, value = value.assemble()
expression = "{fld} {op} ({query})".format(fld=field,
op=operator,
query=sql)
else:
expression = "{fld} {op} {ph}".format(
fld=field, op=operator, ph=self._dialect.placeholder)
self._clauses.append([expression, concat])
if isinstance(value, list):
self._clause_values.extend(value)
else:
self._clause_values.append(value)
return self
def field(self, field, field_alias=None, table=None, schema=None):
"""
Quotes a field name
:param field: field name or Literal
:param field_alias: optional alias and/or cast information
:param table: optional table
:param schema: optional schema (not supported)
:return: str
Examples:
field('field', None) -> "field"
field('field', 'alias') -> "field" AS "alias"
field('field', ['text']) -> CAST("field" AS text)
field('field', ['text', 'alias']) -> CAST("field" AS text) AS "alias"
field(Literal('COUNT(*)'), ['int', 'total']) -> CAST(COUNT(*) AS int) AS "total"
field('field', 'alias', 'table') -> "table"."field" AS "alias"
field('field', 'alias', 'table', 'public') -> "public"."table"."field" AS "alias"
"""
if table is not None:
table = self._quote_table.format(table=table) + self._separator
if schema is not None:
table = self._quote_schema.format(
schema=schema) + self._separator + table
else:
table = ""
if isinstance(field, Literal):
field = str(field)
table = ""
elif field != "*":
field = self._quote_field.format(field=field)
field = table + field
if field_alias is None:
return field
elif isinstance(field_alias, str):
return self._as.join(
[field, self._quote_field.format(field=field_alias)])
elif isinstance(field_alias, (list, tuple)):
_len = len(field_alias)
if _len == 0:
raise SqlError("Alias for field %s cannot be empty" % field)
field = self._cast.format(field=field, cast=field_alias[0])
if _len > 1:
return self._as.join(
[field,
self._quote_field.format(field=field_alias[1])])
else:
return field
else:
raise SqlError("Cannot parse fields")
def returning(self, fields):
"""
RETURNING clause
:param fields: str or list with fields to be returned
:return: self
"""
if isinstance(fields, str):
fields = [fields]
if not isinstance(fields, (list, tuple)):
raise SqlError("returning(): invalid return field type: %s" %
str(type(fields)))
if len(fields) == 0:
raise SqlError("returning(): field list cannot be empty")
self._returning = fields
return self
def fields(self, fields: list):
"""
Set fields for insertion
:param fields: list of field names
:return: self
"""
if not isinstance(fields, (list, tuple)):
raise SqlError("fields(): invalid type for fields parameter")
self._fields = fields
return self
def assemble(self):
"""
Assemble the UPDATE statement
:return: tuple(str, list)
"""
# simple validations
lf = len(self._fields)
if lf == 0:
raise SqlError("assemble(): field list is empty")
if lf != len(self._values):
raise SqlError("assemble(): field and value count mismatch")
parts = [
Sql.SQL_UPDATE,
self._dialect.table(self._table, None, schema=self._schema),
Sql.SQL_SET,
]
# generate field list and placeholder list
fields = []
for name in self._fields:
fields.append("{field}={ph}".format(
field=self._dialect.field(name), ph=self._dialect.placeholder))
parts.append(", ".join(fields))
values = self._values
# where clause
if len(self._clauses) > 0:
c = 0
parts.append(Sql.SQL_WHERE)
values.extend(self._clause_values)
for clause in self._clauses:
expr, concat = clause
if c > 0:
parts.append(concat)
parts.append(expr)
c += 1
return " ".join(parts), values
def assemble(self):
"""
Assemble the INSERT SQL
:return: tuple(str, list)
"""
# simple validations
lf = len(self._fields)
if lf == 0:
raise SqlError("assemble(): field list is empty")
if lf != len(self._values):
raise SqlError("assemble(): field and value count mismatch")
parts = [
Sql.SQL_INSERT,
self._dialect.table(self._table, None, schema=self._schema)
]
# generate field list and placeholder list
fields = []
placeholders = []
for name in self._fields:
fields.append(self._dialect.field(name))
placeholders.append(self._dialect.placeholder)
parts.append("({})".format(", ".join(fields)))
parts.append(Sql.SQL_VALUES)
parts.append("({})".format(", ".join(placeholders)))
# optional returning clause
if self._returning:
fields = []
for name in self._returning:
fields.append(self._dialect.field(name))
parts.append(Sql.SQL_RETURNING)
parts.append(", ".join(fields))
return " ".join(parts), self._values