Two ways to execute SQL commands in Python....? Confused

I am following Dataquest’s “Data Analyst in Python” learning path (and enjoying it :smile: !)

I am now at this lesson, in which I am learning “how to interact with a SQLite database in Python”.
It teaches me this:

import sqlite3
conn = sqlite3.connect("jobs.db")
cursor = conn.cursor()

query = "select * from recent_grads;"
cursor.execute(query)
results = cursor.fetchall()
print(results[0:2])

So if my understanding is correct, I would be able to start a Jupyter Notebook and apply this to retrieve data from a SQLite database.

Now only a few lessons ago, I was asked to enter the following in a Jupyter Notebook:

!conda install -yc conda-forge ipython-sql

followed by:

%%capture
%load_ext sql
%sql sqlite:///factbook.db

which I did (without understanding what I was doing really, to be honest), after which I could SQL execute commands like:

%%sql
SELECT *
  FROM facts
 LIMIT 5

So it appears to me that I am learning two ways in which I can access .db files files and run SQL commands on them.

That’s great… but then also confuses me, raising questions with me like:

  • How does one relate to the other?
  • When/why to use one or the other? (Maybe it has to do with the output format that you are looking for…?)
  • For the first thing that I applied (with %%sql cells), could I have achieve the same with what I am learning now?
  • For the first thing that I learnt (with %%sql cells) what was I actually doing really? I am reading to term ‘cell magic’ related to the %% thing but I don’t understand what that means.
  • Or is the first one that I learnt more related to “Jupyter Notebook” rather than to SQLite as such?

Maybe my questions are a bit… fuzzy and do not make real sense. (Forgive me, I am not a computer scientist by background!). But I hope someone can make some sense of my questions and try to fill me in (somewhat)…!

The first of the ways you referenced is Python.

The second one isn’t really Python. Jupyter isn’t necessarily Python, you can use multiple languages with Jupyter. Jupyter enhances Python.

One of these enhancements is magics. Magics are those code snippets that start with %. With %% sql, you can run SQL code in a Jupyter cell. It’s not Python anymore at that stage.


How does one relate to the other?

I believe I addressed this above.

When/why to use one or the other? (Maybe it has to do with the output format that you are looking for…?)

It depends on what you’re doing and on personal preference. If you’re just exploring a database, you can go either way. But if you want something to be ran, say, daily, a Python script looks like a better option.

For the first thing that I applied (with %%sql cells), could I have achieve the same with what I am learning now?

Depends on what you mean exactly. You can print the same results, but you’re not going to get a Python object like results.

For the first thing that I learnt (with %%sql cells) what was I actually doing really? I am reading to term ‘cell magic’ related to the %% thing but I don’t understand what that means.

Magics are enhancements to iPython (the tool behind the curtains of Jupyter). There are line magics, which act on/from a line of code, and cell magics which act on the whole cell. See the link I shared above for a list of magics and what they can do.

Or is the first one that I learnt more related to “Jupyter Notebook” rather than to SQLite as such?

I believe I already addressed this.

Thank you @Bruno, that is very helpful! It makes much more sense now to me!

1 Like