🔄 PostgreSQL CRUD Operations

🎯 Darsning Maqsadi

PostgreSQL'da CRUD (Create, Read, Update, Delete) operatsiyalarini o'rganish va Python bilan amalga oshirish.

📋 Dars Rejasi

CREATE - Ma'lumot qo'shish
READ - Ma'lumotlarni o'qish
UPDATE - Ma'lumotlarni yangilash
DELETE - Ma'lumotlarni o'chirish
Python bilan amalga oshirish

➕ CREATE - Ma'lumot Qo'shish

📝 INSERT So'rovlari

-- Bitta qator qo'shish INSERT INTO users (username, email, first_name, last_name) VALUES ('john_doe', '[email protected]', 'John', 'Doe'); -- Ko'p qator qo'shish INSERT INTO users (username, email, first_name, last_name) VALUES ('jane_smith', '[email protected]', 'Jane', 'Smith'), ('bob_wilson', '[email protected]', 'Bob', 'Wilson'), ('alice_brown', '[email protected]', 'Alice', 'Brown'); -- Ma'lum ustunlarni qo'shish INSERT INTO posts (title, content, author_id) VALUES ('My First Post', 'This is my first blog post!', 1); -- DEFAULT qiymatlar bilan INSERT INTO users (username, email) VALUES ('new_user', '[email protected]'); -- is_active va created_at avtomatik to'ldiriladi

🔧 Python bilan CREATE

import psycopg2 def create_user(username, email, first_name, last_name): try: connection = psycopg2.connect( host="localhost", database="my_blog", user="postgres", password="your_password" ) cursor = connection.cursor() # Ma'lumot qo'shish cursor.execute(""" INSERT INTO users (username, email, first_name, last_name) VALUES (%s, %s, %s, %s) """, (username, email, first_name, last_name)) connection.commit() print(f"Foydalanuvchi {username} muvaffaqiyatli qo'shildi!") except Error as e: print(f"Xatolik: {e}") finally: if connection: cursor.close() connection.close() # Funksiyani chaqirish create_user('test_user', '[email protected]', 'Test', 'User')

📖 READ - Ma'lumotlarni O'qish

🔍 SELECT So'rovlari

-- Barcha ma'lumotlarni olish
SELECT * FROM users;

-- Ma'lum ustunlarni olish
SELECT username, email, created_at FROM users;

-- Shartli qidirish
SELECT * FROM users WHERE is_active = TRUE;

-- Guruhlash va hisoblash
SELECT 
    COUNT(*) as total_users,
    COUNT(CASE WHEN is_active = TRUE THEN 1 END) as active_users
FROM users;

-- Tartiblash
SELECT * FROM users ORDER BY created_at DESC;

-- Cheklash
SELECT * FROM users LIMIT 10 OFFSET 0;
            

🔗 JOIN So'rovlari

-- INNER JOIN
SELECT u.username, p.title, p.created_at
FROM users u
INNER JOIN posts p ON u.id = p.author_id;

-- LEFT JOIN
SELECT u.username, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.author_id
GROUP BY u.id, u.username;

-- Murakkab so'rov
SELECT 
    u.username,
    p.title,
    c.content as comment,
    c.created_at as comment_date
FROM users u
INNER JOIN posts p ON u.id = p.author_id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.status = 'published'
ORDER BY c.created_at DESC;
            

✏️ UPDATE - Ma'lumotlarni Yangilash

🔄 UPDATE So'rovlari

-- Bitta qatorni yangilash
UPDATE users 
SET first_name = 'Johnny', last_name = 'Smith'
WHERE username = 'john_doe';

-- Ko'p qatorlarni yangilash
UPDATE users 
SET is_active = FALSE
WHERE created_at < '2024-01-01';

-- Shartli yangilash
UPDATE posts 
SET view_count = view_count + 1
WHERE id = 1;

-- Ma'lum ustunni NULL qilish
UPDATE users 
SET bio = NULL
WHERE username = 'old_user';

-- Murakkab yangilash
UPDATE posts 
SET status = 'published', updated_at = CURRENT_TIMESTAMP
WHERE author_id = 1 AND status = 'draft';
            

🔧 Python bilan UPDATE

def update_user_profile(user_id, first_name, last_name, bio): try: connection = psycopg2.connect( host="localhost", database="my_blog", user="postgres", password="your_password" ) cursor = connection.cursor() # Ma'lumotlarni yangilash cursor.execute(""" UPDATE users SET first_name = %s, last_name = %s, bio = %s, updated_at = CURRENT_TIMESTAMP WHERE id = %s """, (first_name, last_name, bio, user_id)) connection.commit() if cursor.rowcount > 0: print(f"Foydalanuvchi {user_id} muvaffaqiyatli yangilandi!") else: print("Foydalanuvchi topilmadi!") except Error as e: print(f"Xatolik: {e}") finally: if connection: cursor.close() connection.close() # Funksiyani chaqirish update_user_profile(1, 'John', 'Doe', 'Software Developer')

🗑️ DELETE - Ma'lumotlarni O'chirish

⚠️ DELETE So'rovlari

-- Bitta qatorni o'chirish
DELETE FROM users WHERE username = 'test_user';

-- Shartli o'chirish
DELETE FROM posts WHERE status = 'draft' AND created_at < '2024-01-01';

-- Barcha ma'lumotlarni o'chirish (EHTIYOT!)
DELETE FROM comments;

-- CASCADE o'chirish (bog'langan ma'lumotlar ham o'chiriladi)
DELETE FROM users WHERE id = 1;
-- Bu foydalanuvchining barcha postlari va kommentariyalari ham o'chiriladi

-- Soft delete (ma'lumotni o'chirmasdan, faqat belgilash)
UPDATE users SET is_active = FALSE WHERE id = 1;
            

🔧 Python bilan DELETE

def delete_user(user_id): try: connection = psycopg2.connect( host="localhost", database="my_blog", user="postgres", password="your_password" ) cursor = connection.cursor() # Avval bog'langan ma'lumotlarni tekshirish cursor.execute("SELECT COUNT(*) FROM posts WHERE author_id = %s", (user_id,)) post_count = cursor.fetchone()[0] if post_count > 0: print(f"Ushbu foydalanuvchining {post_count} ta posti bor!") choice = input("Davom etishni xohlaysizmi? (y/n): ") if choice.lower() != 'y': return # Foydalanuvchini o'chirish cursor.execute("DELETE FROM users WHERE id = %s", (user_id,)) connection.commit() if cursor.rowcount > 0: print(f"Foydalanuvchi {user_id} muvaffaqiyatli o'chirildi!") else: print("Foydalanuvchi topilmadi!") except Error as e: print(f"Xatolik: {e}") finally: if connection: cursor.close() connection.close() # Funksiyani chaqirish delete_user(1)

🔧 Kengaytirilgan CRUD Operatsiyalar

📊 Murakkab So'rovlar

-- Subquery bilan
SELECT * FROM users 
WHERE id IN (
    SELECT author_id FROM posts 
    WHERE status = 'published'
);

-- EXISTS bilan
SELECT u.username FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p 
    WHERE p.author_id = u.id AND p.status = 'published'
);

-- Window functions
SELECT 
    username,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as row_num,
    LAG(created_at) OVER (ORDER BY created_at) as prev_user_date
FROM users;

-- CTE (Common Table Expression)
WITH user_stats AS (
    SELECT 
        author_id,
        COUNT(*) as post_count,
        MAX(created_at) as last_post
    FROM posts 
    GROUP BY author_id
)
SELECT u.username, us.post_count, us.last_post
FROM users u
INNER JOIN user_stats us ON u.id = us.author_id;
            

🔄 Batch Operations

def batch_insert_users(users_data): try: connection = psycopg2.connect( host="localhost", database="my_blog", user="postgres", password="your_password" ) cursor = connection.cursor() # Ko'p ma'lumotlarni bir vaqtda qo'shish cursor.executemany(""" INSERT INTO users (username, email, first_name, last_name) VALUES (%s, %s, %s, %s) """, users_data) connection.commit() print(f"{len(users_data)} ta foydalanuvchi qo'shildi!") except Error as e: print(f"Xatolik: {e}") finally: if connection: cursor.close() connection.close() # Ma'lumotlar users_data = [ ('user1', '[email protected]', 'User', 'One'), ('user2', '[email protected]', 'User', 'Two'), ('user3', '[email protected]', 'User', 'Three') ] batch_insert_users(users_data)

💡 CRUD Best Practices

✅ Yaxshi Amaliyotlar

Prepared Statements - SQL injection'dan himoya
Transactions - Ma'lumotlar izchilligi
Error Handling - Xatoliklarni boshqarish
Connection Pooling - Samaradorlik
Indexing - Tez qidirish

⚠️ Ehtiyot Bo'lish

DELETE - Ma'lumotlarni yo'qotish
UPDATE - Noto'g'ri yangilash
Performance - Sekin so'rovlar
Security - Xavfsizlik
Backup - Nusxa olish

🔒 Xavfsiz CRUD Operatsiyalar

import psycopg2 from psycopg2.extras import RealDictCursor class DatabaseManager: def __init__(self): self.connection = None def connect(self): try: self.connection = psycopg2.connect( host="localhost", database="my_blog", user="postgres", password="your_password", cursor_factory=RealDictCursor ) return True except Error as e: print(f"Ulanish xatoligi: {e}") return False def execute_query(self, query, params=None): try: cursor = self.connection.cursor() cursor.execute(query, params) return cursor.fetchall() except Error as e: print(f"So'rov xatoligi: {e}") return None def execute_update(self, query, params=None): try: cursor = self.connection.cursor() cursor.execute(query, params) self.connection.commit() return cursor.rowcount except Error as e: print(f"Yangilash xatoligi: {e}") self.connection.rollback() return 0 def close(self): if self.connection: self.connection.close() # Ishlatish db = DatabaseManager() if db.connect(): # Xavfsiz so'rov users = db.execute_query( "SELECT * FROM users WHERE username = %s", ('john_doe',) ) print(users) db.close()

📝 Xulosa

🎯 O'rganilgan Mavzular

CREATE - Ma'lumot qo'shish (INSERT)
READ - Ma'lumotlarni o'qish (SELECT)
UPDATE - Ma'lumotlarni yangilash (UPDATE)
DELETE - Ma'lumotlarni o'chirish (DELETE)
Python bilan amalga oshirish
Best practices va xavfsizlik

✅ CRUD Afzalliklari

Ma'lumotlarni to'liq boshqarish
Moslashuvchan so'rovlar
Tez va samarali
Xavfsiz operatsiyalar

💡 Keyingi Qadamlar

ORM (SQLAlchemy) o'rganish
Database optimization
Advanced queries
Performance monitoring

🏆 Amaliy Mashq

To'liq CRUD funksiyalariga ega blog tizimi yarating!