No such column: id why does it give an error in discord.py ,sqlite3?

Question

Python

Here is the error:

Ignoring exception in on_ready
Traceback (most recent call last):
File "C:\Users\Acer\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
await coro(*args, **kwargs)
File "D:\Python\Bots\Discord Bots\CasinoBot\bot.py", line 29, in on_ready
if cursor.execute(f"SELECT id FROM users WHERE id = {member.id}").fetchone() is None:
sqlite3.OperationalError: no such column: id

Here is my code:

import discord
from discord.ext import commands
from config import settings
import sqlite3

PREFIX = "."
intents = discord.Intents.all()
bot = commands.Bot(command_prefix = PREFIX, intents = intents)
bot.remove_command('help')

connection = sqlite3.connect("server.db")
cursor = connection.cursor()

@bot.event
async def on_ready() :
cursor.execute("""CREATE TABLE IF NOT EXISTS users (
name TEXT,
id INT,
cash BIGINT,
rep INT,
lvl INT
)""")

for guild in bot.guilds:
for member in guild.members:
if cursor .execute(f"SELECT id FROM users WHERE id = {member.id}").fetchone() is None:
cursor.execute(f"INSERT INTO users VALUES ('{member}', {member.id}, 0 , 0, 1)")

else:
pass

connection.commit()
print("Bot was connected")

@bot.event
async def on_member_join(member):
if cursor.execute(f"SELECT id FROM users WHERE id = {member.id}").fetchone() is None:
cursor.execute (f"INSERT INTO users VALUES ('{member}', {member.id}, 0, 0, 1)")
connectioh.commit()

else:
pass

#balance
@bot.command(aliases = ["balance", "cash", "bal", "balance"])
async def __balance(ctx, member: discord.Member = None):
if meber is None:
await ctx.send(embed = discord.Embed(
description = f""" The balance of the user **{ctx.author}** is **{sql.execute("SELECT cash FROM users WHERE id = {}".format(ctx.author.id)).fetchone()[0]} **"""))
else:
await ctx.send(embed = discord.Embed(
description = f"""The balance of user **{ctx.author}** is **{sql.execute("SELECT cash FROM users WHERE id = {}".format( member.id)).fetchone()[0]} **"""))

bot.run(settings["token"])

Answer 1

First, format the code, it's unreadable. Button </>.
Secondly, your table creation query looks like CREATE TABLE IF NOT EXISTS - that is, if the table already exists, this query will silently do nothing. This is usually the way it should be - but the problem is that if the table exists, but has a different, incorrect set of columns, this query will still silently do nothing! It checks only the existence of the table, but not its structure.
That's why Hemul GM asks you to show the table structure as it is in the database. This can be done in a couple of ways, for example like this.
Write a separate simple python script that connects to your database and executes the following request:

SELECT sql FROM sqlite_master WHERE name = 'users';

Compare the set of columns in this query with your query. If the columns do not match, then the situation that I wrote about above takes place. Then you have to either delete the table (or maybe the entire database) and then re-create it, as it should... or run an ALTER TABLE query to change the set of columns in the table.
The first is much easier if you are just developing a bot, and there is no valuable information stored in the database.

Answer 2

"characters to end of line". You can learn about this and much more from man, for example linux.die.net/man/3/scanf