Nullable columns don't seem to work

[danielgafni]

Describe the bug Nullable columns don't work with:

• String
• Integer
• Enum

Passing None with any of these types (I didn't check the others) and doing session.add(); session.commit() causes an error like:

Enum:

ValueError: None is not a valid Enum8

clickhouse_sqlalchemy.exceptions.DatabaseException: Orig exception: Code: 49. Unknown element 'None' for type Enum8('error' = 0, 'wrong_query' = 1)

Integer:

clickhouse_sqlalchemy.exceptions.DatabaseException: Orig exception: Code: 53. Type mismatch in VALUES section. Repeat query with types_check=True for detailed info. Column relevance: required argument is not an integer

To Reproduce A rough example:

import enum 
from datetime import timezone
from clickhouse_sqlalchemy.types import DateTime64, UUID, Date
from sqlalchemy import Column, Integer, String

@enum.unique
class LabelingError(enum.Enum):
    error = 0
    wrong_query = 1

class Serp(Base)
    __tablename__: str = "serps"

    created_at = Column(DateTime64(timezone=timezone.utc))  # TODO: fix server_default , server_default=func.now())  # this doesn't auto set the value with None
    updated_at = Column(
        DateTime64(timezone=timezone.utc), nullable=True
    )   # this doesn't work with None

    relevance = Column(Integer(), nullable=True)
    error = Column(sqlalchemy.Enum(LabelingError), nullable=True)

    __table_args__ = (
        engines.ReplicatedMergeTree(
            table_path=f"/tables/{{shard}}/search/{serps_table}",
            replica_name="{replica}",
            order_by="created_at",
            primary_key="created_at",
        ),
    )

Expected behavior This should work.

Versions

• clickhouse-sqlalchemy = 0.2.0
• Python = 3.8.12

[georgipeev]

You need to wrap your column types in clickhouse_sqlalchemy.types.Nullable like so:

    relevance = Column(Nullable(Integer), nullable=True)

The nullable parameter is confusing, but the docstring of the Column constructor explicitly states this (notice the last line):

        :param nullable: When set to ``False``, will cause the "NOT NULL"
            phrase to be added when generating DDL for the column.   When
            ``True``, will normally generate nothing (in SQL this defaults to
            "NULL"), except in some very specific backend-specific edge cases
            where "NULL" may render explicitly.
            Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
            is also ``True`` or the column specifies a :class:`_sql.Identity`,
            in which case it defaults to ``False``.
            This parameter is only used when issuing CREATE TABLE statements.

Also, per #201 , the nullable parameter value is currently being completely ignored and does not alter CREATE TABLE statements.

[franz101]

Curious how we can handle nullable inserts that have default values on the table for batch inserts. Tried ExampleTable.__table__.insert(), records....

Workaround is using add_all