Why is my Sqlite database being deleted after each restart of my code? (Python)

Question

I'm making a discord bot and I want it to have a custom prefix feature. And it's kind of working, but each time I restart it - the database is gone.

I've tried to replace try - except statement with """CREATE TABLE IF NOT EXISTS""". But it doesn't seem to be working properly either. I think I did something wrong in Database initialization.

Here is my code related to this feature:

import sqlite3
import discord
from discord.ext import commands
from discord.utils import get
connection = sqlite3.connect('Server_Settings.db')
cursor = connection.cursor()
try:
        cursor.execute( """CREATE TABLE Settings (_id INTEGER, MD STRING, AR STRING, TC INTEGER, 
        AC INTEGER, WC INTEGER, IACS INTEGER, ITCS INTEGER, IWCS INTEGER,Prefix STRING )""")
except:
        print("no need")

def get_prefix(client, message):
    global Prefix
    TechChannel1 = int
    IsTChannelSet1 = int
    AnnouncementChannel1 = int
    IsAChannelSet1 = int
    WelcomeChannel1 = int
    IsWChannelSet1 = int
    _id = message.guild.id
    check = cursor.execute("SELECT _id FROM Settings WHERE _id = _id")
    result = cursor.fetchone()
    if result != None:
        check = cursor.execute("SELECT Prefix FROM Settings WHERE _id = _id")
        result = cursor.fetchone()
        if result[0] != None:
            Prefix = result[0]
            print(Prefix)
        else:
            cursor.execute("UPDATE Settings SET Prefix = '!' WHERE _id = ?", (_id))
    else:
        if IsTChannelSet == False:
            TechChannel1 = 0
            IsTChannelSet1 = 0
        else:
            IsTChannelSet1 = 1
        if IsAChannelSet == False:
            AnnouncementChannel1 = 0
            IsAChannelSet1 = 0
        else:
            IsAChannelSet1 = 1
        if IsWChannelSet == False:
            WelcomeChannel1 = 0
            IsWChannelSet1 = 0
        else:
            IsWChannelSet1 = 1
            cursor.execute("INSERT INTO Settings (_id,MD,AR,TC,AC,WC,IACS,ITCS,IWCS,Prefix) VALUES (?,?,?,?,?,?,?,?,?,?)",(_id, ModRole, AnnouncementRole, TechChannel1, AnnouncementChannel1, WelcomeChannel1, IsAChannelSet1,
             IsTChannelSet1, IsWChannelSet1, '!'))
    return Prefix

# prefix
bot = commands.Bot(command_prefix=get_prefix)
client = discord.Client
bot.remove_command("help")


@bot.command(name="set.prefix")
@commands.has_role(ModRole)
async def set_prefix(ctx, prefix):
    global Prefix
    mention = ctx.author.mention
    _id = ctx.guild.id
    if len(prefix) > 1:
        embed = discord.Embed(
            title="Attention",
            description="It is not recommended to use prefix with length more than 1 character",
            color= discord.Color.dark_magenta()
        )
        await ctx.channel.send(embed=embed)
    else:
        pass
    cursor.execute("UPDATE Settings SET Prefix = ? WHERE _id = ?", (prefix, _id))
    embed = discord.Embed(title=f"Updating prefix...", description=f"It might take a while", colour=discord.Color.green())
    mes = await ctx.channel.send(embed=embed)
    newmes = await ctx.fetch_message(mes.id)
    cursor.execute("SELECT Prefix FROM Settings WHERE _id = _id")
    result = cursor.fetchone()
    print(result[0], prefix)
    if result[0] == prefix:
        embed = discord.Embed(title=f"@everyone", description=f"My new prefix is {prefix}", colour=discord.Color.green())
        await newmes.edit(embed=embed)
    else:
        embed = discord.Embed(title=f"Oops! Something went wrong", description=f"Try again later", colour=discord.Color.red())
        await newmes.edit(embed=embed)

Running the code:

After restarting code:

Thanks in advance!

Answer 1

Problem

It seems that some of your queries are hardcoded and not using the _id parameter and as such would not identify previously stored values eg:

 check = cursor.execute("SELECT _id FROM Settings WHERE _id = _id")

and

check = cursor.execute("SELECT Prefix FROM Settings WHERE _id = _id")

Proposed Solution

Try changing these to your parameterized queries such as:

cursor.execute("SELECT _id FROM Settings WHERE _id = ?", (_id))

and

check = cursor.execute("SELECT Prefix FROM Settings WHERE _id =  ?", (_id))

I am seeing 3 cases like this in your shared code so far.

Update 1

Referencing the discord api here, message.guild.id should be a snowflake type which are returned as strings in the api.

def get_prefix(client, message):
        global Prefix
        # Previously these were being initialized to a type and not a value
        # Python is an interpreted language that infers type based on first
        # value assignment. I've assigned a default value of `0`. You may 
        # use a value of choice or `None`
        TechChannel1 = 0
        IsTChannelSet1 = 0
        AnnouncementChannel1 = 0
        IsAChannelSet1 = 0
        WelcomeChannel1 = 0
        IsWChannelSet1 = 0

        _id = message.guild.id
        # This should prevent the most recent error `ValueError: parameters are of unsupported type`
        # Adding a check here as it seems from your response shared that
        # The id value from the `message.guild` may be None. You should
        # confirm with the API/docs of discord api to determine whether 
        # this is expected behaviour
        if _id is not None:
            # instead of running a similar query twice to get details
            # from the same row, you can pull all you need in one query
            # You do no seem to use the `id` returned since you are already querying with it
            check = cursor.execute("SELECT Prefix FROM Settings WHERE _id = ?", (_id))
            result = cursor.fetchone()

        if result != None:
            # No longer needed because of edit above
            # check = cursor.execute("SELECT Prefix FROM Settings WHERE _id = ?", (_id))
            # result = cursor.fetchone()
            if result[0] != None:
                Prefix = result[0]
                
            else:
                cursor.execute("UPDATE Settings SET Prefix = '!' WHERE _id = ?", (_id))
                connection.commit() # Commit statement added
        else:
            if IsTChannelSet == False:
                TechChannel1 = 0
                IsTChannelSet1 = 0
            else:
                IsTChannelSet1 = 1
            if IsAChannelSet == False:
                AnnouncementChannel1 = 0
                IsAChannelSet1 = 0
            else:
                IsAChannelSet1 = 1
            if IsWChannelSet == False:
                WelcomeChannel1 = 0
                IsWChannelSet1 = 0
            else:
                IsWChannelSet1 = 1
            cursor.execute("INSERT INTO Settings (_id,MD,AR,TC,AC,WC,IACS,ITCS,IWCS,Prefix) VALUES (?,?,?,?,?,?,?,?,?,?)",(_id, ModRole, AnnouncementRole, TechChannel1, AnnouncementChannel1, WelcomeChannel1, IsAChannelSet1,
                 IsTChannelSet1, IsWChannelSet1, '!'))
            connection.commit() # Commit statement added
        return Prefix

Let me know if this helps.

Answer 2

Ok, so the problem was in lack of commit() statements. Here is what the guy who noticed that said: " I don't see any commit() statement. You normally have to call the commit() method of the (...cursor or connection?) to write a change or group of changes to the database - otherwise your program might function fine in memory, but nothing will save." I am not sure if I did it totally right, but here is how my code looks now:

def get_prefix(client, message):
       global Prefix
        TechChannel1 = int
        IsTChannelSet1 = int
        AnnouncementChannel1 = int
        IsAChannelSet1 = int
        WelcomeChannel1 = int
        IsWChannelSet1 = int
        _id = message.guild.id
        check = cursor.execute("SELECT _id FROM Settings WHERE _id = _id")
        result = cursor.fetchone()
        if result != None:
            check = cursor.execute("SELECT Prefix FROM Settings WHERE _id = _id")
            result = cursor.fetchone()
            if result[0] != None:
                Prefix = result[0]
                connection.commit() # Commit statement added
            else:
                cursor.execute("UPDATE Settings SET Prefix = '!' WHERE _id = ?", (_id))
                connection.commit() # Commit statement added
        else:
            if IsTChannelSet == False:
                TechChannel1 = 0
                IsTChannelSet1 = 0
            else:
                IsTChannelSet1 = 1
            if IsAChannelSet == False:
                AnnouncementChannel1 = 0
                IsAChannelSet1 = 0
            else:
                IsAChannelSet1 = 1
            if IsWChannelSet == False:
                WelcomeChannel1 = 0
                IsWChannelSet1 = 0
            else:
                IsWChannelSet1 = 1
            cursor.execute("INSERT INTO Settings (_id,MD,AR,TC,AC,WC,IACS,ITCS,IWCS,Prefix) VALUES (?,?,?,?,?,?,?,?,?,?)",(_id, ModRole, AnnouncementRole, TechChannel1, AnnouncementChannel1, WelcomeChannel1, IsAChannelSet1,
                 IsTChannelSet1, IsWChannelSet1, '!'))

        return Prefix
    
    #Set commands
    @bot.command(name="set.prefix")
    @commands.has_role(ModRole)
    async def set_prefix(ctx, prefix):
        global Prefix
        mention = ctx.author.mention
        _id = ctx.guild.id
        if len(prefix) > 1:
            embed = discord.Embed(
                title="Attention",
                description="It is not recommended to use prefix with length more than 1 character",
                color= discord.Color.dark_magenta()
            )
            await ctx.channel.send(embed=embed)
        else:
            pass
        cursor.execute("UPDATE Settings SET Prefix = ? WHERE _id = ?", (prefix, _id))
        embed = discord.Embed(title=f"Updating prefix...", description=f"It might take a while", colour=discord.Color.green())
        mes = await ctx.channel.send(embed=embed)
        newmes = await ctx.fetch_message(mes.id)
        cursor.execute("SELECT Prefix FROM Settings WHERE _id = _id")
        result = cursor.fetchone()
        connection.commit() #Commit statement added
        print(result[0], prefix)
        if result[0] == prefix:
            embed = discord.Embed(title=f"@everyone", description=f"My new prefix is {prefix}", colour=discord.Color.green())
            await newmes.edit(embed=embed)
        else:
            embed = discord.Embed(title=f"Oops! Something went wrong", description=f"Try again later", colour=discord.Color.red())
            await newmes.edit(embed=embed)