CODEWITHSUNDEEP
pythonsqlitediscord
Johnny McCormick
Johnny McCormick

Reputation: 1

sqlite3 can't get database to stay updated

This is for a discord bot to store a users game data. As long as the bot never goes off this works, but if I turn it off, it re-initializes the database. I've tried commenting out the initial user insertion similarly to how I commented out the creation of the database itself, but this makes the reference to "row" in line 57 not work correctly.

import discord
import os
import sqlite3

conn = sqlite3.connect('test.db')
print("Opened database successfully")

#CREATED DATABASE
#conn.execute('''CREATE TABLE MEMBERS
   #      (ID              INT      NOT NULL,
   #      NAME             TEXT     NOT NULL,
    #     LEVEL            INT      NOT NULL,
    #     CP               INT,
     #    CLASS            CHAR(25))''')
#print ("Table created successfully")

#INSERT USER
conn.execute("INSERT INTO MEMBERS (ID,NAME,LEVEL,CP,CLASS) \
      VALUES (229039794787713025, 'Solumn', 125, 5525391, 'Mage')");
conn.execute("INSERT INTO MEMBERS (ID,NAME,LEVEL,CP,CLASS) \
      VALUES (624678639778267136, 'SacredPugsly', 0, 0, 'None')");
conn.execute("INSERT INTO MEMBERS (ID,NAME,LEVEL,CP,CLASS) \
      VALUES (159985870458322944, 'Noxis', 124, 10, 'Huntard')");

bot = discord.Client()

@bot.event
async def on_ready():
    guild_count = 0
  
    for guild in bot.guilds:
        print(f"- {guild.id} (name: {guild.name})")

        guild_count = guild_count + 1

    print("AlienBot is in " + str(guild_count) + " servers.")
  
  
def check(message):
  try:
    int(message.content)
    return True
  except ValueError:
    return False

@bot.event
async def on_message(message):
    #debug to check user ID'set
    print(message.author.id)

    if message.content.startswith('!show'):
        #SELECT USER
        cursor = conn.execute("SELECT * from MEMBERS WHERE id=?", (message.author.id,))
        for row in cursor:
          print ("ID = ", row[0])

        response = discord.Embed(title=row[1],color=0x3498db)
        response.add_field(name="Level", value=row[2], inline=False)
        response.add_field(name="CP", value=row[3], inline=False)
        response.add_field(name="Class", value=row[4], inline=False)

        await message.channel.send(embed=response)

    if message.content.startswith('!updatecp'):
      await message.channel.send("Enter your CP")
      cp = await bot.wait_for('message',timeout = 60, check=check)
      attempt = int(cp.content)
      cursor = conn.execute("UPDATE MEMBERS SET cp=? WHERE id=?", (int(attempt),message.author.id)) 

    if message.content.startswith('!updatelevel'):
      await message.channel.send("Enter your Level")
      lvl = await bot.wait_for('message',timeout = 60, check=check)
      attempt = int(lvl.content)
      cursor = conn.execute("UPDATE MEMBERS SET LEVEL=? WHERE id=?", (attempt, message.author.id))   

    if message.content.startswith('!help'):
      response = discord.Embed(title='Bot Commands',color=0x3498db)
      response.add_field(name="!updatecp", value='Allows you to adjust your CP', inline=False)
      response.add_field(name="!updatelevel", value='Allows you to adjust your level', inline=False)
      response.add_field(name="!show", value='Shows you your character data', inline=False)

      await message.channel.send(embed=response)
#conn.close()

from dotenv import load_dotenv
load_dotenv()

bot.run(os.getenv("DISCORD_TOKEN"))```

Upvotes: 0

Views: 30

Answers (1)

Lukas Schmid
Lukas Schmid

Reputation: 1960

You never seem to commit your written data. Either do conn.commit() after inserting or set your connections isolation_level to None.

Documentation

Upvotes: 2

Related Questions