Skip to content

Class rick_db.sql.Update

Update.__init__(dialect: SqlDialect = None)

Initialize a Update() object, using a database dialect. If no dialect is provided, a default dialect will be used. Check SqlDialect for more details.

Update.table(table, schema=None)

Defines the target table name and schema for update. If table is a Record object, it will also load fields and values from this object.

Example: 

# simple UPDATE example
qry = Update(PgSqlDialect()).table('table').fields(['field']).values(['value'])
# output: ('UPDATE "table" SET "field"=%s', ['value'])
print(qry.assemble())

# UPDATE w/ Record object
record = Publisher(name='some publisher name')
qry = Update(PgSqlDialect()).table(record)
# output: ('UPDATE "publisher" SET "name"=%s', ['some publisher name'])
print(qry.assemble())

Update.fields(fields: list)

Defines the field names to be updated. The length of fields list must match the list of provided values.

Example: 

data = [
    ['john', 'connor'],
    ['sarah', 'connor']
]
sql = []
qry = Update(PgSqlDialect()).table('table').fields(['name', 'surname'])
for v in data:
    qry.values(v)
    sql.append(qry.assemble())

# output:
# [('UPDATE "table" SET "name"=%s, "surname"=%s', ['john', 'connor']), 
# ('UPDATE "table" SET "name"=%s, "surname"=%s', ['sarah', 'connor'])]
print(sql)

Update.values(values: Union[list, dict, object])

Define values of fields to be updated. This method can be called multiple times (see fields() for an example). If values is a dict, both fields and values are read from the provided dict. If values is a Record object, fields and values are read from the object.

Example: 

# simple UPDATE example
qry = Update(PgSqlDialect()).table('table').fields(['field']).values(['value'])
# output: ('UPDATE "table" SET "field"=%s', ['value'])
print(qry.assemble())

# INSERT w/ Record object
record = Publisher(name='some publisher name')
qry = Update(PgSqlDialect()).table('tablename').values(record)
# output: ('UPDATE "tablename" SET "name"=%s', ['some publisher name'])
print(qry.assemble())

Update.where(field, operator=None, value=None)

Adds a WHERE clause to the UPDATE clause. It requires a mandatory field name and an operator, and allows an optional value. Clauses generated by multiple calls to this method are concatenated with AND.

Example:

# UPDATE WHERE... common usage
qry = (
    Update(PgSqlDialect()).table("table").values({"field": "value"}).where("id", "=", 7)
)
# output: ('UPDATE "table" SET "field"=%s WHERE "id" = %s', ['value', 7])
print(qry.assemble())

# UPDATE WHERE... no value
qry = (
    Update(PgSqlDialect())
    .table("table")
    .values({"field": "value"})
    .where("id", "IS NOT NULL")
)
# output: ('UPDATE "table" SET "field"=%s WHERE "id" IS NOT NULL', ['value'])
print(qry.assemble())

# UPDATE WHERE... with multiple clauses
qry = (
    Update(PgSqlDialect())
    .table("table")
    .values({"field": "value"})
    .where("id", "=", 7)
    .where("name", "ILIKE", "john%")
)
# output: ('UPDATE "table" SET "field"=%s WHERE "id" = %s AND "name" ILIKE %s', ['value', 7, 'john%'])
print(qry.assemble())

Update.orwhere(field, operator=None, value=None)

Adds a WHERE clause to the UPDATE clause. It requires a mandatory field name and an operator, and allows an optional value. Clauses generated by multiple calls to this method are concatenated with OR.

Example:

qry = (
    Update(PgSqlDialect())
    .table("table")
    .values({"field": "value"})
    .where("id", "=", 7)
    .orwhere("name", "ILIKE", "john%")
)
# output: ('UPDATE "table" SET "field"=%s WHERE "id" = %s OR "name" ILIKE %s', ['value', 7, 'john%'])
print(qry.assemble())

Update.returning(fields: Union[list, str] = Sql.SQL_ALL)

Adds a RETURNING clause to the UPDATE clause with a list of field names. If fields is empty, '*' is used. Example:

qry = (
    Update(PgSqlDialect())
    .table("table")
    .values({"field": "value"})
    .where("id", "=", 7)
    .orwhere("name", "ILIKE", "john%")
    .returning()
)

# output: ('UPDATE "table" SET "field"=%s WHERE "id" = %s OR "name" ILIKE %s RETURNING *', ['value', 7, 'john%'])
print(qry.assemble())

Update.assemble()

Assembles UPDATE SQL string and returns a tuple with (sql_string, list_of_values). If an error occurs, SqlError is raised.

Example:

# simple UPDATE example
qry = Update(PgSqlDialect()).table('table').fields(['field']).values(['value'])
# output: ('UPDATE "table" SET "field"=%s', ['value'])
print(qry.assemble())