sqlc-dev / sqlc-gen-python Goto Github PK

Ruff has become the go-to linter and auto-formatter.

It would be great if the generate code was automatically formatted by ruff. Right now, internally we use a script to

1. Run sqlc generate
2. Run ruff on the output directory

Happy to contribute if this makes sense

I would like to add mysql as a supported target to give type hints other than Any to the generated models.

The following is an example with a pydantic model.

No mysql support:

# Code generated by sqlc. DO NOT EDIT.
# versions:
#   sqlc v1.20.0
import pydantic
from typing import Optional


class Author(pydantic.BaseModel):
    id: Any
    name: Any
    bio: Optional[Any]

If support mysql !

# Code generated by sqlc. DO NOT EDIT.
# versions:
#   sqlc v1.20.0
import pydantic
from typing import Optional


class Author(pydantic.BaseModel):
    id: int
    name: str
    bio: Optional[str]

UPDATE api_keys
...
WHERE key = ? OR key = ?

->

def update_api_key_last_used(self, *, key: Any, key: Any) -> None:
    self._conn.execute(sqlalchemy.text(UPDATE_API_KEY_LAST_USED), {"p1": key, "p2": key})

As you can see key is used twice as the variable name which will obviously not work.

Config:

version: '2'
plugins:
  - name: py
    wasm:
      url: https://downloads.sqlc.dev/plugin/sqlc-gen-python_1.2.0.wasm
      sha256: a6c5d174c407007c3717eea36ff0882744346e6ba991f92f71d6ab2895204c0e
sql:
  - engine: "sqlite"
    queries: "...."
    schema: "..."
    codegen:
      - out: "..."
        plugin: py
        options:
          emit_sync_querier: True
          emit_async_querier: True
          package: "database"

I ran into a problem after upgrading from sqlc-gen-python_1.0.0.wasm to sqlc-gen-python_1.1.0.wasm on a Mac with Apple Silicon.

When I try to run sqlc using the plugin with 1.1.0, the process is killed.
Console output:

default 14:57:39.947500-0400 kernel CODE SIGNING: process 18673[sqlc]: rejecting invalid page at address 0x12e498000 from offset 0x4000 in file "" (cs_mtime:0.0 == mtime:0.0) (signed:0 validated:0 tainted:0 nx:0 wpmapped:1 dirty:0 depth:0)

I checked the signature, and I thought it looked fine:

codesign -dvvv --deep sqlc
Executable=/private/var/tmp/_bazel_bretwalker/5f1e154eeb3037a5f000bde1a5a0b737/external/sqlc_release/sqlc
Identifier=sqlc
Format=Mach-O thin (arm64)
CodeDirectory v=20500 size=581360 flags=0x10000(runtime) hashes=18162+2 location=embedded
Hash type=sha256 size=32
CandidateCDHash sha256=ecc37620dc0926c725d806a625f1a5940e090d6d
CandidateCDHashFull sha256=ecc37620dc0926c725d806a625f1a5940e090d6dac7b613ba3cfce13eec15ffc
Hash choices=sha256
CMSDigest=ecc37620dc0926c725d806a625f1a5940e090d6dac7b613ba3cfce13eec15ffc
CMSDigestType=2
Launch Constraints:
	None
CDHash=ecc37620dc0926c725d806a625f1a5940e090d6d
Signature size=8972
Authority=Developer ID Application: Kyle Conroy (88ZP47B2C5)
Authority=Developer ID Certification Authority
Authority=Apple Root CA
Timestamp=Jul 31, 2023 at 4:06:00 PM
Info.plist=not bound
TeamIdentifier=88ZP47B2C5
Runtime Version=13.3.0
Sealed Resources=none
Internal requirements count=1 size=164

But after ad hoc signing, the process was no longer killed:

sudo codesign -f -s - sqlc

When defining an insert statement with a returning, using :one, the generated return type of the method has | None attached to it. AFAIK, the only possibilities are to get a result or to have an exception when inserting a statement, and presumably the | None is added automatically as it's how :one is normally generated. Is it possible to generate a different return type for :one when it is an INSERT statement?

Version

1.10.0

What happened?

Python output looks good!

Minor issue.

I had a column name named class. This generated a python dataclass that did not escape the reserved word. Not sure if the solution is to

* not name things "class" (my workaround)

* quote class in the dataclass (like type hinting can do in python)

* warn user and reject reserved words as column names/dataclass field names

(I have seen this in mysql with type as column name too.)

Relevant log output

No response

Database schema

No response

SQL queries

No response

Configuration

No response

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Python

Context

Given the following SQL schema

CREATE TYPE public.state AS ENUM (
    'CREATED',
    'RUNNING'
);

CREATE TABLE public.member (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    states public.state[] DEFAULT ARRAY['CREATED'::public.state] NOT NULL
);

Queries:

-- name: GetMember :one
SELECT * FROM member WHERE id = $1;

sqlc-gen-python generates the following model:

class State(str, enum.Enum):
    CREATED = "CREATED"
    RUNNING = "RUNNING"


@dataclasses.dataclass()
class Member:
    id: uuid.UUID
    states: List[State]

And the following query:

GET_MEMBER = """-- name: get_member \\:one
SELECT id, states FROM member WHERE id = :p1
"""

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

    def get_member(self, *, member_id: uuid.UUID) -> Optional[models.Member]:
        row = self._conn.execute(sqlalchemy.text(GET_MEMBER), {"p1": member_id}).first()
        if row is None:
            return None
        return models.Member(
            id=row[0],
            states=row[1],
        )

Issue

When using the get_member() method, the states property doesn't have the expected type.
It should be a List[State] but the type is a string of the following format: {CREATED,RUNNING}.

member = queries.get_member(some_uuid)

# the following line is not expected.
type(member.states) # <class 'str'>

Workaround

Our current workaround is to manually parse the string but this would be better handled by SQLAlchemy / SQLC.

It seems that SQLAlchemy supports array types when using the ORM interface:
https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ARRAY
But I'm not sure how to use this with the execute method.

For reference, the List type is generated by sqlc-gen-python:

Haven't had a lot of time to investigate.

SQL and connection script working before update. Updated config and output

from sqlc_runtime.psycopg2 import build_psycopg2_connection
connection_string = ""
conn =  build_psycopg2_connection(psycopg2.connect(connection_string))

SQL

-- name: SaveMessage :execresult
INSERT INTO message (
    uuid,
    userid,
    content
) VALUES (
    $1,
    $2,
    $3
) ;

Generated looks fine

    def save_message(
        self,
        *,
        uuid: str,
        userid: Optional[int],
        content: Optional[str],
    ) -> sqlalchemy.engine.Result:
        return self._conn.execute(
            sqlalchemy.text(SAVE_MESSAGE),
            {
                "p1": uuid,
                "p2": userid, 
                "p3": content 
            },
        )

Produces this error

TypeError: expected string or bytes-like object, got 'TextClause'

The old connection string using sqlalchemy failed with connection error, maybe I also need to downgrade?
https://levelup.gitconnected.com/how-to-fix-attributeerror-optionengine-object-has-no-attribute-execute-in-pandas-eb635fbb89e4

Version

1.10.0

What happened?

I am using MariaDB - maybe MySQL does not support ? syntax.

Relevant log output

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?,\n    ?\n)' at line 11")

Database schema

No response

SQL queries

CREATE_TRAINING_ENTRY = """ 
INSERT INTO training (
    messageuuid,
    originalclass
) VALUES (
    ?,
    ?
)
"""

    def create_training_entry(self, arg: CreateTrainingEntryParams) -> None:
        self._conn.execute(
            sqlalchemy.text(CREATE_TRAINING_ENTRY),
            {
                "p1": arg.messageuuid,
                "p2": arg.originalclass,
            },
        )

Configuration

No response

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Python

Since I am using mysql, I would like to generate code that accepts multiple values using sqlc.slice.

Example

schema & query

create table students (
  id int not null auto_increment,
  name varchar(255) not null,
  age int not null
);

-- name: SelectStudents :many
SELECT * FROM students 
WHERE age IN (sqlc.slice("ages"));

current results:

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

    def select_students(self, *, ages: int) -> Iterator[models.Student]:
        result = self._conn.execute(sqlalchemy.text(SELECT_STUDENTS), {"p1": ages})
        for row in result:
            yield models.Student(
                id=row[0],
                name=row[1],
                age=row[2],
            )

i want:

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

    def select_students(self, *, ages: list[int]) -> Iterator[models.Student]:
         # some kind of processing

Version

1.15.0

What happened?

Version is 1.16.

When creating an enum, and sql statements, the output of python adds double quotes which fails to execute.

Example

CREATE TYPE contact_type AS ENUM ('email', 'phone);

CREATE TABLE IF NOT EXISTS  identity (
    ID BIGSERIAL PRIMARY KEY,
    Username VARCHAR(128),
    ContactType contact_type
);

SELECT i.Username, i.ID
    FROM identity as i
    WHERE i.ContactType = 'email';

The above changes single quotes to double quotes, which fails to execute.

Relevant log output

No response

Database schema

No response

SQL queries

No response

Configuration

No response

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Python

Originally reported here: sqlc-dev/sqlc#2007