Python & Relational Databases – Connecting to MS SQL Server and PostgreSQL for CRUD Operations
Quick Answer: Relational databases store structured data in tables. Python connects to enterprise databases like MS SQL Server and PostgreSQL using libraries like pyodbc and psycopg2 to perform Create, Read, Update, and Delete operations safely.
Imagine Priya manages stock for three busy grocery shops in Bengaluru.
Her shops sell over 2,000 items daily, and storing records in text files leads to data clashes and inventory errors.
How do we connect a Python backend to an enterprise database to manage this data securely?
1. Why Do We Need Relational Databases in Python?
The problem with flat files
We could store grocery orders in simple text or CSV files.
Example 1: Storing stock data in a text file
This approach works for small scripts, but fails under concurrent access.
with open ("inventory.txt", "a") as file:
file.write ("Rice, 55.0, 100\n")
Expected output
Data appended to text file
This approach has major limitations when businesses scale.
- No concurrency control, meaning two users updating stock simultaneously will overwrite data.
- No built-in data validation or types, allowing incorrect prices or negative quantities.
- Searching through thousands of lines in a text file is extremely slow.
We need an enterprise relational database management system (RDBMS) like MS SQL Server or PostgreSQL.
The solution: connecting Python to an RDBMS
Relational Database = a structured data store organized into tables with rows and columns.
CRUD = Create, Read, Update, and Delete – the four basic database operations.
Driver = a library that allows Python to communicate with a specific database server.
Figure 1: Python applications connect through drivers to execute transactional SQL queries.
2. Connecting and Executing CRUD Operations in Python
Let us write a complete runnable Python script demonstrating how to connect to a PostgreSQL database using psycopg2 and perform CRUD operations for Priya's shop.
Example 2: Complete CRUD operations with PostgreSQL
This code establishes a secure connection, inserts a product, reads it, updates price, and deletes the record.
import psycopg2
# Establish database connection
connection = psycopg2.connect (
dbname="grocery_db",
user="priya_admin",
password="secret_pass",
host="localhost",
port="5432"
)
cursor = connection.cursor ()
# 1. CREATE: Insert a new product
cursor.execute (
"INSERT INTO products (name, price) VALUES (%s, %s);",
("Basmati Rice", 75.0 )
)
connection.commit ()
# 2. READ: Query products
cursor.execute ("SELECT id, name, price FROM products;")
rows = cursor.fetchall ()
for row in rows:
print(row)
# 3. UPDATE: Modify product price
cursor.execute (
"UPDATE products SET price = %s WHERE name = %s;",
(80.0 , "Basmati Rice")
)
connection.commit ()
# 4. DELETE: Remove a product
cursor.execute (
"DELETE FROM products WHERE name = %s;",
("Basmati Rice",)
)
connection.commit ()
# Close connection resources
cursor.close ()
connection.close ()
Expected output
(1, 'Basmati Rice', 75.0)
Notice the use of parameter placeholders (%s) instead of string formatting.
Always commit transactions to save changes permanently to the database server.
| Feature | Flat Files (CSV/TXT) | Relational Databases (SQL) |
|---|---|---|
| Concurrency | Not supported (file locks collide) | Fully supported with transaction isolation |
| Data Integrity | None (any text allowed) | Enforced via data types and constraints |
| Query Speed | Slow linear search | Fast indexed lookups |
The important terms to remember
- Connection = an active communication channel between Python and the database.
- Cursor = an object used to execute SQL queries and fetch results.
- Parameterized Query = passing variables safely to prevent SQL injection attacks.
Design tip: Always use context managers (with statements) or try-finally blocks to guarantee database cursors and connections are closed properly.
3. Common Mistakes to Avoid
1. Concatenating user input directly into SQL strings
Building queries with string formatting allows attackers to inject malicious SQL commands.
Better approach: Always use parameterized queries with tuple arguments (e.g., cursor.execute("SELECT * FROM users WHERE name = %s", (name,))).
4. Interview Questions & Answers
Question 1: What is the difference between fetchone() and fetchall() in database connectors?
What the interviewer expects: A clear explanation of memory efficiency and row retrieval.
Strong answer: "fetchone() retrieves a single row from the result set, which is memory efficient for large datasets. fetchall() retrieves all matching rows into a Python list at once, which is suitable for smaller result sets like Priya's daily product list."
What interviewers expect at different levels
- Junior developer: Knows how to write basic SQL queries and execute them using Python drivers.
- Senior developer: Understands connection pooling, transaction management, and security best practices.
- Technical architect: Designs database schemas, indexing strategies, and multi-tenant architectures.
5. Practice Exercises
Beginner: Employee database table
Connect to a local database and write a Python script that creates an employees table and inserts three employee records.
Expected result: Three records inserted successfully and verified via a SELECT query.
Intermediate: Inventory update function
Write a Python function that takes a product ID and a new stock quantity, then updates the database record safely.
Hint: Use parameterized queries and commit the transaction.
6. Frequently Asked Questions (FAQ)
Which library should I use for MS SQL Server in Python?
pyodbc is the standard and most widely used library for connecting Python to MS SQL Server using ODBC drivers.
Why do we need to call connection.commit()?
Relational databases run transactions by default; changes like INSERT or UPDATE are temporary until explicitly committed.
7. Next Steps
Now that you can connect Python to relational databases, you are ready to learn NoSQL database integration with MongoDB in the next article.