Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Add :copyfrom support#92

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Open
kellen-miller wants to merge3 commits intosqlc-dev:main
base:main
Choose a base branch
Loading
fromkellen-miller:add-copyfrom
Open
Show file tree
Hide file tree
Changes fromall commits
Commits
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 2 additions & 0 deletions.gitignore
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -5,3 +5,5 @@ bin
.direnv
.devenv*
devenv.local.nix

.idea/
46 changes: 46 additions & 0 deletionsREADME.md
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -76,3 +76,49 @@ class Status(str, enum.Enum):
OPEN = "op!en"
CLOSED = "clo@sed"
```

### Bulk Inserts with `:copyfrom`

Use the `:copyfrom` command to generate batch insert methods that leverage SQLAlchemy’s executemany behavior via `Connection.execute()` with a list of parameter mappings.

SQL (example):

```sql
-- name: CreateUsersBatch :copyfrom
INSERT INTO users (email, name) VALUES ($1, $2);
```

Generated methods:

```py
def create_users_batch(self, arg_list: List[Any]) -> int
async def create_users_batch(self, arg_list: List[Any]) -> int
```

Call with a list of dicts using positional parameter keys `p1..pN` (the generator converts `$1`/`@name` to `:pN`):

```py
rows = [
{"p1": "a@example.com", "p2": "Alice"},
{"p1": "b@example.com", "p2": "Bob"},
]
count = queries.create_users_batch(rows) # returns affected rowcount (int)
```

When a typed params struct is emitted (e.g., many parameters or config thresholds), the method accepts `List[<QueryName>Params]`. The generator converts items to dicts internally:

```py
@dataclasses.dataclass()
class CreateUsersWithDetailsParams:
email: str
name: str
bio: Optional[str]
age: Optional[int]
active: Optional[bool]

count = queries.create_users_with_details([
CreateUsersWithDetailsParams("a@example.com", "Alice", None, None, True),
])
```

Implementation note: sync and async use `conn.execute(sqlalchemy.text(SQL), list_of_dicts)` and `await async_conn.execute(...)` respectively; SQLAlchemy performs efficient batch inserts under the hood.
2 changes: 1 addition & 1 deletionexamples/src/authors/models.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
# Code generated by sqlc. DO NOT EDIT.
# versions:
# sqlc v1.28.0
# sqlc v1.29.0
import dataclasses
from typing import Optional

Expand Down
17 changes: 15 additions & 2 deletionsexamples/src/authors/query.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,8 @@
# Code generated by sqlc. DO NOT EDIT.
# versions:
# sqlc v1.28.0
# sqlc v1.29.0
# source: query.sql
from typing import AsyncIterator, Iterator, Optional
from typing importAny,AsyncIterator, Iterator, List, Optional

import sqlalchemy
import sqlalchemy.ext.asyncio
Expand All@@ -20,6 +20,11 @@
"""


CREATE_AUTHORS_BATCH = """-- name: create_authors_batch \\:copyfrom
INSERT INTO authors (name, bio) VALUES (:p1, :p2)
"""


DELETE_AUTHOR = """-- name: delete_author \\:exec
DELETE FROM authors
WHERE id = :p1
Expand DownExpand Up@@ -52,6 +57,10 @@ def create_author(self, *, name: str, bio: Optional[str]) -> Optional[models.Aut
bio=row[2],
)

def create_authors_batch(self, arg_list: List[Any]) -> int:
result = self._conn.execute(sqlalchemy.text(CREATE_AUTHORS_BATCH), arg_list)
return result.rowcount

def delete_author(self, *, id: int) -> None:
self._conn.execute(sqlalchemy.text(DELETE_AUTHOR), {"p1": id})

Expand DownExpand Up@@ -89,6 +98,10 @@ async def create_author(self, *, name: str, bio: Optional[str]) -> Optional[mode
bio=row[2],
)

async def create_authors_batch(self, arg_list: List[Any]) -> int:
result = await self._conn.execute(sqlalchemy.text(CREATE_AUTHORS_BATCH), arg_list)
return result.rowcount

async def delete_author(self, *, id: int) -> None:
await self._conn.execute(sqlalchemy.text(DELETE_AUTHOR), {"p1": id})

Expand Down
3 changes: 3 additions & 0 deletionsexamples/src/authors/query.sql
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -17,3 +17,6 @@ RETURNING *;
-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;

-- name: CreateAuthorsBatch :copyfrom
INSERT INTO authors (name, bio) VALUES ($1, $2);
2 changes: 1 addition & 1 deletionexamples/src/booktest/models.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
# Code generated by sqlc. DO NOT EDIT.
# versions:
# sqlc v1.28.0
# sqlc v1.29.0
import dataclasses
import datetime
import enum
Expand Down
2 changes: 1 addition & 1 deletionexamples/src/booktest/query.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
# Code generated by sqlc. DO NOT EDIT.
# versions:
# sqlc v1.28.0
# sqlc v1.29.0
# source: query.sql
import dataclasses
import datetime
Expand Down
2 changes: 1 addition & 1 deletionexamples/src/jets/models.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
# Code generated by sqlc. DO NOT EDIT.
# versions:
# sqlc v1.28.0
# sqlc v1.29.0
import dataclasses


Expand Down
2 changes: 1 addition & 1 deletionexamples/src/jets/query-building.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
# Code generated by sqlc. DO NOT EDIT.
# versions:
# sqlc v1.28.0
# sqlc v1.29.0
# source: query-building.sql
from typing import AsyncIterator, Optional

Expand Down
2 changes: 1 addition & 1 deletionexamples/src/ondeck/city.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
# Code generated by sqlc. DO NOT EDIT.
# versions:
# sqlc v1.28.0
# sqlc v1.29.0
# source: city.sql
fromtypingimportAsyncIterator,Optional

Expand Down
2 changes: 1 addition & 1 deletionexamples/src/ondeck/models.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
# Code generated by sqlc. DO NOT EDIT.
# versions:
# sqlc v1.28.0
# sqlc v1.29.0
import dataclasses
import datetime
import enum
Expand Down
2 changes: 1 addition & 1 deletionexamples/src/ondeck/venue.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
# Code generated by sqlc. DO NOT EDIT.
# versions:
# sqlc v1.28.0
# sqlc v1.29.0
# source: venue.sql
import dataclasses
from typing import AsyncIterator, List, Optional
Expand Down
26 changes: 26 additions & 0 deletionsexamples/src/tests/test_authors.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -29,6 +29,18 @@ def test_authors(db: sqlalchemy.engine.Connection):
assert len(author_list) == 1
assert author_list[0] == new_author

# Test batch insert with copyfrom
batch_authors = [
{"p1": "Dennis Ritchie", "p2": "Creator of C Programming Language"},
{"p1": "Ken Thompson", "p2": "Creator of Unix and Go Programming Language"},
{"p1": "Rob Pike", "p2": "Co-creator of Go Programming Language"},
]
rows_affected = querier.create_authors_batch(batch_authors)
assert rows_affected == 3

all_authors = list(querier.list_authors())
assert len(all_authors) == 4 # 1 existing + 3 batch inserted


@pytest.mark.asyncio
async def test_authors_async(async_db: sqlalchemy.ext.asyncio.AsyncConnection):
Expand All@@ -54,3 +66,17 @@ async def test_authors_async(async_db: sqlalchemy.ext.asyncio.AsyncConnection):
author_list.append(author)
assert len(author_list) == 1
assert author_list[0] == new_author

# Test batch insert with copyfrom
batch_authors = [
{"p1": "Dennis Ritchie", "p2": "Creator of C Programming Language"},
{"p1": "Ken Thompson", "p2": "Creator of Unix and Go Programming Language"},
{"p1": "Rob Pike", "p2": "Co-creator of Go Programming Language"},
]
rows_affected = await querier.create_authors_batch(batch_authors)
assert rows_affected == 3

all_authors = []
async for author in querier.list_authors():
all_authors.append(author)
assert len(all_authors) == 4 # 1 existing + 3 batch inserted
24 changes: 24 additions & 0 deletionsinternal/endtoend/testdata/copyfrom/python/models.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
# Code generated by sqlc. DO NOT EDIT.
# versions:
# sqlc v1.29.0
import dataclasses
import datetime
from typing import Optional


@dataclasses.dataclass()
class Author:
id: int
name: str
bio: str


@dataclasses.dataclass()
class User:
id: int
email: str
name: str
bio: Optional[str]
age: Optional[int]
active: Optional[bool]
created_at: datetime.datetime
158 changes: 158 additions & 0 deletionsinternal/endtoend/testdata/copyfrom/python/query.py
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,158 @@
# Code generated by sqlc. DO NOT EDIT.
# versions:
# sqlc v1.29.0
# source: query.sql
import dataclasses
from typing import Any, List, Optional

import sqlalchemy
import sqlalchemy.ext.asyncio

from copyfrom import models


CREATE_AUTHOR = """-- name: create_author \\:one
INSERT INTO authors (name, bio) VALUES (:p1, :p2) RETURNING id, name, bio
"""


CREATE_AUTHORS = """-- name: create_authors \\:copyfrom
INSERT INTO authors (name, bio) VALUES (:p1, :p2)
"""


CREATE_AUTHORS_NAMED = """-- name: create_authors_named \\:copyfrom
INSERT INTO authors (name, bio) VALUES (:p1, :p2)
"""


CREATE_USER = """-- name: create_user \\:one
INSERT INTO users (email, name) VALUES (:p1, :p2) RETURNING id, email, name, bio, age, active, created_at
"""


CREATE_USERS_BATCH = """-- name: create_users_batch \\:copyfrom
INSERT INTO users (email, name) VALUES (:p1, :p2)
"""


CREATE_USERS_WITH_DETAILS = """-- name: create_users_with_details \\:copyfrom
INSERT INTO users (email, name, bio, age, active) VALUES (:p1, :p2, :p3, :p4, :p5)
"""


@dataclasses.dataclass()
class CreateUsersWithDetailsParams:
email: str
name: str
bio: Optional[str]
age: Optional[int]
active: Optional[bool]


class Querier:
def __init__(self, conn: sqlalchemy.engine.Connection):
self._conn = conn

def create_author(self, *, name: str, bio: str) -> Optional[models.Author]:
row = self._conn.execute(sqlalchemy.text(CREATE_AUTHOR), {"p1": name, "p2": bio}).first()
if row is None:
return None
return models.Author(
id=row[0],
name=row[1],
bio=row[2],
)

def create_authors(self, arg_list: List[Any]) -> int:
result = self._conn.execute(sqlalchemy.text(CREATE_AUTHORS), arg_list)
return result.rowcount

def create_authors_named(self, arg_list: List[Any]) -> int:
result = self._conn.execute(sqlalchemy.text(CREATE_AUTHORS_NAMED), arg_list)
return result.rowcount

def create_user(self, *, email: str, name: str) -> Optional[models.User]:
row = self._conn.execute(sqlalchemy.text(CREATE_USER), {"p1": email, "p2": name}).first()
if row is None:
return None
return models.User(
id=row[0],
email=row[1],
name=row[2],
bio=row[3],
age=row[4],
active=row[5],
created_at=row[6],
)

def create_users_batch(self, arg_list: List[Any]) -> int:
result = self._conn.execute(sqlalchemy.text(CREATE_USERS_BATCH), arg_list)
return result.rowcount

def create_users_with_details(self, arg_list: List[CreateUsersWithDetailsParams]) -> int:
data = list()
for item in arg_list:
data.append({
"p1": item.email,
"p2": item.name,
"p3": item.bio,
"p4": item.age,
"p5": item.active,
})
result = self._conn.execute(sqlalchemy.text(CREATE_USERS_WITH_DETAILS), data)
return result.rowcount


class AsyncQuerier:
def __init__(self, conn: sqlalchemy.ext.asyncio.AsyncConnection):
self._conn = conn

async def create_author(self, *, name: str, bio: str) -> Optional[models.Author]:
row = (await self._conn.execute(sqlalchemy.text(CREATE_AUTHOR), {"p1": name, "p2": bio})).first()
if row is None:
return None
return models.Author(
id=row[0],
name=row[1],
bio=row[2],
)

async def create_authors(self, arg_list: List[Any]) -> int:
result = await self._conn.execute(sqlalchemy.text(CREATE_AUTHORS), arg_list)
return result.rowcount

async def create_authors_named(self, arg_list: List[Any]) -> int:
result = await self._conn.execute(sqlalchemy.text(CREATE_AUTHORS_NAMED), arg_list)
return result.rowcount

async def create_user(self, *, email: str, name: str) -> Optional[models.User]:
row = (await self._conn.execute(sqlalchemy.text(CREATE_USER), {"p1": email, "p2": name})).first()
if row is None:
return None
return models.User(
id=row[0],
email=row[1],
name=row[2],
bio=row[3],
age=row[4],
active=row[5],
created_at=row[6],
)

async def create_users_batch(self, arg_list: List[Any]) -> int:
result = await self._conn.execute(sqlalchemy.text(CREATE_USERS_BATCH), arg_list)
return result.rowcount

async def create_users_with_details(self, arg_list: List[CreateUsersWithDetailsParams]) -> int:
data = list()
for item in arg_list:
data.append({
"p1": item.email,
"p2": item.name,
"p3": item.bio,
"p4": item.age,
"p5": item.active,
})
result = await self._conn.execute(sqlalchemy.text(CREATE_USERS_WITH_DETAILS), data)
return result.rowcount
Loading
[8]ページ先頭
©2009-2025 Movatter.jp