Primary key field not being added to any queries #143

nexy7574 · 2021-12-20T14:38:23Z

Checklist

The bug is reproducible against the latest release and/or master.
There are no similar issues or pull requests to fix it yet.

Describe the bug

Despite the column id (the table's primary key) being included in queries explicitly via the name (id=), and via implicit (pk=), the same result is still yielded.
I don't know what could be causing this as I've been using encode/orm since 0.2 and its worked fine, up until this one god forsaken project.

To reproduce

I'm honestly not too sure what's causing this so im going to provide my table, the queries I'm making, and some example data

# Define the table
import datetime
import orm
from databases import Database

registry = orm.ModelRegistry(Database("sqlite:///main.db"))


class User(orm.Model):
    tablename = "users"
    registry = registry
    fields = {
        "id": orm.BigInteger(primary_key=True),
        "access_token": orm.Text(default=None),
        "refresh_token": orm.Text(default=None),
        "expires": orm.DateTime(default=None),
        "session": orm.Text(default=None),
        "bio": orm.String(default="Default bio", min_length=2, max_length=4069),
    }

# elsewhere in an async function:
async def main():  # not production code, I'm running this in a fastapi server - but that's probably irrelivant 
    data = {
        "id": 421698654180012064,
        "access_token": "aoigniawghaoiwhgnagwuwgnuip",
        "refresh_token": "aiwngfuagnpauiwaiwbguiwgbuiawg",
        "expires": datetime.datetime.now() + datetime.timedelta(days=7),
        "session": "hello world"
    }
    await User.objects.create(**data)  # , pk=data["id"]) also raises the same error
    # I also tried with the below code, and it yielded a stranger error, but pretty much the same
    """
    await User.objects.update_or_create(id=data["id"], defaults=data)  # specifying pk still does nothing
    """

Expected behavior

An entry is correctly created

Actual behavior

Sometimes (unclear why) the id field autoincrements itself(???), or (more often than not) the code errors out with a not null constraint failure

Debugging material

Notable tracebacks:

Traceback from console

Authorized user:
{
    'id': '123456788989898989898',
    'username': '...',
    'avatar': 'redacted',
    'discriminator': '0000',
    'public_flags': 64,
    'flags': 64,
    'banner': None,
    'banner_color': '#36393f',
    'accent_color': 3553599,
    'locale': 'en-GB',
    'mfa_enabled': True,
    'email': 'redacted@gmail.com',
    'verified': True
}
Authorized user's auth data:
{'access_token': 'awdadaawdawdawd', 'token_type': 'Bearer', 'expires_in': 604800, 'refresh_token': 'dawsdwadswasd', 'scope': 'identify email guilds'}
INFO:     127.0.0.1:44550 - "GET /callbacks/authorise?code=redacted HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/uvicorn/protocols/http/httptools_impl.py", line 376, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
    return await self.app(scope, receive, send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/uvicorn/middleware/debug.py", line 96, in __call__
    raise exc from None
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/uvicorn/middleware/debug.py", line 93, in __call__
    await self.app(scope, receive, inner_send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/fastapi/applications.py", line 208, in __call__
    await super().__call__(scope, receive, send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/routing.py", line 656, in __call__
    await route.handle(scope, receive, send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/routing.py", line 259, in handle
    await self.app(scope, receive, send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/routing.py", line 61, in app
    response = await func(request)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/fastapi/routing.py", line 226, in app
    raw_response = await run_endpoint_function(
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/fastapi/routing.py", line 159, in run_endpoint_function
    return await dependant.call(**values)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/server/src/main.py", line 55, in authorise_user
    user_db = await User.objects.create(
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/orm/models.py", line 412, in create
    instance.pk = await self.database.execute(expr)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/databases/core.py", line 169, in execute
    return await connection.execute(query, values)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/databases/core.py", line 295, in execute
    return await self._connection.execute(built_query)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/databases/backends/sqlite.py", line 128, in execute
    await cursor.execute(query_str, args)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/aiosqlite/cursor.py", line 37, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/aiosqlite/cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
sqlite3.IntegrityError: NOT NULL constraint failed: users.id

From debug logs (via `logging` module):

DEBUG:databases:Query: INSERT INTO users (access_token, refresh_token, expires, session, bio) VALUES (?, ?, ?, ?, ?) Args: ('redacted (access token)', 'redacted (refresh token)', '2021-12-27 14:32:28.747869', 'session token', 'Default bio. Go to user settings -> bio to change.')
DEBUG:aiosqlite:executing functools.partial(<built-in method cursor of sqlite3.Connection object at 0x7f4984848b70>)
DEBUG:aiosqlite:operation functools.partial(<built-in method cursor of sqlite3.Connection object at 0x7f4984848b70>) completed
DEBUG:aiosqlite:executing functools.partial(<built-in method execute of sqlite3.Cursor object at 0x7f49850cbab0>, 'INSERT INTO users (access_token, refresh_token, expires, session, bio) VALUES (?, ?, ?, ?, ?)', ['access token', 'refresh token', '2021-12-27 14:32:28.747869', 'session token', 'Default bio. Go to user settings -> bio to change.'])
DEBUG:aiosqlite:returning exception NOT NULL constraint failed: users.id

Traceback from web debugger

500 Server Error
Traceback (most recent call last):
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/routing.py", line 656, in __call__
    await route.handle(scope, receive, send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/routing.py", line 259, in handle
    await self.app(scope, receive, send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/routing.py", line 61, in app
    response = await func(request)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/fastapi/routing.py", line 226, in app
    raw_response = await run_endpoint_function(
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/fastapi/routing.py", line 159, in run_endpoint_function
    return await dependant.call(**values)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/server/src/main.py", line 55, in authorise_user
    user_db = await User.objects.create(
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/orm/models.py", line 412, in create
    instance.pk = await self.database.execute(expr)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/databases/core.py", line 169, in execute
    return await connection.execute(query, values)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/databases/core.py", line 295, in execute
    return await self._connection.execute(built_query)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/databases/backends/sqlite.py", line 128, in execute
    await cursor.execute(query_str, args)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/aiosqlite/cursor.py", line 37, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/aiosqlite/cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
sqlite3.IntegrityError: NOT NULL constraint failed: users.id

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/uvicorn/middleware/debug.py", line 93, in __call__
    await self.app(scope, receive, inner_send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/fastapi/applications.py", line 208, in __call__
    await super().__call__(scope, receive, send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 165, in __call__
    response = self.debug_response(request, exc)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 245, in debug_response
    content = self.generate_html(exc)
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 217, in generate_html
    traceback_obj = traceback.TracebackException.from_exception(
  File "/home/nexus/.pyenv/versions/3.9.7/lib/python3.9/traceback.py", line 538, in from_exception
    return cls(type(exc), exc, exc.__traceback__, *args, **kwargs)
  File "/home/nexus/.pyenv/versions/3.9.7/lib/python3.9/traceback.py", line 517, in __init__
    self.stack = StackSummary.extract(
  File "/home/nexus/.pyenv/versions/3.9.7/lib/python3.9/traceback.py", line 359, in extract
    result.append(FrameSummary(
  File "/home/nexus/.pyenv/versions/3.9.7/lib/python3.9/traceback.py", line 260, in __init__
    self.locals = {k: repr(v) for k, v in locals.items()} if locals else None
  File "/home/nexus/.pyenv/versions/3.9.7/lib/python3.9/traceback.py", line 260, in <dictcomp>
    self.locals = {k: repr(v) for k, v in locals.items()} if locals else None
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/orm/models.py", line 493, in __repr__
    return f"<{self.__class__.__name__}: {self}>"
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/orm/models.py", line 496, in __str__
    return f"{self.__class__.__name__}({self.pkname}={self.pk})"
  File "/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/orm/models.py", line 486, in pk
    return getattr(self, self.pkname)
AttributeError: 'User' object has no attribute 'id'

Environment

OS: Manjaro (Linux (me)-laptop 5.14.21-2-MANJARO #1 SMP PREEMPT Sun Nov 21 22:43:47 UTC 2021 x86_64 GNU/Linux)
Python version: Python 3.9.7, no optimisations, installed via pyenv
ORM version: master commit (0.3.1)

The text was updated successfully, but these errors were encountered:

aminalaee · 2021-12-20T14:57:48Z

Just to be clear, what's the schema of the table? How do you create the table?

nexy7574 · 2021-12-20T15:44:11Z

Just to be clear, what's the schema of the table? How do you create the table?

await registry.create_all()

SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
sqlite> .d
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users (
        id BIGINT NOT NULL, 
        access_token TEXT NOT NULL, 
        refresh_token TEXT NOT NULL, 
        expires DATETIME NOT NULL, 
        session TEXT NOT NULL, 
        bio VARCHAR(4069) NOT NULL, 
        PRIMARY KEY (id)
);
COMMIT;
sqlite>

aminalaee · 2021-12-20T16:24:46Z

Thanks for the input.

Did you use BIGINT in previous versions too? I'm not sure if SQLite allows BIGINT as primary key.

nexy7574 · 2021-12-20T16:54:25Z

Yep. INT also causes the same issue. I also think I had the same issue when it was text, so I don't think the column type matters

nexy7574 · 2021-12-20T16:58:38Z

I just changed the id column to orm.Text(...), and now I get this warning in the console when trying to write a value to it:

/home/nexus/PycharmProjects/Unnamed-bot-List/venv/lib/python3.9/site-packages/databases/backends/sqlite.py:160: SAWarning: Column 'users.id' is marked as a member of the primary key for table 'users', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed.  Primary key columns typically may not store NULL.

nexy7574 · 2021-12-20T17:02:54Z

sqlite> PRAGMA main.table_info("users");
0|id|TEXT|1||1
1|access_token|TEXT|1||0
2|refresh_token|TEXT|1||0
3|expires|DATETIME|1||0
4|session|TEXT|1||0
5|bio|VARCHAR(4069)|1||0
sqlite> INSERT INTO users (id, access_token, refresh_token, expires, session, bio) VALUES (421698654189912064, "foobar", "barfoo", "2021-12-20T17:01:56.066857", "hello world", "cool bio");
sqlite> SELECT * FROM users;
421698654189912064|foobar|barfoo|2021-12-20T17:01:56.066857|hello world|cool bio

Doesn't look like its an issue with the generated schema

with BIGINT as pk type (no effect)

sqlite> PRAGMA main.table_info("users");
0|id|BIGINT|1||1
1|access_token|TEXT|1||0
2|refresh_token|TEXT|1||0
3|expires|DATETIME|1||0
4|session|TEXT|1||0
5|bio|VARCHAR(4069)|1||0
sqlite> INSERT INTO users (id, access_token, refresh_token, expires, session, bio) VALUES (421698654189912064, "foobar", "barfoo", "2021-12-20T17:01:56.066857", "hello world", "cool bio");
sqlite> SELECT * FROM users;
421698654189912064|foobar|barfoo|2021-12-20T17:01:56.066857|hello world|cool bio

aminalaee · 2021-12-20T17:30:30Z

A few points here:

When using the orm.Text you're getting a warning which is explaining the warning, because you should either set that to non nullable or specify a default. Maybe a method returning str(uuid.uuid4())?

And for the BIGINT or INTEGER from SQLite docs:

With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

I think the error you mentioned of unstable auto increment is explained here, INTEGER PRIMARY KEY will be a shortcut to rowid but BIGINT PRIMARY KEY won't be. And as far as I know SQLite doesn't have BIGINT type, so you'd rather use INT PRIMARY KEY.

Anyways please try again with the Integer and see if it solves your issue.

nexy7574 · 2021-12-20T17:43:07Z

Oh my bad, I thought sqlite made all primary keys non-nullalbe (coming from postgres which seemed to do the same thing)

Integer(primary_key=True, allow_null=False) fixed the issue. Thank you @aminalaee 🥇

nexy7574 · 2021-12-20T18:00:02Z

I retract my statement. Instead of inserting the actual data, it just autoincremented the id column again.

(Just to clarify, the value I'm passing to id in objects.create isn't being passed to the query when it should be, I don't want a default.)

aminalaee · 2021-12-20T18:42:42Z

Yeah, I think that's done by decision.

You shouldn't pass the primary key to the query. You either set on on python-side by specifying a default like this:

orm/tests/test_columns.py

Lines 45 to 53 in c6b9c0e

    
           class User(orm.Model): 
        
               registry = models 
        
               fields = { 
        
                   "id": orm.UUID(primary_key=True, default=uuid.uuid4), 
        
                   "name": orm.String(allow_null=True, max_length=16), 
        
                   "email": orm.Email(allow_null=True, max_length=256), 
        
                   "ipaddress": orm.IPAddress(allow_null=True), 
        
                   "url": orm.URL(allow_null=True, max_length=2048), 
        
               }

Or by letting the database to set it:

orm/tests/test_columns.py

Lines 28 to 42 in c6b9c0e

    
           class Product(orm.Model): 
        
               registry = models 
        
               fields = { 
        
                   "id": orm.Integer(primary_key=True), 
        
                   "uuid": orm.UUID(allow_null=True), 
        
                   "created": orm.DateTime(default=datetime.datetime.now), 
        
                   "created_day": orm.Date(default=datetime.date.today), 
        
                   "created_time": orm.Time(default=time), 
        
                   "data": orm.JSON(default={}), 
        
                   "description": orm.Text(allow_blank=True), 
        
                   "huge_number": orm.BigInteger(default=0), 
        
                   "price": orm.Decimal(max_digits=5, decimal_places=2, allow_null=True), 
        
                   "status": orm.Enum(StatusEnum, default=StatusEnum.DRAFT), 
        
                   "value": orm.Float(allow_null=True), 
        
               }

nexy7574 · 2021-12-20T18:44:23Z

Wait.. so can I not specify my own primary key value? does it have to be decided by the db/default?

aminalaee · 2021-12-20T20:08:25Z

Yeah, it's removed here https://github.com/encode/orm/blob/master/orm/models.py#L402

I'm just saying this is expected, if you find maybe Django or SQLAlchemy have a different behaviour, we could change it.

nexy7574 · 2021-12-20T20:18:50Z

Ah I see, just a misunderstanding on my part then 😅

Thanks again

nexy7574 closed this as completed Dec 20, 2021

nexy7574 reopened this Dec 20, 2021

nexy7574 closed this as completed Dec 20, 2021

nexy7574 mentioned this issue Aug 27, 2022

Create and friends ignore setting the PK #157

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Primary key field not being added to any queries #143

Primary key field not being added to any queries #143

nexy7574 commented Dec 20, 2021

aminalaee commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

aminalaee commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

nexy7574 commented Dec 20, 2021 •

edited

aminalaee commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

nexy7574 commented Dec 20, 2021 •

edited

aminalaee commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

aminalaee commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

Primary key field not being added to any queries #143

Primary key field not being added to any queries #143

Comments

nexy7574 commented Dec 20, 2021

Checklist

Describe the bug

To reproduce

Expected behavior

Actual behavior

Debugging material

Environment

aminalaee commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

aminalaee commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

nexy7574 commented Dec 20, 2021 • edited

aminalaee commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

nexy7574 commented Dec 20, 2021 • edited

aminalaee commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

aminalaee commented Dec 20, 2021

nexy7574 commented Dec 20, 2021

nexy7574 commented Dec 20, 2021 •

edited

nexy7574 commented Dec 20, 2021 •

edited