Use JupySQL in
We have seen how JupySQL allows users to run SQL queries via the
%%sql magics, but what if you want to execute SQL queries in a
.py script instead?
In this tutorial, we’ll demonstrate how to embed JupySQL magics in a Python file using VSCode and Spyder.
Python Interactive Window in VSCode#
VSCode allows users to work with Jupyter-like code cells and run code in the Python Interactive Window. To work with these code cells, first, select the Python environment in which JupySQL is installed. To select an environment, use the Python: Select Interpreter command from the Command Palette.
Once done, you can define Jupyter-like code cells within Python code using a
# %% comment. For more details, refer VSCode Jupyter support.
Here’s a code snippet that allows users to download a sample dataset and perform SQL queries on the data using JupySQL’s
%%sql cell magic.
# %% %pip install jupysql duckdb duckdb-engine --quiet %load_ext sql %sql duckdb:// # %% from urllib.request import urlretrieve _ = urlretrieve( "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv", "penguins.csv", ) # %% %%sql SELECT * FROM penguins.csv LIMIT 3
Now let’s look at the steps for running this code in VSCode.
First, create a new file and select the file type as
Python File as shown below:
Now, add a code cell and try to run the cell. It would prompt the user to install the
Ensure to select the correct Python environment for the code cell to run properly:
Now, run the file in the interactive mode as shown below. You may also run each cell individually by clicking the
Run Cell option.
Python Interactive Window in Spyder#
The Spyder IDE also supports the
# %% format for running Python code cells interactively as we can see below:
Python Interactive Window in PyCharm#
The percent format is also supported by
Click here for more details on the percent format.
Users may be interested in running the scripts programmatically. This can be achieved by using
jupytext and ploomber-engine.
ploomber-engine is a toolbox for executing notebooks.
Let’s say we save the code snippet in a file named
sql-analysis.py. Run the below commands in the terminal to run it programmatically.
pip install ploomber-engine jupytext sql-analysis.py --to ipynb ploomber-engine sql-analysis.ipynb output.ipynb
output.ipynb should look like: