Skip to content
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 our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Insert int data which is out of datatype limit can be inserted successfully, without data check #270

Open
flyly0755 opened this issue Oct 28, 2023 · 0 comments

Comments

@flyly0755
Copy link

flyly0755 commented Oct 28, 2023

Describe the bug

from sqlalchemy import Column, create_engine
from clickhouse_sqlalchemy import engines, types
from clickhouse_sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import insert as sainsert

ChBase = declarative_base()

class Uint16Table(ChBase):
    id = Column(types.UInt16, primary_key=True)
    intvalue = Column(types.UInt16)

    __tablename__ = 'Uint16Table'
    __table_args__ = (
        engines.MergeTree(order_by=('id',),
                          primary_key=('id',)),
        {'comment': 'Uint16 Table'}
    )

#clickhouse machine info
ckuser = "xxx"
ckpwd = "xxx"
ckip = "xxx"
ckport = "xxx"
ckdbname = 'xxx'

uri = f"clickhouse://{ckuser}:{ckpwd}@" \
      f"{ckip}:{ckport}/{ckdbname}"
engine = create_engine(uri, echo=False)
DBsession = sessionmaker(bind=engine)
session = DBsession()
print(session)
session.execute('SELECT 1')
# Uint16Table.__table__.create(engine)
# session.close()

datalist = [{'id': 1, 'intvalue': 65534},
            {'id': 2, 'intvalue': 65535},
            {'id': 3, 'intvalue': 65536},  # 65536 is bigger than uint16 upper limit(65535), after insert, the value in database is 0
            {'id': 4, 'intvalue': 65537}   # 65537 is bigger than uint16 upper limit(65535), after insert, the value in database is 1
            ]
# session.bulk_insert_mappings(Uint16Table, datalist)
session.execute(sainsert(Uint16Table), datalist)
session.commit()
session.close()

With code as above, use bulk_insert_mappings or execute method both can run successfully, although store value is wrong(65536 stored as 0, 65537 as 1),
Compare with postgres database, which will raise error:

sqlalchemy.exc.DataError: (psycopg2.errors.NumericValueOutOfRange) smallint out of range

Seems like clickhouse-sqlalchemy doesn't check the value

To Reproduce
code as above

Expected behavior
check the value fisrt, if value out of range, will raise error.

Versions
clickhouse-sqlalchemy==0.2.3
SQLAlchemy==1.4.8

  • Version of package with the problem.
  • Python version.
    python 3.11
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant