Step-by-Step Guide to Python Microservices with SQL Server for Hotel Management

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)

• 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:

🛠️ 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/’, 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/’, 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 Serveryou 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: