#🔒 SQL pain p2

22 messages · Page 1 of 1 (latest)

gritty sluice
# 
import sqlite3 as sql

def createDatabase():

    global connection, cursor

    connection = sql.connect("Database.db")
    cursor = connection.cursor()
    try:
        cursor.execute("CREATE TABLE Users(ID, name, subject)")
    except:
        pass

def addUser(Id, name, subject):
    

    cursor.execute(f"""
    INSERT INTO Users VALUES
        ({Id}, {name}, {subject})
    """)

    connection.commit()
    response = cursor.execute("SELECT Id, name, subject FROM Users")
    print(response.fetchall())

createDatabase()
addUser(905236, "Tony", "computer_science")

im getting the error:
Traceback (most recent call last):
File "c:\Users\sampo\OneDrive\Desktop\Reigate\Year 2\Computer Science\NEA\NEA code\database_handler.py", line 27, in <module>
addUser(905236, "Tony", "computer_science")
File "c:\Users\sampo\OneDrive\Desktop\Reigate\Year 2\Computer Science\NEA\NEA code\database_handler.py", line 17, in addUser
cursor.execute(f"""
sqlite3.OperationalError: no such column: Tony

im trying to make a function which can update a database, sorry for the breif explanation.

wraith micaBOT
#

@gritty sluice

Python help channel opened

Remember to:

  • Ask your Python question, not if you can ask or if there's an expert who can help.
  • Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
  • Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.
gritty sluice
# 
import sqlite3 as sql

def createDatabase():

    global connection, cursor

    connection = sql.connect("Database.db")
    cursor = connection.cursor()
    try:
        cursor.execute("CREATE TABLE Users(ID, name, subject)")
    except:
        pass

def addUser(NewID, Newname, Newsubject):
    

    cursor.execute(f"""
    INSERT INTO Users VALUES
        ({NewID}, {Newname}, {Newsubject})
    """)

    connection.commit()
    response = cursor.execute("SELECT ID, name, subject FROM Users")
    print(response.fetchall())

createDatabase()
addUser(905236, "Tony", "computer_science")
    ```

ive just tried to fix this, as it seemed i was replacing column names with variables? im unsure if this is the correct terminology. For example, im executing name, subject and ID, and calling name, subject and ID variables such as 905207, sam, computer science. Hope that makes sense. Oh, and it didnt work, im getting the same error:

  File "c:\Users\sampo\OneDrive\Desktop\Reigate\Year 2\Computer Science\NEA\NEA code\database_handler.py", line 27, in <module>
    addUser(905236, "Tony", "computer_science")
  File "c:\Users\sampo\OneDrive\Desktop\Reigate\Year 2\Computer Science\NEA\NEA code\database_handler.py", line 17, in addUser
    cursor.execute(f"""
sqlite3.OperationalError: no such column: Tony
valid nexus
#

first suggestion: get rid of the exception handler

#

do this instead cursor.execute("CREATE TABLE IF NOT EXISTS Users(ID, name, subject)")

#

that way if something goes wrong, you'll hear about it.

gritty sluice
#

Thank you! ill place that in now

#

Hi there, ive gotten the same error after placing that in:

Traceback (most recent call last):
File "c:\Users\sampo\OneDrive\Desktop\Reigate\Year 2\Computer Science\NEA\NEA code\database_handler.py", line 26, in <module>
addUser(905236, "Tony", "computer_science")
File "c:\Users\sampo\OneDrive\Desktop\Reigate\Year 2\Computer Science\NEA\NEA code\database_handler.py", line 16, in addUser
cursor.execute(f"""
sqlite3.OperationalError: no such column: Tony

# 

import sqlite3 as sql

def createDatabase():

    global connection, cursor

    connection = sql.connect("Database.db")
    cursor = connection.cursor()

    cursor.execute("CREATE TABLE IF NOT EXISTS Users(ID, name, subject)")


def addUser(NewID, Newname, Newsubject):
    

    cursor.execute(f"""
    INSERT INTO Users VALUES
        ({NewID}, {Newname}, {Newsubject})
    """)

    connection.commit()
    response = cursor.execute("SELECT ID, name, subject FROM Users")
    print(response.fetchall())

createDatabase()
addUser(905236, "Tony", "computer_science")
valid nexus
#

second, do your insert like this

# 
    cursor.execute("INSERT INTO Users VALUES (?, ?, ?)",
        [Id, name, subject],
    )
#

what you've got is failing to add quote marks

#

!e

import sqlite3 as sql


def createDatabase():
    global connection, cursor

    connection = sql.connect("Database.db")
    cursor = connection.cursor()

    cursor.execute("CREATE TABLE IF NOT EXISTS Users(ID, name, subject)")


def addUser(Id, name, subject):
    cursor.execute(
        """
    INSERT INTO Users VALUES
        (?, ?, ?)
    """,
        [Id, name, subject],
    )
    connection.commit()
    response = cursor.execute("SELECT Id, name, subject FROM Users")
    print(response.fetchall())


createDatabase()
addUser(905236, "Tony", "computer_science")
wraith micaBOT
gritty sluice
#

brillaint! thank you so much!

#

also, is there any database viewer i can use?

valid nexus
#

there are a couple but I don't use any

#

just google for "sqlite gui"

gritty sluice
#

brilliant thank!

#

you*

wraith micaBOT
#
Python help channel closed

This help channel has been closed and it's no longer possible to send messages here. If your question wasn't answered, feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.