sqlite3 Conn Insert Value Error

[TylerDunbar]

Hello - Thank you for reading my post. Quick disclaimer - Teaching myself python late in life (64 yrs). This is to support PhD. I am using "Python Day 2" to get a very very very basic intro to python. The code in the book is throwing an error.

Question: I am attempting to load a single line to a sql database from python. Where is the missing comma? Rather, what is wrong with my syntax. I have included "My Code" and the "Error Message" Below.

I have searched Python Forum, sqlitetutorial.net, geeksforgeeks. Everything that I reference uses the cursor method and a more significant amount of code (see "Example"). I am working through the Example Code currently. I would like to understand where my syntax error is so that I can learn from every effort on my learning journey.


My Code

import sqlite3
conn = sqlite3.connect("simpsons.db")
#conn.execute("CREATE TABLE SIMPSON_INFO(ID INTEGERPRIMARY KEY, NAME TEXT, GENDER TEXT, AGE INT, OCCUPATION TEXT);")
conn.execute("INSERT INTO SIMPSON_INFO(NAME, GENDER, AGE, OCCUPATION) VALUES ("Bart", "Male", 10, "Student");")
conn.commit()

Error Message

Error:
C:\Python\PyCharmProjects\pythonProject1\venv\Scripts\python.exe C:\Python\PyCharmProjects\pythonProject1\DataBaseInstall.py 
  File "C:\Python\PyCharmProjects\pythonProject1\DataBaseInstall.py", line 11
    conn.execute("INSERT INTO SIMPSON_INFO(NAME, GENDER, AGE, OCCUPATION) VALUES ("Bart", "Male", 10, "Student");")
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
SyntaxError: invalid syntax. Perhaps you forgot a comma?

Example

# importing sqlite3 module
import sqlite3


# create connection by using object
# to connect with hotel_data database
connection = sqlite3.connect('hotel_data.db')

# query to create a table named FOOD1
connection.execute(''' CREATE TABLE hotel
		(FIND INT PRIMARY KEY NOT NULL,
		FNAME TEXT NOT NULL,
		COST INT NOT NULL,
		WEIGHT INT);
		''')

# insert query to insert food details in
# the above table
connection.execute("INSERT INTO hotel VALUES (1, 'cakes',800,10 )")
connection.execute("INSERT INTO hotel VALUES (2, 'biscuits',100,20 )")
connection.execute("INSERT INTO hotel VALUES (3, 'chocos',1000,30 )")


print("All data in food table\n")

# create a cousor object for select query
cursor = connection.execute("SELECT * from hotel ")

# display all data from hotel table
for row in cursor:
	print(row)

Yoriz write Sep-04-2023, 04:15 PM:
Please post all code, output and errors (in its entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.

[DPaul]

Without having executed the code, I would look into line 4 (conn.execute(...))
You are using double quotes for both string and value info.
When I do that, python does not like it.
Test it : Use single for string and double quotes for values. (Like you do elsewhere)
Paul

[TylerDunbar]

Hello DPaul - Thank you for looking at my code. That was one of many edits I had tried in advance of posting the question. Im using PyCharm IDE and it seems to have its own syntax rules that I need to getting familiar with.

[deanhystad]

There are three quotes in python, single ', double " and triple""". Single and double quotes can be used interchangeably, but they must be paired. If a string literal starts with a double quote, it ends at the next double quote. In your example, you want the SQL command to be a single string, but the way you use quotes results in multiple strings.

Output:
"INSERT INTO SIMPSON_INFO(NAME, GENDER, AGE, OCCUPATION) VALUES ("
", "
", 10, "
");"

To use quotes within a string you have two choices. Disable the special meaining of the quote character, or use a different quote character to mark the start and stop of the string.

To stop a quote from being interpreted as the end of a string, preceed the quote with a backslash. This is your code using that technique.

conn.execute("INSERT INTO SIMPSON_INFO(NAME, GENDER, AGE, OCCUPATION) VALUES (\"Bart\", \"Male\", 10, \"Student\");")

The example you posted uses the second approach. It uses single quotes inside the string and double quotes to mark the start and end of the string.

conn.execute("INSERT INTO SIMPSON_INFO(NAME, GENDER, AGE, OCCUPATION) VALUES ('Bart', 'Male', 10, 'Student');")

Python ignores the single quotes because it looking for the a quote that matches the start of the string. This is were triple quotes come in handy.

Triple quotes are used to make a string literal that spans multiple lines.

""" I am
a string that spans
over multiple lines."""

But triple quotes can also be used for single line strings. The advantage of using triple quotes is you are free to use single and double quotes inside the string.

One last thing. Are you learning Python or C? Why are you using semicolons? A semicolon can be used to write multiple statements on a single line.

a = 1; b = 2 * a; c = a + b; print(a, b, c)

HOWEVER, you should not write multiple statements on one line, so there should be almost no semicolons used in a python program.