How do I add 1 to a SQLite3 Integer (python)

user_15070107 0
Viewed 302

I am trying to make a table that logs the amount of times a player has logged in, by UUID and number of joins.

Here is what my table looks like (for testing)

I would like to make my program check if the UUID is already in the database, and then add 1 to the number of joins.

import sqlite3

connection = sqlite3.connect("joins.db")
cursor = connection.cursor()
try:
    cursor.execute("CREATE TABLE joinsdatabase (uuid TEXT, joins INTEGER)")
except:
    print("Table exists: Not creating a new one!")

def addP(player_uuid):
    rows = cursor.execute("SELECT uuid, joins FROM joinsdatabase").fetchall()
    cursor.execute("INSERT INTO joinsdatabase VALUES ('"+player_uuid+"', 1)")
    connection.commit()
upsert sqlite sql python database
2 Answers

Try this, you may need to play about with the variables.

cursor.execute("UPDATE joinsdatabase SET count = ? WHERE uuid = ?", (int(oldCount) + 1, player_uuid))
user_150706890

If your version of SQLite is 3.24.0+ you can use UPSERT, but first you must define the column uuid as PRIMARY KEY or UNIQUE. So drop the table that you have with:

DROP TABLE joinsdatabase;

and then recreate it:

CREATE TABLE joinsdatabase (uuid TEXT PRIMARY KEY, joins INTEGER DEFAULT 1)

This way also, the default value of joins for a new row will be 1, so there is no need to set it in the INSERT statement.

Now you can use UPSERT like this:

def addP(player_uuid):
    sql = """INSERT INTO joinsdatabase(uuid) VALUES (?)
    ON CONFLICT(uuid) DO UPDATE
    SET joins = joins + 1"""
    cursor.execute(sql, (player_uuid,))
    connection.commit()

You don't need this line:

rows = cursor.execute("SELECT uuid, joins FROM joinsdatabase").fetchall()

inside addP().

If your version of SQLite does not support UPSERT you will need 2 statements.

def addP(player_uuid):
    sql = "UPDATE joinsdatabase SET joins = joins + 1 WHERE uuid = ?"
    cursor.execute(sql, (player_uuid,))
    sql = "INSERT OR IGNORE INTO joinsdatabase(uuid) VALUES (?)"
    cursor.execute(sql, (player_uuid,))
    connection.commit()

The UPDATE statement will update the row if the uuid exists in the table and the INSERT statement will insert the row if the uuid does not exist in the table.
This will also work if uuid is unique.

user_104988280
Related