SQL + Python = Awesome!

You can create a program to interact with a database by embedding SQL syntax in a Python script (using the sqlite3 library, which comes standard with every Python install)

Let's write a short program that asks a user for a place name and returns all records from the database that contain the value in the "place" field.

1. Using Sublime, create a file called "nypl_search.py" in the directory where the "nypldb.db" database is located.

2. Write some pseudocode in "nypl_search.py" that describes what our code will do once it's finished:

# import sqlite3 library

# connect to the database

# say hello to the user

# ask the user for a place

# search the place field in the "nypl_items" table for the place name

# return a list of records from the database that are from the place name

1. Import the sqlite3 library, connect to the database, and create a cursor object. (Don't worry about this last part!)

# import sqlite3 library
import sqlite3

# connect to the database
conn = sqlite3.connect('nypldb.db')

# create a cursor object  
c = conn.cursor()

1. Replace pseudocode with working Python and SQL

# say hello to the user
print("Hello! I will search your database for items from any place you tell me! ")

# ask the user for a place name
place_name = input("Please give me a place name: ")

# search the place field for the place name
cur.execute("SELECT * FROM nypl_items WHERE place = ?", (place_name,))

# return a list of records from the database that contain the keyword
record_list = cur.fetchall()

for i in record_list:
    print("\n\n", i)

1. Run the program. First, open the command line, cd to the directory containing your "nypl_search.py" file and "nypldb.db" database. Then type

python nypl_search.py

and hit Enter. You'll be prompted to enter a place name, which is case-sensitive. Try "Paris" or some other location, and you should see output from the database returned to you.

Congratulations! You've successfully accessed your database using a Python script. This is an excellent first step in performing data analysis on large data sets or creating your own applications. You'll use these skills in later sections, including Introduction to Natural Language Processing with NLTK and Introduction to Web Frameworks with Flask.