Error in inserting table in column

I am following a online learning course line by line but I somehow got this error. What can I change in order to have the result complete?

sample data

classroom_data =[( 1, “Raj”,“m”, 70, 84, 92),
( 2, “Poonam”, “F”, 87, 69, 93),
( 3, “Nik”, “M”, 65, 83, 90),
( 4, “Rahul”,“F”, 83, 76, 89)]

open connection

connection = sqlite3.connect(“classroomDB.db”)

open cursor

cursor = connection.cursor()

insert each student record

for student in classroom_data:
# formatted query string
insert_statement = “”“INSERT INTO classroom
(student_id, name, gender, physics_marks, chemistry_marks, mathematics_marks)
VALUES
({0}, “{1}”, “{2}”, {3}, {4}, {5});”“”.format(student[0], student[1], student[2],
student[3],student[4], student[5])
# execute insert query
cursor.execute(insert_statement)

commit the changes

connection.commit()

close the connection

connection.close()


RESULT:



OperationalError Traceback (most recent call last)
Cell In [18], line 20
14 insert_statement = “”“INSERT INTO classroom
15 (student_id, name, gender, physics_marks, chemistry_marks, mathematics_marks)
16 VALUES
17 ({0}, “{1}”, “{2}”, {3}, {4}, {5});”“”.format(student[0], student[1], student[2],
18 student[3],student[4], student[5])
19 # execute insert query
—> 20 cursor.execute(insert_statement)
22 # commit the changes
23 connection.commit()

OperationalError: table classroom has no column named chemistry_marks

Your Python code shows messed up. Did you notice? You have to enclose it between triple backticks to prevent Markdown from mangling it:

```
Your code will be here.
```

You posted exactly the same question four days ago. Why do you duplicate your own question? The answer there still apply:

Namely:

We do not see how you created the SQL table classroom so we cannot say why is the column missing.

How did you create the SQLite database file classroomDB.db?

1 Like

I’m sorry I didn’t notice, I thought it was an automated response telling me to not post photos.

I’m still confused as to how to fix the problem. I’m still a beginner, so formatting settings are all foreign to me. I copied the tutorial step by step and this was the result.

To prevent Markdown mangling your text just put a line with triple backticks before your code and another line with triple backticks after your code as I have shown to you. Or use the button </> in the editor’s toolbar.


I’m still confused as to how to fix the problem.

I am not sure if this is about my question: “How did you create the SQLite database file classroomDB.db?” Let me know what should I explain in more details.

We cannot help you without knowing the assignment instructions and the origin of the database file classroomDB.db. According to how your code behaves the SQL table classroom is already in the file and it does not contain the column chemistry_marks.

Did not you skip a part about creating databases and tables in your course?

1 Like

It changed the triple quotes to triple ticks and it looks like that solved the problem. After printing, I recieved another error saying a value is undefined.

I’m still wondering why it isn’t working since I am following the tutorial step by step.

If you want a screenshot of the example, email me. (tsneed2000@gmail.com)

“”"

sample data

classroom_data =[( 1, “Raj”,“m”, 70, 84, 92),
( 2, “Poonam”, “F”, 87, 69, 93),
( 3, “Nik”, “M”, 65, 83, 90),
( 4, “Rahul”,“F”, 83, 76, 89)]

open connection

connection = sqlite3.connect(“classroomDB.db”)

open cursor

cursor = connection.cursor()

insert each student record

for student in classroom_data:
# formatted query string
insert_statement = ‘’‘INSERT INTO classroom
(student_id, name, gender, physics_marks, chemistry_marks, mathematics_marks)
VALUES
({0}, “{1}”, “{2}”, {3}, {4}, {5});’‘’.format(student[0], student[1], student[2],
student[3],student[4], student[5])
# execute insert query
cursor.execute(insert_statement)

commit the changes

connection.commit()

close the connection

connection.close()
“”"
+++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++
Error Message:


NameError Traceback (most recent call last)
Cell In [2], line 8
2 classroom_data =[( 1, “Raj”,“m”, 70, 84, 92),
3 ( 2, “Poonam”, “F”, 87, 69, 93),
4 ( 3, “Nik”, “M”, 65, 83, 90),
5 ( 4, “Rahul”,“F”, 83, 76, 89)]
7 # open connection
----> 8 connection = sqlite3.connect(“classroomDB.db”)
9 # open cursor
10 cursor = connection.cursor()

NameError: name ‘sqlite3’ is not defined

I am sorry, I do not understand what you did. What are those triple “ticks”. I was mentioning triple backticks - sequence of three backtick characters: ```. They are specific characters and they are used to show your code correctly here in this forum. They are not part of the Python code.

We do not see your Python code correctly if you do not mark it properly.


For example if you need to put this text into your post:

for i in range(10):
    print(i)

You have to enclose the whole text block between two separate lines with triple backticks and actually you have to put this text into your post instead:

```
for i in range(10):
    print(i)
```

Then your Python code will show properly here and we will be able to read it.


Please edit your last post and put a separate line with triple backticks (and nothing else) before your code and another separate line with triple backtick after your code. If you are unable to write the backtick character on your keyboard (you cannot replace it by a similarly looking character like an apostrophe '), copy the line here and paste it into your post:

```

Enclose the error message the same way so we can see it properly too. Look at the live preview of your post on the right side to check if it is formatted properly.


No definitely not. We do not want screenshots of text.

You didn’t import sqlite3.

1 Like

Thank you for your patience.

I am using jupyter noetbook by the way, because that’s what the instructer is using.

I don’t have permission to edit posts so I have the code here with the apostrophe and the error I am getting. I added (import sqlite3) so the other error defining sqlite3 has been solved.

Do I have this formatted incorrecly? I’m thinking I used too many or little spaces.

=========================================================================

# sample data 

import sqlite3

classroom_data =[( 1, "Raj","m", 70, 84, 92),
                 ( 2, "Poonam", "F", 87, 69, 93),
                 ( 3, "Nik", "M", 65, 83, 90),
                 ( 4, "Rahul","F", 83, 76, 89)]
                  
# open connection
connection = sqlite3.connect("classroomDB.db")
# open cursor
cursor = connection.cursor()
# insert each student record 
for student in classroom_data: 
    # formatted query string 
    insert_statement = '''INSERT INTO classroom
                       (student_id, name, gender, physics_marks, chemistry_marks, mathematics_marks)
                       VALUES
                       ({0}, "{1}", "{2}", {3}, {4}, {5});'''.format(student[0], student[1], student[2],
                                                                    student[3],student[4], student[5])
    # execute insert query 
    cursor.execute(insert_statement)
    
# commit the changes
connection.commit()
# close the connection
connection.close()

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
Cell In [2], line 23
     17     insert_statement = '''INSERT INTO classroom
     18                        (student_id, name, gender, physics_marks, chemistry_marks, mathematics_marks)
     19                        VALUES
     20                        ({0}, "{1}", "{2}", {3}, {4}, {5});'''.format(student[0], student[1], student[2],
     21                                                                     student[3],student[4], student[5])
     22     # execute insert query 
---> 23     cursor.execute(insert_statement)
     25 # commit the changes
     26 connection.commit()

OperationalError: table classroom has no column named chemistry_marks

Thank you for your patience.

I am using jupyter noetbook by the way, because that’s what the instructer is using.

I don’t have permission to edit posts so I have the code here with the apostrophe and the error I am getting. I added (import sqlite3) so the other error defining sqlite3 has been solved.

Do I have this formatted incorrecly? I’m thinking I used too many or little spaces.

=========================================================================

# sample data 

import sqlite3

classroom_data =[( 1, "Raj","m", 70, 84, 92),
                 ( 2, "Poonam", "F", 87, 69, 93),
                 ( 3, "Nik", "M", 65, 83, 90),
                 ( 4, "Rahul","F", 83, 76, 89)]
                  
# open connection
connection = sqlite3.connect("classroomDB.db")
# open cursor
cursor = connection.cursor()
# insert each student record 
for student in classroom_data: 
    # formatted query string 
    insert_statement = '''INSERT INTO classroom
                       (student_id, name, gender, physics_marks, chemistry_marks, mathematics_marks)
                       VALUES
                       ({0}, "{1}", "{2}", {3}, {4}, {5});'''.format(student[0], student[1], student[2],
                                                                    student[3],student[4], student[5])
    # execute insert query 
    cursor.execute(insert_statement)
    
# commit the changes
connection.commit()
# close the connection
connection.close()

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
Cell In [2], line 23
     17     insert_statement = '''INSERT INTO classroom
     18                        (student_id, name, gender, physics_marks, chemistry_marks, mathematics_marks)
     19                        VALUES
     20                        ({0}, "{1}", "{2}", {3}, {4}, {5});'''.format(student[0], student[1], student[2],
     21                                                                     student[3],student[4], student[5])
     22     # execute insert query 
---> 23     cursor.execute(insert_statement)
     25 # commit the changes
     26 connection.commit()

OperationalError: table classroom has no column named chemistry_marks

It’s telling you what’s wrong: “table classroom has no column named chemistry_marks”. What is the actual name of that column?

There’s also another issue I could mention, and it’s that you should be really using a parametrised query, not building the query string using string formatting. I don’t know whether you’ve got to that point in the course yet, but it’s something to look out for because it’s important for safety and security.

Great! You formatted the post correctly. You can probably see for yourself that indentation is not lost anymore and parts of the text are not replaced by something else anymore. I think that with your current rights you have a limited time to edit your posts in this forum.

About the code

Note that everything essential was said already at the beginning of our communication so I will try to explain it in different words and more details.

connection = sqlite3.connect("classroomDB.db")

Using this command you are opening an SQLite database stored in a file named classroomDB.db.

    insert_statement = '''INSERT INTO classroom
                       (student_id, name, gender, physics_marks, chemistry_marks, mathematics_marks)
                       VALUES
                       ({0}, "{1}", "{2}", {3}, {4}, {5});'''.format(student[0], student[1], student[2],
                                                                    student[3],student[4], student[5])

Here you construct the SQL query which will be used to insert a new row into the database table classroom which is in the opened database file classroomDB.db. Notice that you are not creating any databases, tables or columns in this program. You are using a database which already existed before the program was executed.

Also notice that here in this SQL query you are referring to a column named chemistry_marks.

Also as I noted earlier: this way (using format()) of inserting parameter values into an SQL query is really wrong - it is dangerous! If the course does not explain this for you I would stay away from it. Link to how to do this correctly is in my earlier post.

    cursor.execute(insert_statement)

Here you execute the constructed SQL query. The error shows up as an exception coming from this statement after you execute the notebook cell. See below:

OperationalError: table classroom has no column named chemistry_marks

This is the exception message which explains to you that while you were operating with the table classroom you referred to a column named chemistry_marks but this column does not exist in the table.

Conclusion

You are using an existing database file named classroomDB.db. Your program is written so that it expects that the database contains a table named classroom which contains a column named chemistry_marks. The column does not exist there so the program exits with the error.

I asked you about these earlier already:

  • Where does the database file classroomDB.db come from?
    • Did you create the file or did you get it somehow?
  • Did not you need to do some operations with the file before doing this task?
    • Did not you skip some exercise(s)?
  • Or does not the column have a different name?

Answering these questions is essential for finding the cause of the problem.

When creating the table, I got an error saying the “table classroom” already exists

# Open connection
connection = sqlite3.connect("classroomDB.db")
# open curser
cursor = connection.cursor()
# query for creating table
create_table = """
                CREATE TABLE classroom (
                student_id INTEGER PRIMARY KEY,
                name VARCHAR(20),
                gender CHAR(1),
                physics_marks INTEGER,
                chemistry_marks INTEGER,
                mathematics_marks INTEGER
                );"""
# execute query 
cursor.execute(create_table)
# commit changes 
connection.commit()
# Close connection
connection.close()

==========================================================================

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
Cell In [5], line 16
      6 create_table = """
      7                 CREATE TABLE classroom (
      8                 student_id INTEGER PRIMARY KEY,
   (...)
     13                 mathematics_marks INTEGER
     14                 );"""
     15 # execute query 
---> 16 cursor.execute(create_table)
     17 # commit changes 
     18 connection.commit()

OperationalError: table classroom already exists

Maybe the current error where the “chemistry_marks” column doesn’t exist has to do with the creation of the table itself.

  • The database file classroomDB.db is something I typed into jupyter notebooks letter by letter as the instructor was talking about it in jupyternotebook. It seems to be working for the instructor so it might be created or imported somehow.

  • The operation before is stated above. I copied letter by letter here as well.

  • The column doesn’t have a different name, as I defined it above when creating the table.

You can say CREATE TABLE IF NOT EXISTS classroom ... if necessary.

I’d delete “classroomDB.db” and put the table creation and data insertion in one script so it’s all in the same place while debugging the problem.

1 Like

CREATE TABLE IF NOT EXISTS will not help you to fix your current problem. It will not change the existing table. Deleting the file classroomDB.db and creating it anew will help.

Maybe the file with the missing column was there before you started to do your exercises and you did not notice that your table creation was failing from the beginning.