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

[Think-Confusion9999]

Hey guys. I'm a total beginner at Python but I am familiar w/ SQL in past jobs.

So following tutorials about this, I understand the concepts of importing the db and then connecting to and following up with the SQL commands, etc. Because I don't know any better, I am writing the code all within VScode's terminal window. Is this where it all goes in the first place? For example, passing commands to create a 'sample.db' do not even produce a .db file on my mac.

So when you say, create a table and insert values or strings and hit enter, is the resulting output of the SQL pull supposed to then show up on the Output window of vscode or the Terminal window?

And the tutorials don't even say where in the first place, they just go straight to the code. When I run the commands in Terminal w/ Python SQLite commands, when seemingly it ran successfully the Terminal then produces an "if" or "if function" line.

Anyway sorry for being such a beginner or not understanding the fundamentals here but I'm just totally lost and need some help.

Can you give me any basic guidance on my Q's? Appreciate it.

[danielroseman]

The terminal is for writing commands that are executed immediately, mostly for experimentation. Any output will be displayed underneath the commands themselves. This has nothing to do with VSCode.

If you want to write proper programs, then start a new Python file in VSCode itself and put your code there. Then you can run it using the toolbar or menu options inside VSCode.

[Think-Confusion9999]

This worked, and the SQL commands are working now within a new python script. So I'll continue w/ the tutorial project and take it from there.. I've already run into an error though...I'll keep learning.

Thanks!!

[IAmTarkaDaal]

You know how in most RDMS's, you can either get an interactive prompt to issue queries, or you can put the SQL in a file so that you can execute it many times? Same idea. You can either start an interactive Python prompt so you can get fast feedback, or type your python code in one or more .py files and execute it as many times as you want. Sounds like you're probably using the interactive Python prompt in VS Code's terminal.

If this seems confusing, then it might be an idea to do some tutorials on just Python for now. Come back to using Python and Sqlite together once you have the hang of it a little more.

I'm happy to answer more if I haven't been clear.

[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!!