Sign inGet started
← Back to all guides

How to connect to a SQL database in a Juptyer notebook

By Nick Barth

Updated on March 6, 2024

Connecting to a SQL database in Jupyter involves using specific libraries and writing some Python code to establish the connection. Below are the step-by-step instructions to connect to a SQL database within a Jupyter notebook.

Step 1: Install required libraries

First, ensure you have the necessary libraries installed. You mentioned a preference for `pandas`. Pandas will provide convenient data manipulation, but you also need a driver to connect to your specific SQL database. For instance, `sqlite3` for SQLite databases, `pymysql` for MySQL databases, or `psycopg2` for PostgreSQL databases.

You can install the necessary libraries using `pip`. In your Jupyter notebook, run the following command (replacing `library_name` with the name of your chosen database library):

!pip install pandas library_name

Step 2: Import libraries

Next, import the installed libraries:

import pandas as pd
⁠
⁠import library_name as db

Replace `library_name` with the actual imported name of the library you're using.

Step 3: Database Details

Ask your users to fill in their database credentials. You can use a cell in Jupyter to collect these details:

database_type = 'your_database_type_here'
⁠
⁠host = 'your_host_here'
⁠
⁠port = 'your_port_here'
⁠
⁠database_name = 'your_database_name_here'
⁠
⁠user = 'your_username_here'
⁠
⁠password = 'your_password_here'

Step 4: Create database connection string

Construct the connection string based on the input details:

connection_string = f"{database_type}://{user}:{password}@{host}:{port}/{database_name}"

For example, a connection string for a PostgreSQL database might look like this:

connection_string = f"postgresql://{user}:{password}@{host}:{port}/{database_name}"

Step 5: Connect to the database

Use the following code to establish the connection to the database and run a query. Note that the specifics might differ depending on the SQL dialect and library you're using.

connection = db.connect(connection_string)

Example query: Select everything from a specific table

query = "SELECT * FROM your_table_name"

Run the query and store the result in a pandas DataFrame

df = pd.read_sql(query, connection)

Display the first few rows of the DataFrame

df.head()

Step 6: Using Deepnote (optional)

If you're using Deepnote, an alternative to Jupyter, you can take advantage of its integrated database connection function. Deepnote allows you to connect directly to your database through its user interface with a few clicks, making this process simpler and more straightforward.

To connect via Deepnote:

  1. Click the 'Integrations' tab on the left panel.
  2. Add a new integration and select your database type.
  3. Fill in your database’ details such as host, port, database, user, and password.
  4. Once connected, you can access the database directly using SQL cells or link it to a Python cell as shown above.

Remember, when entering sensitive information such as passwords, consider using secret environment variables or other secure input methods that avoid hardcoding credentials into your notebook.

By following these steps, you should be able to connect to your SQL database within a Jupyter notebook environment effectively.

Nick Barth

Product Engineer

Nick has been interested in data science ever since he recorded all his poops in spreadsheet, and found that on average, he pooped 1.41 times per day. When he isn't coding, or writing content, he spends his time enjoying various leisurely pursuits.

Follow Nick on LinkedIn and GitHub

That’s it, time to try Deepnote

Get started – it’s free
Book a demo

Footer

Solutions

  • Notebook
  • Data apps
  • Machine learning
  • Data teams

Product

Company

Comparisons

Resources

  • Privacy
  • Terms

© Deepnote