Python Project■Based Interview Guide
Prepared for roles involving Python, Excel Automation, REST APIs, Office 365, and SQLite
This guide contains detailed project■related questions and answers to help you explain your work
confidently in interviews.
Project 1: Excel to SharePoint Automation
Q: Can you explain your Excel to SharePoint automation project?
A: I built Python scripts that automated the process of reading Excel files, cleaning the data, and sending
it to SharePoint lists. This eliminated manual data entry. I used pandas and openpyxl for Excel handling,
and the SharePoint REST API for inserting data. I also integrated with Power Automate flows that were
triggered when SharePoint items were updated, helping in business approval workflows.
Q: How do you handle missing or invalid values in Excel before pushing to SharePoint?
A: Before pushing data, I validated each row. Missing values were either filled with defaults using
pandas.fillna() or skipped with proper logging. For example, email addresses were checked with regex,
and rows with invalid emails were not uploaded. This reduced runtime errors and improved data quality.
import pandas as pd, re
df = pd.read_excel("data.xlsx")
valid = df[df['Email'].apply(lambda x: bool(re.match(r'^[\w.-]+@[\w.-]+$', str(x))))]
valid.to_excel("valid_data.xlsx", index=False)
Q: How did you connect Python to SharePoint?
A: I connected using the SharePoint REST API or Microsoft Graph API. Authentication was handled
using tokens provided by Office 365. After obtaining an access token, I used the requests library in
Python to perform POST or PATCH calls to SharePoint list endpoints.
import requests
url = "https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items"
headers = {"Authorization": "Bearer ", "Content-Type": "application/json"}
data = {"fields": {"Title": "Task1", "Status": "Pending"}}
resp = requests.post(url, headers=headers, json=data)
print(resp.status_code, resp.json())
Q: How did your automation integrate with Power Automate?
A: Once items were inserted into SharePoint lists using Python, Power Automate flows were configured
to trigger automatically on new item creation. For example, when a record was added via Python, an
approval workflow in Power Automate was triggered and managers received notifications in Outlook or
Teams.
Q: What challenges did you face during this project?
A: The main challenges were authentication with SharePoint API, handling Excel files with inconsistent
formatting, and making sure retries happened if API requests failed. I solved these by using OAuth
refresh tokens, validating Excel data rigorously, and writing retry logic with exponential backoff.
Project 2: SQLite & API Utilities
Q: Can you describe your SQLite and API project?
A: In this project, I developed small Python scripts for data validation before inserting records into SQLite
and Office 365 lists. I also worked with simple APIs using the requests module. Additionally, I built forms
and small utilities for business users to search and update metadata more easily.
Q: Why did you choose SQLite as the database?
A: SQLite was chosen because it is lightweight, serverless, and easy to embed into Python scripts. For a
6■month utility project, SQLite required no setup and was sufficient for local validation tasks. It also
integrates smoothly with pandas for analysis.
Q: How do you insert and query data using Python sqlite3?
A: You connect to a database with sqlite3.connect(), then use cursors to execute SQL queries. Always
use parameterized queries to prevent SQL injection. Commit changes and close the connection when
done.
import sqlite3
con = sqlite3.connect("test.db")
cur = con.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS users(id INTEGER, name TEXT)")
cur.execute("INSERT INTO users VALUES(?, ?)", (1, "Nivi"))
con.commit()
for row in cur.execute("SELECT * FROM users"): print(row)
con.close()
Q: How did you validate data before inserting into SQLite?
A: I added checks for data types and formats using Python functions before insertion. For example,
numeric fields were checked with isnumeric(), and empty strings were replaced with None to avoid
constraint violations. Invalid rows were logged separately for review.
Q: What APIs did you work with in this project?
A: I worked with simple REST APIs that exposed data for Office 365 and other business utilities. I used
Python's requests module to call GET and POST endpoints, handle JSON responses, and integrate them
with SQLite for local use.
Common Interview Questions Based on Both Projects
Q: What is the difference between requests.text and requests.json()?
A: response.text returns the raw response as a string, whereas response.json() parses the response
body as JSON (if the content type is JSON).
Q: How do you secure API credentials in Python scripts?
A: Instead of hardcoding API keys or tokens, I stored them in environment variables or external config
files. For example, I used os.environ.get('API_KEY') to load keys. This is safer and avoids leaking secrets
in source code.
Q: How would you handle an API timeout or failure?
A: I used the timeout parameter in requests and wrapped calls in try/except blocks. For resilience, I
implemented retries with exponential backoff so that transient network issues wouldn't fail the whole
script.
Q: How would you optimize Python code that processes thousands of Excel rows?
A: I used pandas for vectorized operations instead of looping row by row. I also processed files in chunks
(chunksize in pandas.read_csv) when working with very large datasets. This improved both performance
and memory efficiency.
Q: If tomorrow the client asks you to migrate from SharePoint to SQL Server, how would you
adapt your script?
A: The core logic for reading/validating Excel files would remain the same. Instead of calling the
SharePoint API, I would use pyodbc or SQLAlchemy to connect to SQL Server and perform bulk inserts.
This shows flexibility in integrating with different backends.
Final Tips
✔ Always explain your projects using the STAR method (Situation, Task, Action, Result). ✔ Show
confidence in describing Python basics (data types, error handling, file handling). ✔ Be clear about tools
you used (pandas, requests, sqlite3, openpyxl). ✔ Mention challenges and how you solved them —
interviewers love problem■solving stories. ✔ If you don’t know something, explain how you would
approach finding the solution.
