In today’s digital world, microservices play a vital role in building scalable and maintainable systems.
This blog demonstrates how to create Python Microservices with SQL Server to manage hotel data like guest names, room types, check-in/check-out dates, and room numbers.
🧱 Tech Stack Used
- Python (Flask for microservice framework)
- Microsoft SQL Server (Relational DB)
- PyODBC (SQL Server driver for Python)
- Postman (API testing)
- VS Code (IDE)
- SQL Server Management Studio (DB admin)
🎯 Goal of the Project
We aim to create a simple RESTful Python microservice integrated with SQL Server that performs:
- Create Hotel Records
- Retrieve Hotel Records
- Update Hotel Records
- Delete Hotel Records
🛠️ Steps to Build the Python Microservice
1. Database Setup in SQL Server
Create a new table in SQL Server to store hotel data.
sql
CopyEdit
CREATE TABLE Hotel (
id INT PRIMARY KEY IDENTITY(1,1),
guestName VARCHAR(100),
roomType VARCHAR(50),
checkInDate DATE,
checkOutDate DATE,
roomNumber INT
);
2. Python Microservice Code with Flask
Here’s the complete code to implement Python Microservices with SQL Server:
python
CopyEdit
from flask import Flask, request, jsonify
import pyodbc
app = Flask(__name__)
# SQL Server connection string
conn_str = (
“Driver={ODBC Driver 17 for SQL Server};”
“Server=localhost\\SQLEXPRESS;” # Update with your server name
“Database=HotelDB;” # Update with your DB name
“Trusted_Connection=yes;”
)
# Create hotel record
@app.route(‘/hotels’, methods=[‘POST’])
def create_hotel():
data = request.json
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute(“””
INSERT INTO Hotel (guestName, roomType, checkInDate, checkOutDate, roomNumber)
VALUES (?, ?, ?, ?, ?)
“””, data[‘guestName’], data[‘roomType’], data[‘checkInDate’], data[‘checkOutDate’], data[‘roomNumber’])
conn.commit()
conn.close()
return jsonify({‘message’: ‘Hotel record created’}), 201
# Get all hotel records
@app.route(‘/hotels’, methods=[‘GET’])
def get_hotels():
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute(“SELECT * FROM Hotel”)
hotels = cursor.fetchall()
result = []
for row in hotels:
result.append({
‘id’: row.id,
‘guestName’: row.guestName,
‘roomType’: row.roomType,
‘checkInDate’: row.checkInDate.strftime(“%Y-%m-%d”),
‘checkOutDate’: row.checkOutDate.strftime(“%Y-%m-%d”),
‘roomNumber’: row.roomNumber
})
conn.close()
return jsonify(result)
# Update hotel record
@app.route(‘/hotels/<int:id>’, methods=[‘PUT’])
def update_hotel(id):
data = request.json
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute(“””
UPDATE Hotel
SET guestName=?, roomType=?, checkInDate=?, checkOutDate=?, roomNumber=?
WHERE id=?
“””, data[‘guestName’], data[‘roomType’], data[‘checkInDate’], data[‘checkOutDate’], data[‘roomNumber’], id)
conn.commit()
conn.close()
return jsonify({‘message’: ‘Hotel record updated’})
# Delete hotel record
@app.route(‘/hotels/<int:id>’, methods=[‘DELETE’])
def delete_hotel(id):
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute(“DELETE FROM Hotel WHERE id=?”, id)
conn.commit()
conn.close()
return jsonify({‘message’: ‘Hotel record deleted’})
if __name__ == ‘__main__’:
app.run(debug=True)
🔍 Testing the Python Microservice
Use Postman to test the API endpoints:
- POST /hotels → Create a new record
- GET /hotels → View all records
- PUT /hotels/{id} → Update record
- DELETE /hotels/{id} → Delete record
🧠 Why Use Python Microservices with SQL Server?
- Scalability: Break down monolithic applications into independent microservices.
- Database Power: SQL Server handles relational data efficiently and securely.
- Python Integration: Libraries like pyodbc make it easy to connect Python services with SQL Server.
- Easy Testing: RESTful architecture makes it easy to test and debug.
🧩 Use Cases in the Hotel Industry
- Managing bookings and reservations
- Handling guest check-in/check-out operations
- Integrating hotel systems with CRM platforms
- Real-time availability tracking and analytics
🏁 Conclusion
Using Python Microservices with SQL Server, you can create modular, maintainable, and scalable systems for hotel data management. This architecture allows flexibility and ensures enterprise-grade reliability. Whether you’re building a hotel CRM or a booking engine, this setup is robust and production-ready.
Additional Resources: