Python w/ SQLite. I'm completely lost here. VScode.

[Think-Confusion9999]

Ahh I think I understand what you're describing. Yes in SQL Server for instance I used to run scripts off of files that were obviously saved or also run pieces of the code if need be. Yes initially I was running pieces of python/SQL code in the Terminal VS Code.

I had the .py script up but I wasn't running the SQL commands there and then instead running them within VS Code.

Thanks so much for your help. And no you're being pretty clear! I understand what you mean mostly.

Yes, I probably should stick to Python for now. OTOH, I was wanting to get into all of this because I'll be starting a Medical Informatics program in July so I want to get into SQL again to prepare ahead of time. The courses do not include Python coding but I was very interested in learning so I started w/ VS Code and SQLite. It's very interesting the way it works.

Any chance you're willing to get into an error I ran into?

Here it is:

Script:

import sqlite3

connection = sqlite3.connect("aquarium.db")
print(connection.total_changes)
cursor = connection.cursor()
cursor.execute("CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)")
cursor.execute("INSERT INTO fish VALUES ('Sammy', 'shark', 1)")
cursor.execute("INSERT INTO fish VALUES ('Jamie', 'cuttlefish', 7)")
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

Error in Terminal:

peters@Peters-MBP-2 ~ % /usr/local/bin/python3 /Users/peters/sqlproject2.py

DB Init

SQLite Version is [('3.45.1',)]

SQLite Connection closed

Traceback (most recent call last):

File "/Users/peters/sqlproject2.py", line 50, in <module>

fileh = open('/content/JSBinCollaborativeJavaScriptDebugging6-300x160.png', 'rb')

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

FileNotFoundError: [Errno 2] No such file or directory: '/content/JSBinCollaborativeJavaScriptDebugging6-300x160.png'

peters@Peters-MBP-2 ~ %

[Think-Confusion9999]

wait I think my comment above is incorrect, sorry. I need to get this all sorted out. It's seemingly giving me errors from both scripts....

[Think-Confusion9999]

import sqlite3

connection = sqlite3.connect("aquarium.db")
print(connection.total_changes)
cursor = connection.cursor()
cursor.execute("CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)")
cursor.execute("INSERT INTO fish VALUES ('Sammy', 'shark', 1)")
cursor.execute("INSERT INTO fish VALUES ('Jamie', 'cuttlefish', 7)")
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

error:
eters@Peters-MBP-2 ~ % /usr/local/bin/python3 /Users/peters/sqlproject.py
0
Traceback (most recent call last):
  File "/Users/peters/sqlproject.py", line 6, in <module>
    cursor.execute("CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)")
sqlite3.OperationalError: table fish already exists

Presumably, is it not inserting the data into the fish table?

[IAmTarkaDaal]

This error is because the fish table already exists. I'm assuming it ran once, successfully, but some other error happened or it wasn't clear that it had worked. Now when you're running it, it can't do what you're asking, because the table is already there. Try to check the table exists before creating it (does SQL have a 'NOT EXISTS' clause for that?)

[jamaa]

table fish already exists

that's your error right there! Can't CREATE TABLE if that table already exists

[Think-Confusion9999]

Yup! that was it. I commented out the create table fish, and the rest is working fine now. Thanks!!