Skip to content

insert

Insert query builder.

InsertQuery

InsertQuery is used to insert data into a table.

It is generic over the Table being inserted into, and the database being used. InsertQuery is never used directly, but always returned by a Db instance. It returns an InsertQueryReady instance once values() has been called.

from embar.db.pg import PgDb
from embar.query.insert import InsertQuery
db = PgDb(None)
insert = db.insert(None)
assert isinstance(insert, InsertQuery)
Source code in src/embar/query/insert.py 
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
46
47
class InsertQuery[T: Table, Db: AllDbBase]:
    """
    `InsertQuery` is used to insert data into a table.

    It is generic over the `Table` being inserted into, and the database being used.
    `InsertQuery` is never used directly, but always returned by a Db instance.
    It returns an `InsertQueryReady` instance once `values()` has been called.

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

    _db: Db
    table: type[T]

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

    def values(self, *items: T) -> InsertQueryReady[T, Db]:
        """
        Load a sequence of items into the table.
        """
        return InsertQueryReady(table=self.table, db=self._db, items=items)

__init__(table, db)

Create a new InsertQuery instance.

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

values(*items)

Load a sequence of items into the table.

Source code in src/embar/query/insert.py 
43
44
45
46
47
def values(self, *items: T) -> InsertQueryReady[T, Db]:
    """
    Load a sequence of items into the table.
    """
    return InsertQueryReady(table=self.table, db=self._db, items=items)

InsertQueryReady

InsertQueryReady is an insert query that is ready to be awaited or run.

Source code in src/embar/query/insert.py 
 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
144
145
146
147
148
149
150
151
152
class InsertQueryReady[T: Table, Db: AllDbBase]:
    """
    `InsertQueryReady` is an insert query that is ready to be awaited or run.
    """

    _db: Db
    table: type[T]
    items: Sequence[T]
    on_conflict: OnConflict | None = None

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

    def returning(self) -> InsertQueryReturning[T, Db]:
        return InsertQueryReturning(self.table, self._db, self.items, on_conflict=self.on_conflict)

    def on_conflict_do_nothing(self, target: TupleAtLeastOne | None = None) -> Self:
        self.on_conflict = OnConflictDoNothing(target)
        return self

    def on_conflict_do_update(self, target: TupleAtLeastOne, update: dict[str, PyType]) -> Self:
        self.on_conflict = OnConflictDoUpdate(target, update)
        return self

    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.executemany(query)
            else:
                db = cast(DbBase, self._db)
                return db.executemany(query)

        return awaitable().__await__()

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

    def run(self) -> InsertQueryReady[T, Db] | None:
        """
        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.executemany(query)
        return self

    def sql(self) -> QueryMany:
        """
        Create the SQL query and binding parameters (psycopg format) for the query.

        ```python
        from embar.column.common import Text
        from embar.table import Table
        from embar.query.insert import InsertQueryReady
        class MyTable(Table):
            my_col: Text = Text()
        row = MyTable(my_col="foo")
        insert = InsertQueryReady(db=None, table=MyTable, items=[row])
        query = insert.sql()
        assert query.sql == 'INSERT INTO "my_table" ("my_col") VALUES (%(my_col)s)'
        assert query.many_params == [{'my_col': 'foo'}]
        ```
        """

        column_names = self.table.column_names().values()
        column_names_quoted = [f'"{c}"' for c in column_names]
        columns = ", ".join(column_names_quoted)
        placeholders = [f"%({name})s" for name in column_names]
        placeholder_str = ", ".join(placeholders)
        sql = f"INSERT INTO {self.table.fqn()} ({columns}) VALUES ({placeholder_str})"
        values = [it.value_dict() for it in self.items]

        if self.on_conflict is not None:
            count = -1

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

            conflict_query = self.on_conflict.sql(get_count)
            values = [{**row, **conflict_query.params} for row in values]
            sql += f"\n{conflict_query.sql}"

        return QueryMany(sql, many_params=values)

__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/insert.py 
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
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.executemany(query)
        else:
            db = cast(DbBase, self._db)
            return db.executemany(query)

    return awaitable().__await__()

__init__(table, db, items)

Create a new InsertQueryReady instance.

Source code in src/embar/query/insert.py 
60
61
62
63
64
65
66
def __init__(self, table: type[T], db: Db, items: Sequence[T]):
    """
    Create a new InsertQueryReady instance.
    """
    self.table = table
    self._db = db
    self.items = items

run() 

run() -> None

run() -> InsertQueryReady[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/insert.py 
102
103
104
105
106
107
108
109
110
111
112
def run(self) -> InsertQueryReady[T, Db] | None:
    """
    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.executemany(query)
    return self

sql()

Create the SQL query and binding parameters (psycopg format) for the query.

from embar.column.common import Text
from embar.table import Table
from embar.query.insert import InsertQueryReady
class MyTable(Table):
    my_col: Text = Text()
row = MyTable(my_col="foo")
insert = InsertQueryReady(db=None, table=MyTable, items=[row])
query = insert.sql()
assert query.sql == 'INSERT INTO "my_table" ("my_col") VALUES (%(my_col)s)'
assert query.many_params == [{'my_col': 'foo'}]
Source code in src/embar/query/insert.py 
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
144
145
146
147
148
149
150
151
152
def sql(self) -> QueryMany:
    """
    Create the SQL query and binding parameters (psycopg format) for the query.

    ```python
    from embar.column.common import Text
    from embar.table import Table
    from embar.query.insert import InsertQueryReady
    class MyTable(Table):
        my_col: Text = Text()
    row = MyTable(my_col="foo")
    insert = InsertQueryReady(db=None, table=MyTable, items=[row])
    query = insert.sql()
    assert query.sql == 'INSERT INTO "my_table" ("my_col") VALUES (%(my_col)s)'
    assert query.many_params == [{'my_col': 'foo'}]
    ```
    """

    column_names = self.table.column_names().values()
    column_names_quoted = [f'"{c}"' for c in column_names]
    columns = ", ".join(column_names_quoted)
    placeholders = [f"%({name})s" for name in column_names]
    placeholder_str = ", ".join(placeholders)
    sql = f"INSERT INTO {self.table.fqn()} ({columns}) VALUES ({placeholder_str})"
    values = [it.value_dict() for it in self.items]

    if self.on_conflict is not None:
        count = -1

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

        conflict_query = self.on_conflict.sql(get_count)
        values = [{**row, **conflict_query.params} for row in values]
        sql += f"\n{conflict_query.sql}"

    return QueryMany(sql, many_params=values)

InsertQueryReturning

InsertQueryReturning is an insert query that will return what it inserts.

Source code in src/embar/query/insert.py 
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 InsertQueryReturning[T: Table, Db: AllDbBase]:
    """
    `InsertQueryReturning` is an insert query that will return what it inserts.
    """

    _db: Db
    table: type[T]
    items: Sequence[T]
    on_conflict: OnConflict | None

    def __init__(self, table: type[T], db: Db, items: Sequence[T], on_conflict: OnConflict | None):
        """
        Create a new InsertQueryReturning instance.
        """
        self.table = table
        self._db = db
        self.items = items
        self.on_conflict = on_conflict

    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: InsertQueryReturning[T, DbBase]) -> list[T]: ...
    @overload
    def run(self: InsertQueryReturning[T, AsyncDbBase]) -> InsertQueryReturning[T, Db]: ...

    def run(self) -> Sequence[T] | InsertQueryReturning[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) -> QueryMany:
        """
        Create the SQL query and binding parameters (psycopg format) for the query.
        """
        column_names = self.table.column_names().values()
        column_names_quoted = [f'"{c}"' for c in column_names]
        columns = ", ".join(column_names_quoted)
        placeholders = [f"%({name})s" for name in column_names]
        placeholder_str = ", ".join(placeholders)
        sql = f"INSERT INTO {self.table.fqn()} ({columns}) VALUES ({placeholder_str})"
        values = [it.value_dict() for it in self.items]

        if self.on_conflict is not None:
            count = -1

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

            conflict_query = self.on_conflict.sql(get_count)
            values = [{**row, **conflict_query.params} for row in values]
            sql += f"\n{conflict_query.sql}"

        sql += " RETURNING *"
        return QueryMany(sql, many_params=values)

    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/insert.py 
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
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, items, on_conflict)

Create a new InsertQueryReturning instance.

Source code in src/embar/query/insert.py 
165
166
167
168
169
170
171
172
def __init__(self, table: type[T], db: Db, items: Sequence[T], on_conflict: OnConflict | None):
    """
    Create a new InsertQueryReturning instance.
    """
    self.table = table
    self._db = db
    self.items = items
    self.on_conflict = on_conflict

run() 

run() -> list[T]

run() -> InsertQueryReturning[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/insert.py 
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
def run(self) -> Sequence[T] | InsertQueryReturning[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()

Create the SQL query and binding parameters (psycopg format) for the query.

Source code in src/embar/query/insert.py 
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) -> QueryMany:
    """
    Create the SQL query and binding parameters (psycopg format) for the query.
    """
    column_names = self.table.column_names().values()
    column_names_quoted = [f'"{c}"' for c in column_names]
    columns = ", ".join(column_names_quoted)
    placeholders = [f"%({name})s" for name in column_names]
    placeholder_str = ", ".join(placeholders)
    sql = f"INSERT INTO {self.table.fqn()} ({columns}) VALUES ({placeholder_str})"
    values = [it.value_dict() for it in self.items]

    if self.on_conflict is not None:
        count = -1

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

        conflict_query = self.on_conflict.sql(get_count)
        values = [{**row, **conflict_query.params} for row in values]
        sql += f"\n{conflict_query.sql}"

    sql += " RETURNING *"
    return QueryMany(sql, many_params=values)