0

I'm trying to add just a single value to mysql table from python. I've tried about five different methods but I still get the error saying that the other keys in my table aren't being passed values when I call insert. The thing is I don't want to insert any other values, just to one specific key. Heres the code I currently have, and thanks in advance for you help

cursor.execute("SELECT * FROM SONG")
        result = cursor.fetchall()
        sname = str(input("What is the name of the song you are looking for: "))
        for row in result:
            title = row[0]
            if(title == sname):
                sql = ("INSERT INTO USER (s_id) VALUES (%s)")
                val = row[1]
                cursor.execute(sql, (val,))

mysql.connector.errors.DatabaseError: 1364 (HY000): Field 'userid' doesn't have a default value

CREATE TABLE USER
(username   VARCHAR(20),
userid      CHAR(3),
a_id        CHAR(3),
s_id        CHAR(3),
PRIMARY KEY (userid),
FOREIGN KEY (a_id) REFERENCES ALBUM(a_id),
FOREIGN KEY (s_id) REFERENCES SONG(s_id));
CREATE TABLE PLAYLIST
(p_name     VARCHAR(20),
p_id        CHAR(3)         NOT NULL,
userid      CHAR(3),
s_id        CHAR(3),
PRIMARY KEY (p_id),
FOREIGN KEY (userid) REFERENCES USER(userid),
FOREIGN KEY (s_id) REFERENCES SONG(s_id));
Josh
  • 1
  • 1
  • "The thing is I don't want to insert any other values, just to one specific key"—are the other fields nullable? Your database may be protecting you from making a data integrity error. – ChrisGPT was on strike Dec 09 '22 at 22:49
  • "I still get the error saying that the other keys in my table aren't being passed values when I call insert"—please [edit] your question and paste the _exact, complete_ error message into it. – ChrisGPT was on strike Dec 09 '22 at 23:08
  • The values are not set to not null so yes, the actual error I get is: Field 'userid' doesn't have a default value – Josh Dec 09 '22 at 23:13
  • If you don't provide values to insert, they are null. And fields must be _nullable_ to accept null values. The error indicates that your `userid` field is not nullable, and therefore cannot accept a null value. Either provide a value for `userid` (and any other non-nullable fields) or make them nullable. – ChrisGPT was on strike Dec 09 '22 at 23:14
  • I made them nullable, I will include my mysql code if you would like to see it – Josh Dec 09 '22 at 23:53
  • You also made `userid` your primary key. Primary keys cannot be null. See the first linked duplicate. – ChrisGPT was on strike Dec 10 '22 at 00:13

1 Answers1

-1

The correct method will be not to use python for that, but just MySQL query to do that

    sname = str(input("What is the name of the song you are looking for: "))
    sql = ("INSERT INTO USER (s_id) select id from SONG where name= '%s'")
    cursor.execute(sql)

https://www.w3schools.com/sql/sql_insert_into_select.asp

arheops
  • 15,544
  • 1
  • 21
  • 27