Skip to content

update

Update query builder.

UpdateQuery

UpdateQuery is used to update rows.

It is never used directly, but always created from a Db. It returns an UpdateQueryReady instance once set() has been called.

from embar.db.pg import PgDb
from embar.query.update import UpdateQuery
db = PgDb(None)
update = db.update(None)
assert isinstance(update, UpdateQuery)
Source code in src/embar/query/update.py 
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
class UpdateQuery[T: Table, Db: AllDbBase]:
    """
    `UpdateQuery` is used to update rows.

    It is never used directly, but always created from a Db.
    It returns an `UpdateQueryReady` instance once `set()` has been called.

    ```python
    from embar.db.pg import PgDb
    from embar.query.update import UpdateQuery
    db = PgDb(None)
    update = db.update(None)
    assert isinstance(update, UpdateQuery)
    ```
    """

    table: type[T]
    _db: Db

    def __init__(self, table: type[T], db: Db):
        """
        Create a new UpdateQuery instance.
        """
        self.table = table
        self._db = db

    def set(self, data: Mapping[str, Any]) -> UpdateQueryReady[T, Db]:
        """
        Set the values to be updated.
        """
        return UpdateQueryReady(table=self.table, db=self._db, data=data)

__init__(table, db)

Create a new UpdateQuery instance.

Source code in src/embar/query/update.py 
34
35
36
37
38
39
def __init__(self, table: type[T], db: Db):
    """
    Create a new UpdateQuery instance.
    """
    self.table = table
    self._db = db

set(data)

Set the values to be updated.

Source code in src/embar/query/update.py 
41
42
43
44
45
def set(self, data: Mapping[str, Any]) -> UpdateQueryReady[T, Db]:
    """
    Set the values to be updated.
    """
    return UpdateQueryReady(table=self.table, db=self._db, data=data)

UpdateQueryReady

UpdateQueryReady is an update query that is ready to be awaited or run.

Source code in src/embar/query/update.py 
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
class UpdateQueryReady[T: Table, Db: AllDbBase]:
    """
    `UpdateQueryReady` is an update query that is ready to be awaited or run.
    """

    table: type[T]
    _db: Db
    data: Mapping[str, Any]
    _where_clause: WhereClause | None = None

    def __init__(self, table: type[T], db: Db, data: Mapping[str, Any]):
        """
        Create a new UpdateQueryReady instance.
        """
        self.table = table
        self._db = db
        self.data = data

    def where(self, where_clause: WhereClause) -> Self:
        """
        Add a WHERE clause to limit which rows are updated.
        """
        self._where_clause = where_clause
        return self

    def returning(self) -> UpdateQueryReturning[T, Db]:
        return UpdateQueryReturning(self.table, self._db, self.data, self._where_clause)

    def __await__(self):
        """
        async users should construct their query and await it.

        non-async users have the `run()` convenience method below.
        """
        query = self.sql()

        async def awaitable():
            db = self._db
            if isinstance(db, AsyncDbBase):
                return await db.execute(query)
            else:
                db = cast(DbBase, self._db)
                return db.execute(query)

        return awaitable().__await__()

    @overload
    def run(self: UpdateQueryReady[T, DbBase]) -> None: ...
    @overload
    def run(self: UpdateQueryReady[T, AsyncDbBase]) -> UpdateQueryReady[T, Db]: ...

    def run(self) -> None | UpdateQueryReady[T, Db]:
        """
        Run the query against the underlying DB.

        Convenience method for those not using async.
        But still works if awaited.
        """
        if isinstance(self._db, DbBase):
            query = self.sql()
            return self._db.execute(query)
        return self

    def sql(self) -> QuerySingle:
        """
        Combine all the components of the query and build the SQL and bind parameters (psycopg format).
        """
        count = -1

        def get_count() -> int:
            nonlocal count
            count += 1
            return count

        params: dict[str, Any] = {}

        cols = self.table.column_names()

        setters: list[str] = []
        for field_name, value in self.data.items():
            col = cols[field_name]
            count = get_count()
            binding_name = f"set_{field_name}_{count}"
            setter = f'"{col}" = %({binding_name})s'
            setters.append(setter)
            params[binding_name] = value

        set_stmt = ", ".join(setters)
        sql = f"UPDATE {self.table.fqn()} SET {set_stmt}"

        if self._where_clause is not None:
            where_data = self._where_clause.sql(get_count)
            sql += f"\nWHERE {where_data.sql}"
            params = {**params, **where_data.params}

        return QuerySingle(sql, params)

__await__()

async users should construct their query and await it.

non-async users have the run() convenience method below.

Source code in src/embar/query/update.py 
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
def __await__(self):
    """
    async users should construct their query and await it.

    non-async users have the `run()` convenience method below.
    """
    query = self.sql()

    async def awaitable():
        db = self._db
        if isinstance(db, AsyncDbBase):
            return await db.execute(query)
        else:
            db = cast(DbBase, self._db)
            return db.execute(query)

    return awaitable().__await__()

__init__(table, db, data)

Create a new UpdateQueryReady instance.

Source code in src/embar/query/update.py 
58
59
60
61
62
63
64
def __init__(self, table: type[T], db: Db, data: Mapping[str, Any]):
    """
    Create a new UpdateQueryReady instance.
    """
    self.table = table
    self._db = db
    self.data = data

run() 

run() -> None

run() -> UpdateQueryReady[T, Db]

Run the query against the underlying DB.

Convenience method for those not using async. But still works if awaited.

Source code in src/embar/query/update.py 
 99
100
101
102
103
104
105
106
107
108
109
def run(self) -> None | UpdateQueryReady[T, Db]:
    """
    Run the query against the underlying DB.

    Convenience method for those not using async.
    But still works if awaited.
    """
    if isinstance(self._db, DbBase):
        query = self.sql()
        return self._db.execute(query)
    return self

sql()

Combine all the components of the query and build the SQL and bind parameters (psycopg format).

Source code in src/embar/query/update.py 
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
def sql(self) -> QuerySingle:
    """
    Combine all the components of the query and build the SQL and bind parameters (psycopg format).
    """
    count = -1

    def get_count() -> int:
        nonlocal count
        count += 1
        return count

    params: dict[str, Any] = {}

    cols = self.table.column_names()

    setters: list[str] = []
    for field_name, value in self.data.items():
        col = cols[field_name]
        count = get_count()
        binding_name = f"set_{field_name}_{count}"
        setter = f'"{col}" = %({binding_name})s'
        setters.append(setter)
        params[binding_name] = value

    set_stmt = ", ".join(setters)
    sql = f"UPDATE {self.table.fqn()} SET {set_stmt}"

    if self._where_clause is not None:
        where_data = self._where_clause.sql(get_count)
        sql += f"\nWHERE {where_data.sql}"
        params = {**params, **where_data.params}

    return QuerySingle(sql, params)

where(where_clause)

Add a WHERE clause to limit which rows are updated.

Source code in src/embar/query/update.py 
66
67
68
69
70
71
def where(self, where_clause: WhereClause) -> Self:
    """
    Add a WHERE clause to limit which rows are updated.
    """
    self._where_clause = where_clause
    return self

UpdateQueryReturning

UpdateQueryReturning is an update query that returns all results.

Source code in src/embar/query/update.py 
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
class UpdateQueryReturning[T: Table, Db: AllDbBase]:
    """
    `UpdateQueryReturning` is an update query that returns all results.
    """

    table: type[T]
    _db: Db
    data: Mapping[str, Any]
    _where_clause: WhereClause | None = None

    def __init__(self, table: type[T], db: Db, data: Mapping[str, Any], where_clause: WhereClause | None):
        """
        Create a new UpdateQueryReturning instance.
        """
        self.table = table
        self._db = db
        self.data = data
        self._where_clause = where_clause

    def __await__(self) -> Generator[Any, None, Sequence[T]]:
        """
        async users should construct their query and await it.

        non-async users have the `run()` convenience method below.
        """
        query = self.sql()
        model = self._get_model()
        model = cast(type[T], model)
        adapter = TypeAdapter(list[model])

        async def awaitable():
            db = self._db
            if isinstance(db, AsyncDbBase):
                data = await db.fetch(query)
            else:
                db = cast(DbBase, self._db)
                data = db.fetch(query)
            results = adapter.validate_python(data)
            return results

        return awaitable().__await__()

    @overload
    def run(self: UpdateQueryReturning[T, DbBase]) -> list[T]: ...
    @overload
    def run(self: UpdateQueryReturning[T, AsyncDbBase]) -> UpdateQueryReturning[T, Db]: ...

    def run(self) -> list[T] | UpdateQueryReturning[T, Db]:
        """
        Run the query against the underlying DB.

        Convenience method for those not using async.
        But still works if awaited.
        """
        if isinstance(self._db, DbBase):
            query = self.sql()
            model = self._get_model()
            model = cast(type[T], model)
            adapter = TypeAdapter(list[model])
            data = self._db.fetch(query)
            results = adapter.validate_python(data)
            return results
        return self

    def sql(self) -> QuerySingle:
        """
        Combine all the components of the query and build the SQL and bind parameters (psycopg format).
        """
        count = -1

        def get_count() -> int:
            nonlocal count
            count += 1
            return count

        params: dict[str, Any] = {}

        cols = self.table.column_names()

        setters: list[str] = []
        for field_name, value in self.data.items():
            col = cols[field_name]
            count = get_count()
            binding_name = f"set_{field_name}_{count}"
            setter = f'"{col}" = %({binding_name})s'
            setters.append(setter)
            params[binding_name] = value

        set_stmt = ", ".join(setters)
        sql = f"UPDATE {self.table.fqn()} SET {set_stmt}"

        if self._where_clause is not None:
            where_data = self._where_clause.sql(get_count)
            sql += f"\nWHERE {where_data.sql}"
            params = {**params, **where_data.params}

        sql += " RETURNING *"

        return QuerySingle(sql, params)

    def _get_model(self) -> type[BaseModel]:
        """
        Generate the dataclass that will be used to deserialize (and validate) the query results.
        """
        model = generate_model(self.table)
        return model

__await__()

async users should construct their query and await it.

non-async users have the run() convenience method below.

Source code in src/embar/query/update.py 
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
def __await__(self) -> Generator[Any, None, Sequence[T]]:
    """
    async users should construct their query and await it.

    non-async users have the `run()` convenience method below.
    """
    query = self.sql()
    model = self._get_model()
    model = cast(type[T], model)
    adapter = TypeAdapter(list[model])

    async def awaitable():
        db = self._db
        if isinstance(db, AsyncDbBase):
            data = await db.fetch(query)
        else:
            db = cast(DbBase, self._db)
            data = db.fetch(query)
        results = adapter.validate_python(data)
        return results

    return awaitable().__await__()

__init__(table, db, data, where_clause)

Create a new UpdateQueryReturning instance.

Source code in src/embar/query/update.py 
156
157
158
159
160
161
162
163
def __init__(self, table: type[T], db: Db, data: Mapping[str, Any], where_clause: WhereClause | None):
    """
    Create a new UpdateQueryReturning instance.
    """
    self.table = table
    self._db = db
    self.data = data
    self._where_clause = where_clause

run() 

run() -> list[T]

run() -> UpdateQueryReturning[T, Db]

Run the query against the underlying DB.

Convenience method for those not using async. But still works if awaited.

Source code in src/embar/query/update.py 
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
def run(self) -> list[T] | UpdateQueryReturning[T, Db]:
    """
    Run the query against the underlying DB.

    Convenience method for those not using async.
    But still works if awaited.
    """
    if isinstance(self._db, DbBase):
        query = self.sql()
        model = self._get_model()
        model = cast(type[T], model)
        adapter = TypeAdapter(list[model])
        data = self._db.fetch(query)
        results = adapter.validate_python(data)
        return results
    return self

sql()

Combine all the components of the query and build the SQL and bind parameters (psycopg format).

Source code in src/embar/query/update.py 
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
def sql(self) -> QuerySingle:
    """
    Combine all the components of the query and build the SQL and bind parameters (psycopg format).
    """
    count = -1

    def get_count() -> int:
        nonlocal count
        count += 1
        return count

    params: dict[str, Any] = {}

    cols = self.table.column_names()

    setters: list[str] = []
    for field_name, value in self.data.items():
        col = cols[field_name]
        count = get_count()
        binding_name = f"set_{field_name}_{count}"
        setter = f'"{col}" = %({binding_name})s'
        setters.append(setter)
        params[binding_name] = value

    set_stmt = ", ".join(setters)
    sql = f"UPDATE {self.table.fqn()} SET {set_stmt}"

    if self._where_clause is not None:
        where_data = self._where_clause.sql(get_count)
        sql += f"\nWHERE {where_data.sql}"
        params = {**params, **where_data.params}

    sql += " RETURNING *"

    return QuerySingle(sql, params)