Building a WhatsApp Chatbot in Python: Collecting User Information and Storing Data in Database and Excel

Step 1: Setup Twilio Account and WhatsApp Sandbox

Twilio is a cloud communications platform that allows you to integrate various communication channels into your applications, including WhatsApp.

1. Create a Twilio Account: Go to Twilio’s website and sign up for an account. Twilio offers a free trial with a credit balance that you can use to test your application.
2. Get Twilio API Credentials: After logging into your Twilio account, navigate to the Console to find your Account SID and Auth Token. These credentials are used to authenticate your requests to Twilio’s API.
3. Setup WhatsApp Sandbox: Twilio provides a WhatsApp Sandbox for testing your WhatsApp integration without needing a production WhatsApp Business Account. Follow Twilio’s instructions to set up the sandbox and get a Sandbox Number, which is a virtual WhatsApp number provided by Twilio for testing purposes.

Step 2: Install Required Libraries

Before you can start coding, install the necessary Python libraries using pip. These libraries will help you build the chatbot, handle WhatsApp messages, interact with the database, and work with Excel files.

pip install twilio flask sqlalchemy openpyxl

• twilio: Twilio's Python helper library for interacting with the Twilio API.
• flask: A lightweight web framework for creating web applications in Python.
• sqlalchemy: An ORM (Object-Relational Mapper) library for working with databases in Python.
• openpyxl: A library for reading and writing Excel files in Python.

Step 3: Initialize Flask App and Twilio Client

In this step, you’ll set up your Flask application and initialize the Twilio client to send and receive messages via WhatsApp. Create a new Python file (e.g., app.py) and import the required libraries:

from flask import Flask, request, jsonify
from twilio.twiml.messaging_response import MessagingResponse
from twilio.rest import Client
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from openpyxl import Workbook

# Initialize Flask app
app = Flask(__name__)
# Initialize Twilio client
account_sid = 'YOUR_TWILIO_ACCOUNT_SID'
auth_token = 'YOUR_TWILIO_AUTH_TOKEN'
client = Client(account_sid, auth_token)
# Initialize SQLAlchemy
Base = declarative_base()
engine = create_engine('sqlite:///user_data.db', echo=True)
Session = sessionmaker(bind=engine)
session = Session()

Replace 'YOUR_TWILIO_ACCOUNT_SID' and 'YOUR_TWILIO_AUTH_TOKEN' with your actual Twilio credentials.

• Flask: Initializes a Flask web application.
• Twilio Client: Initializes the Twilio client using your Twilio Account SID and Auth Token.
• SQLAlchemy: Sets up SQLAlchemy to work with an SQLite database (user_data.db).
• Openpyxl: Prepares the Excel file handling for later steps.

Step 4: Define Database Model

In this step, you define the structure of your database using SQLAlchemy’s ORM. This model represents the user data that will be collected and stored.

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    mobile_number = Column(String)
    num_items = Column(Integer)
    address = Column(String)

# Create the database tables
Base.metadata.create_all(engine)

• User: Represents the users table in the database with columns for user details such as name, mobile number, number of items, and address.
• Base.metadata.create_all(engine): Creates the database tables based on the defined models.

Step 5: Create Routes for Chatbot Actions

In this step, you create routes (URL endpoints) that handle incoming WhatsApp messages and process user data.

@app.route('/webhook', methods=['POST'])
def webhook():
    incoming_msg = request.values.get('Body', '').lower()
    resp = MessagingResponse()
  if 'name' in incoming_msg:
          resp.message("Please enter your name.")
      elif 'mobile number' in incoming_msg:
          resp.message("Please enter your mobile number.")
      elif 'number of items' in incoming_msg:
          resp.message("Please enter the number of items.")
      elif 'address' in incoming_msg:
          resp.message("Please enter your address.")
      else:
          # Save user data to database
          user = User(
              name=request.values.get('Body'),
              mobile_number=request.values.get('From').split(":")[-1],
              num_items=request.values.get('NumMedia'),
              address=request.values.get('MediaUrl0')
          )
          session.add(user)
          session.commit()
          resp.message("Thank you! Your information has been recorded.")
      return str(resp)
  if __name__ == "__main__":
      app.run(debug=True)    if 'name' in incoming_msg:
          resp.message("Please enter your name.")
      elif 'mobile number' in incoming_msg:
          resp.message("Please enter your mobile number.")
      elif 'number of items' in incoming_msg:
          resp.message("Please enter the number of items.")
      elif 'address' in incoming_msg:
          resp.message("Please enter your address.")
      else:
          # Save user data to database
          user = User(
              name=request.values.get('Body'),
              mobile_number=request.values.get('From').split(":")[-1],
              num_items=request.values.get('NumMedia'),
              address=request.values.get('MediaUrl0')
          )
          session.add(user)
          session.commit()
          resp.message("Thank you! Your information has been recorded.")

• @app.route('/webhook', methods=['POST']): Defines a route for handling incoming POST requests, which are sent by Twilio when a message is received.
• incoming_msg = request.values.get('Body', '').lower(): Retrieves the incoming message from Twilio and converts it to lowercase for easier processing.
• The if-elif-else block checks the incoming message and responds accordingly, prompting the user to enter their name, mobile number, number of items, and address.
• Upon receiving all required information, the user’s data is saved to the database using SQLAlchemy.

Step 6: Run the Flask App

Run your Flask app using the following command in your terminal or command prompt:

python app.py

This command starts the Flask development server, allowing your chatbot to receive messages from WhatsApp.

Step 7: Test Your Chatbot

Send messages to your WhatsApp Sandbox number to test the chatbot’s functionality. Follow the prompts to enter the required information and verify that the data is stored correctly in the SQLite database (user_data.db).

Step 8: Export Data to Excel

Lastly, you can add code to export the collected user data to an Excel file for further analysis or reporting.

def export_to_excel():
    users = session.query(User).all()
    
    wb = Workbook()
    ws = wb.active
    ws.append(['Name', 'Mobile Number', 'Number of Items', 'Address'])
    for user in users:
        ws.append([user.name, user.mobile_number, user.num_items, user.address])
    
    wb.save('user_data.xlsx')
# Call the export function after data collection
export_to_excel()

This code retrieves all user data from the database and writes it to an Excel file named user_data.xlsx with columns for name, mobile number, number of items, and address.

By following these steps and explanations, you should have a fully functional WhatsApp chatbot that collects user information, stores it in a database, and exports it to an Excel file.