Starting with SQL in Python

This is an introduction to getting SQL up and running in a Jupyter notebook with Python.

Photo by Christopher Gower on Unsplash

A quick and easy way to be able to run SQL queries with Python is using SQLite. SQLite is a library that utilizes an SQL database engine. It performs relatively fast and has been proven to be highly reliable. SQLite is the most commonly used database engine in the test environment.

Here are simple steps to getting started.

Step 1 — Importing SQLite and Pandas

To start, we will need to import SQLite into our Jupyter notebook.

import sqlite3
import pandas as pd

Step 2 — Connecting your database

Using the connect() function allows you to create a database in your environment. This helps users name their database to be called in Python later on. The connect() function maintains the connection while you are working with the database.

An important thing to note is that while you are connected to the database, other users will not be able to access it at the same time. This is why it is essential to close() the connection when you are done(I’ll discuss closing a connection near the end).

For this example, I will be referencing a CIA database called “factbook.db”.

sql_connect = sqlite3.connect('factbook.db')

Step 3 — Cursor Object

The cursor() function is used to assist with executing our SQL queries.

cursor = sql_connect.cursor()

It is important that you use the cursor() to return a Cursor instance corresponding to the database we want to query.

Step 4 — Writing a Query

Using SQLite and Python, a SQL query will need to be passed throughout a string. Though it is not necessary, I believe it is good practice to save your queries to a variable so it can be reference later without having to re-write the entire query.

The table from our factbook.db file is called facts.

1. Save SQL Query as a string