SQL statements in Python

It's time to begin writing SQL queries! In this exercise, your job is to run a query against the hotels database to find all the expensive hotels in the south. The connection to the database has been created for you, along with a cursor c.

As Alan described in the video, you should be careful about SQL injection. Here, you'll pass parameters the safe way: As an extra tuple argument to the .execute() method. This ensures malicious code can't be injected into your query.

This exercise is part of the course

Building Chatbots in Python

Exercise instructions

Define a tuple t of strings "south" and "hi" for the area and price.
Execute the query using the cursor's .execute() method. You're looking for all of the fields for all hotels where the area is "south" and the price is "hi".
Print the results using the cursor's .fetchall() method.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

# Import sqlite3
import sqlite3

# Open connection to DB
conn = sqlite3.connect('hotels.db')

# Create a cursor
c = conn.cursor()

# Define area and price
area, price = "____", "____"
t = (____, ____)

# Execute the query
c.____('SELECT ____ FROM ____ WHERE ____=____ AND ____=____', ____)

# Print the results
print(____)

Edit and Run Code

This exercise is part of the course

Building Chatbots in Python

IntermediateSkill Level

4.8+

Start Course for Free

In this chapter, you'll learn how to build your first chatbot. After gaining a bit of historical context, you'll set up a basic structure for receiving text and responding to users, and then learn how to add the basic elements of personality. You'll then build rule-based systems for parsing text.

Exercise 1: Introduction to conversational software Exercise 2: EchoBot I Exercise 3: EchoBot II Exercise 4: Creating a personality Exercise 5: Chitchat Exercise 6: Adding variety Exercise 7: ELIZA I: asking questions Exercise 8: Text processing with regular expressions Exercise 9: ELIZA II: Extracting key phrases Exercise 10: ELIZA III: Pronouns Exercise 11: ELIZA IV: Putting it all together

Here, you'll use machine learning to turn natural language into structured data using spaCy, scikit-learn, and rasa NLU. You'll start with a refresher on the theoretical foundations and then move onto building models using the ATIS dataset, which contains thousands of sentences from real people interacting with a flight booking system.

Exercise 1: Understanding intents and entities Exercise 2: Intent classification with regex I Exercise 3: Intent classification with regex II Exercise 4: Entity extraction with regex Exercise 5: Word vectors Exercise 6: word vectors with spaCy Exercise 7: Intents and classification Exercise 8: Intent classification with sklearn Exercise 9: Entity extraction Exercise 10: Using spaCy's entity recognizer Exercise 11: Assigning roles using spaCy's parser Exercise 12: Robust language understanding with rasa NLU Exercise 13: Rasa NLU Exercise 14: Data-efficient entity recognition

In this chapter, you'll build a personal assistant to help you plan a trip. It will be able to respond to questions like "are there any cheap hotels in the north of town?" by looking inside a hotel’s database for matching results.

Exercise 1: Virtual assistants and accessing data Exercise 2: SQL basics Exercise 3: SQL statements in Python

Current Exercise

Exercise 4: Exploring a DB with natural language Exercise 5: Creating queries from parameters Exercise 6: Using your custom function to find hotels Exercise 7: Creating SQL from natural language Exercise 8: Incremental slot filling and negation Exercise 9: Refining your search Exercise 10: Basic negation Exercise 11: Filtering with excluded slots

Everything you've built so far has statelessly mapped intents to actions and responses. It's amazing how far you can get with that! But to build more sophisticated bots you will always want to add some statefulness. That's what you'll do here, as you build a chatbot that helps users order coffee.

Exercise 1: Why statefulness is key Exercise 2: Form filling Exercise 3: Asking contextual questions Exercise 4: Dealing with rejection Exercise 5: Asking questions & queuing answers Exercise 6: Pending actions I Exercise 7: Pending actions II Exercise 8: Pending state transitions Exercise 9: Putting it all together I Exercise 10: Putting it all together II Exercise 11: Frontiers of dialogue research Exercise 12: Generating text with neural networks Exercise 13: Congratulations!