cursor.fetchone() returning None after attempting to insert?

Question

I have a table in an SQLite3 Database that looks like this:

CREATE TABLE "user" (
    "id"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "discriminator" TEXT NOT NULL,
    "userid"    INTEGER NOT NULL UNIQUE,
    "username"  TEXT NOT NULL,
    "matches_played"    INTEGER NOT NULL,
    "match_wins"    INTEGER NOT NULL,
    "match_losses"  INTEGER NOT NULL,
    "match_draws"   INTEGER NOT NULL,
    "rating"    REAL NOT NULL,
    "plays_game"    INTEGER,
    FOREIGN KEY("plays_game") REFERENCES "ygo_games"("game_id")
);

And after attempting an insert like this:

arg = tuple(user_data)
    statement = db_cursor.execute('INSERT INTO user VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?, NULL)', arg)
    result = db_cursor.fetchone()

With the arg tuple being like this:

arg = ('1234', 123123123123, 'Name', 0, 0, 0, 0, 1000)

The result ends up being 'None'. Why could this happen?

Edit: Just realized that INSERT statements don't return any rows, but still, what would be the best way to check if the execution was successful from Python?

Why are you inserting `NULL` into `NOT NULL` columns? Also, what are you hoping to fetch after an action query? And does data show up in database? — Parfait, Jan 30 '20 at 21:11
@Parfait the first value in the INSERT statement is sent as `NULL` because the Id column has `AUTO_INCREMENT` on, so it does not need a value in order to have a value. What I am trying to do is to know if the execution was successful in order to continue with my function. Also, no, data does not show up in the database. — Felipe Hernández, Jan 30 '20 at 21:14
Try to `db_conn.commit()` (using connection object) after the `db_cur.execute()`. — Parfait, Jan 30 '20 at 21:16

score 0 · Answer 1 · answered Jan 30 '20 at 21:30

Per docs, to check if query was successful use the cursor.lastrowid attribute:

If the INSERT or REPLACE statement failed to insert the previous successful rowid is returned.

Also, consider the recommended practice of explicitly specifying the append columns to be clear of mapping of variables from VALUES or SELECT clauses. This always allows you to omit the auto increment column (which by the way is not necessary in SQLite) and last column, plays_game:

sql = '''INSERT INTO user ("discriminator", "userid", "username", "matches_played", 
                           "match_wins", "match_losses", "match_draws", "rating")
         VALUES(?, ?, ?, ?, ?, ?, ?, ?)'''

statement = db_cursor.execute(sql, arg)
id = db_cursor.lastrowid

db_conn.commit()                             # IMPORTANT TO PERSIST CHANGES

cursor.fetchone() returning None after attempting to insert?

1 Answers1