coleifer / peewee

a small, expressive orm -- supports postgresql, mysql, sqlite and cockroachdb
http://docs.peewee-orm.com/
MIT License
11.17k stars 1.37k forks source link

Check Constraint not recognized in Pewee but in SQLITE3 #2917

Closed aadejare closed 3 months ago

aadejare commented 3 months ago

Running 3.17.0 of peewee I cannot access check constraints. For instance when I run this command in peeweee

("""CREATE TABLE GetnTable (
  tableid INTEGER PRIMARY KEY,
 coldata1 text CHECK ( coldata1 in ('A','B','C',)),
  );""")

I get no such column: coldata1

I then run in sqlite3 and it will work, but when using the libraries to access the data with peewee it does not.

class GetnTable(BaseModel):
    entrant1Characters = BlobField(column_name='entrant1Characters', null=True)
    coldata1 = TextField(column_name='coldata1', null=True,
    constraints=[Check("coldata1\\ in\\ \\('A','B','C'\\)")])
    tableid = AutoField(null=True)

    class Meta:
        database =db
        table_name = 'temperdata'

peewee.OperationalError: table coldata1 has no column named coldata1

I am using the SqliteExtDatabase

import os
import peewee
from peewee import *

from playhouse.sqlite_ext import SqliteExtDatabase
coleifer commented 3 months ago

It works just fine bro:

from peewee import *
from playhouse.sqlite_ext import *

db = SqliteExtDatabase(':memory:')

class Ex(db.Model):
    data = TextField(constraints=[Check("data in ('a', 'b', 'c')")])

db.create_tables([Ex])

Ex.create(data='a')
try:
    Ex.create(data='d')
except IntegrityError as exc:
    print('Received constraint error as expected: %s' % exc)

Output:

Received constraint error as expected: CHECK constraint failed: data in ('a', 'b', 'c')
aadejare commented 3 months ago

This did not address the issue. To go further, I run the insert data in a query by first checking if the query exists with the traceback

  File "<stdin>", line 1, in <module>
  File "...file", line 54, in safedata
    cond1data.save()
  File "/Users/uxer_xxx/anaconda3/envs/dev/lib/python3.11/site-packages/peewee.py", line 6951, in save
    pk = self.insert(**field_dict).execute()
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/uxer_xxx/anaconda3/envs/dev/lib/python3.11/site-packages/peewee.py", line 2036, in inner
    return method(self, database, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/uxer_xxx/anaconda3/envs/dev/lib/python3.11/site-packages/peewee.py", line 2107, in execute
    return self._execute(database)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/uxer_xxx/anaconda3/envs/dev/lib/python3.11/site-packages/peewee.py", line 2912, in _execute
    return super(Insert, self)._execute(database)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/uxer_xxx/anaconda3/envs/dev/lib/python3.11/site-packages/peewee.py", line 2625, in _execute
    cursor = database.execute(self)
             ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/uxer_xxx/anaconda3/envs/dev/lib/python3.11/site-packages/peewee.py", line 3330, in execute
    return self.execute_sql(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/uxer_xxx/anaconda3/envs/dev/lib/python3.11/site-packages/peewee.py", line 3320, in execute_sql
    with __exception_wrapper__:
  File "/Users/uxer_xxx/anaconda3/envs/dev/lib/python3.11/site-packages/peewee.py", line 3088, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/Users/uxer_xxx/anaconda3/envs/dev/lib/python3.11/site-packages/peewee.py", line 196, in reraise
    raise value.with_traceback(tb)
  File "/Users/uxer_xxx/anaconda3/envs/dev/lib/python3.11/site-packages/peewee.py", line 3322, in execute_sql
    cursor.execute(sql, params or ())

    peewee.OperationalError: table GetnTable has no column named coldata1
coleifer commented 3 months ago

I demonstrated that check constraints work perfectly fine. Whatever issue you’re having is particular to your code.

aadejare commented 3 months ago

Went back and checked, the issue was that the check constraint had a space before the keyword.

Old code: coldata1 text CHECK ( coldata1 in ('A','B','C',)), New code: coldata1 text CHECK (coldata1 in ('A','B','C',)),