1. Virtual assistants and accessing data
This chapter is all about building chatbots that behave like a virtual assistant.
Common chatbot use cases include scheduling a meeting, booking a flight,
and searching for a restaurant. All of these tasks require information about the
outside world, and to access it we need to interact with databases, APIs, or
a combination of both.
In these next exercises, you will write code for chatbots that can interact with a
SQL database, but the lessons are also applicable to REST APIs and to other types of
databases.
2. Virtual assistants
3. Basic SQL
To get started, we'll introduce the only SQL command you need to know to complete these
exercises, the SELECT command. In SQL, data is stored in rows in a table.
For example, in a restaurant database, we would typically have a table called "restaurants",
where each row is a single restaurant, and the columns contain the restaurants attributes
like it's name, price range, area, and stars rating.
To retrieve the restaurants from the table (and each of their attributes)
we would execute the query SELECT * from restaurants;
If we only wanted names and ratings, the query would be
SELECT name, rating from restaurants;
where 'name' and 'rating' correspond to the names of the columns in the table.
If we only want expensive restaurants in the center of the city, we query:
SELECT name from restaurants WHERE area = 'center' AND pricerange = 'hi';
4. SQLite with Python
There are many different databases which support some kind of
SQL.
Python has a module called `sqlite3`, which lets us interact with the `sqlite`
database software, which comes pre-installed on most operating systems.
To use it, we first `import sqlite3`. Since SQLite persists data in a single file,
we just pass the path to this file when we want to open up a connection to the database,
conn = sqlite3.connect('hotels.db')
in order to actually run queries, we need to create a cursor using this connection by calling connection dot cursor
we can execute queries by passing a string
c.execute() method.
after that, we can get the results from the cursor object, using the cursor's fetchall method, this returns a list of tuples.
5. SQL injection
One thing we always have to be careful about is SQL injection.
Practically, this means that you shouldn't use regular string operations like
concatenation or the `.format()` method to add parameters to SQL queries.
for example, it's a bad idea to use curly brackets as a placeholder for area, and then use the string's format method to insert it.
This is a bad idea because it lets the user inject any code they like into our SQL command
(including instructions to delete the whole database!).
The save way to pass parameters is to add them as an extra tuple argument, to the execute function.
The execute function has safeguards implemented to make sure malicious code can't be injected into your query this way.
6. Let's practice!
And with that, you know enough SQL to write the DB queries for your first hotel booking chatbot! In the next video, you'll see how to extract query parameters directly from user messages.