Creating a Simple API to Access Data from an SQLite Database
Written on
Chapter 1: Introduction to APIs and SQLite
APIs play a crucial role in software development, enabling applications to communicate and share data seamlessly. In this guide, we'll delve into crafting a straightforward API that fetches data from an SQLite database utilizing Python. We will also cover how to populate the database before setting up the API.
Section 1.1: Setting Up the SQLite Database
To begin, we need to establish our SQLite database. First, ensure you have the SQLite package installed. SQLite is a lightweight, serverless, and self-managing SQL database engine.
Once installed, you can create a new database file using the following Python code:
import sqlite3
conn = sqlite3.connect('database.db')
The connect function initiates a connection to the SQLite database, yielding a connection object. The database.db parameter designates the name of the database file; SQLite will generate it if it doesn't already exist.
Section 1.2: Creating a Table
Next, let's create a table to hold our data. We will define a table named employees with three fields: id, name, and salary. The id field will serve as the primary key, ensuring each entry is unique.
Here’s the SQL command to set up the employees table:
cur = conn.cursor()
cur.execute('''
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
salary REAL
)
''')
conn.commit()
The cursor object allows us to execute SQL statements in the database. The CREATE TABLE command forms the employees table with the specified columns. After defining the table structure, we commit the changes to the database.
Section 1.3: Inserting Data into the Table
With our table ready, we can now add some entries. For demonstration, we'll insert three employees into the table:
cur.execute("INSERT INTO employees (name, salary) VALUES (?, ?)", ('John Doe', 5000))
cur.execute("INSERT INTO employees (name, salary) VALUES (?, ?)", ('Jane Doe', 6000))
cur.execute("INSERT INTO employees (name, salary) VALUES (?, ?)", ('Bob Smith', 7000))
conn.commit()
Here, we employ the INSERT INTO command to input three records into the employees table, each comprising a name and salary. After executing the inserts, we commit the changes to ensure they are saved.
Chapter 2: Building the API
Now that we have data in our database, we can create an API to retrieve it. We will utilize the Flask framework for this purpose:
from flask import Flask, jsonify
app = Flask(__name__)
@app.route('/employees')
def get_employees():
cur = conn.cursor()
cur.execute("SELECT * FROM employees")
rows = cur.fetchall()
employees = []
for row in rows:
employee = {
'id': row[0],
'name': row[1],
'salary': row[2]
}
employees.append(employee)
return jsonify(employees)
This snippet defines a Flask application with a single endpoint, /employees, which returns a JSON array containing all employee records from the database. The function utilizes the cursor to execute a SQL query that retrieves all rows from the employees table. Each row is transformed into a dictionary and appended to a list, which is returned as a JSON response through the jsonify function.
To run the Flask app, add this code at the end of your script:
if __name__ == '__main__':
app.run(debug=True)
Chapter 3: Testing Your API
To verify your API's functionality, you can use the requests library in Python, which enables sending HTTP requests and examining responses. Here’s an example of testing your API:
import requests
response = requests.get('http://localhost:5000/employees')
if response.status_code == 200:
employees = response.json()
for employee in employees:
print(employee['name'], employee['salary'])
else:
print('Failed to get employees:', response.status_code)
In this example, the requests.get method sends a GET request to the /employees endpoint. If the response indicates a success (status code 200), we retrieve and print the employee data. Otherwise, an error message is displayed.
You can also explore the requests.post, requests.put, and requests.delete methods to test different HTTP methods in your API.
Additionally, consider using testing frameworks like unittest or pytest for comprehensive testing, allowing you to define various test cases to validate your API's performance.
Conclusion
In summary, setting up a basic API to access data from an SQLite database is a manageable endeavor in Python. By leveraging the Flask framework, we can swiftly construct a route handler to execute SQL queries and return results in JSON format. Following the outlined procedures enables us to populate the database with sample data, facilitating real-world testing of our API.
Learn how to create an SQL API for your Database in just minutes using WayScript. This video guides you through the automation process.
Watch this tutorial on integrating a RESTful API with an SQL database through a real-world Martini use case.