INTERFACING PYTHON WITH MYSQL - INSERT RECORDS

# Python program to insert records into MySQL database

import mysql.connector
from datetime import datetime

def connect_database():
    """Establish connection to MySQL database"""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='library',
            user='root',
            password='password'
        )
        return connection
    except mysql.connector.Error as error:
        print(f"Error connecting to MySQL: {error}")
        return None

def create_table():
    """Create books table if it doesn't exist"""
    connection = connect_database()
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS books (
                book_id INT AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(100) NOT NULL,
                author VARCHAR(50) NOT NULL,
                genre VARCHAR(30),
                price DECIMAL(8,2),
                publication_date DATE,
                isbn VARCHAR(20) UNIQUE
            )
        """)
        
        connection.commit()
        print("Table 'books' created successfully!")
        
    except mysql.connector.Error as error:
        print(f"Error creating table: {error}")
    
    finally:
        cursor.close()
        connection.close()

def insert_single_record():
    """Insert a single record into books table"""
    connection = connect_database()
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        print("\n--- Insert New Book ---")
        title = input("Enter book title: ")
        author = input("Enter author name: ")
        genre = input("Enter genre: ")
        price = float(input("Enter price: "))
        pub_date = input("Enter publication date (YYYY-MM-DD): ")
        isbn = input("Enter ISBN: ")
        
        # Insert query
        query = """
            INSERT INTO books (title, author, genre, price, publication_date, isbn)
            VALUES (%s, %s, %s, %s, %s, %s)
        """
        values = (title, author, genre, price, pub_date, isbn)
        
        cursor.execute(query, values)
        connection.commit()
        
        print(f"\nRecord inserted successfully! Book ID: {cursor.lastrowid}")
        
    except mysql.connector.Error as error:
        print(f"Error inserting record: {error}")
        connection.rollback()
    except ValueError:
        print("Invalid input! Please enter correct data types.")
    
    finally:
        cursor.close()
        connection.close()

def insert_multiple_records():
    """Insert multiple records at once"""
    connection = connect_database()
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        # Sample book data
        books_data = [
            ('The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 299.99, '1925-04-10', '978-0-7432-7356-5'),
            ('To Kill a Mockingbird', 'Harper Lee', 'Fiction', 350.00, '1960-07-11', '978-0-06-112008-4'),
            ('1984', 'George Orwell', 'Dystopian', 275.50, '1949-06-08', '978-0-452-28423-4'),
            ('Pride and Prejudice', 'Jane Austen', 'Romance', 320.00, '1813-01-28', '978-0-14-143951-8'),
            ('The Catcher in the Rye', 'J.D. Salinger', 'Fiction', 280.75, '1951-07-16', '978-0-316-76948-0')
        ]
        
        query = """
            INSERT INTO books (title, author, genre, price, publication_date, isbn)
            VALUES (%s, %s, %s, %s, %s, %s)
        """
        
        cursor.executemany(query, books_data)
        connection.commit()
        
        print(f"\n{cursor.rowcount} records inserted successfully!")
        
    except mysql.connector.Error as error:
        print(f"Error inserting multiple records: {error}")
        connection.rollback()
    
    finally:
        cursor.close()
        connection.close()

def display_all_books():
    """Display all books in the table"""
    connection = connect_database()
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        cursor.execute("SELECT * FROM books ORDER BY book_id")
        records = cursor.fetchall()
        
        if records:
            print("\n--- All Books ---")
            print("ID\tTitle\t\t\tAuthor\t\t\tGenre\t\tPrice\tDate\t\tISBN")
            print("-" * 100)
            for record in records:
                print(f"{record[0]}\t{record[1][:20]:<20}\t{record[2][:15]:<15}\t{record[3]:<10}\t{record[4]}\t{record[5]}\t{record[6]}")
        else:
            print("No books found in the database.")
            
    except mysql.connector.Error as error:
        print(f"Error displaying books: {error}")
    
    finally:
        cursor.close()
        connection.close()

def search_books():
    """Search books by different criteria"""
    connection = connect_database()
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        print("\nSearch books by:")
        print("1. Author")
        print("2. Genre")
        print("3. Price range")
        
        choice = input("Enter your choice (1-3): ")
        
        if choice == '1':
            author = input("Enter author name: ")
            cursor.execute("SELECT * FROM books WHERE author LIKE %s", (f"%{author}%",))
        elif choice == '2':
            genre = input("Enter genre: ")
            cursor.execute("SELECT * FROM books WHERE genre = %s", (genre,))
        elif choice == '3':
            min_price = float(input("Enter minimum price: "))
            max_price = float(input("Enter maximum price: "))
            cursor.execute("SELECT * FROM books WHERE price BETWEEN %s AND %s", (min_price, max_price))
        else:
            print("Invalid choice!")
            return
        
        results = cursor.fetchall()
        
        if results:
            print("\n--- Search Results ---")
            print("ID\tTitle\t\t\tAuthor\t\t\tGenre\t\tPrice")
            print("-" * 70)
            for record in results:
                print(f"{record[0]}\t{record[1][:20]:<20}\t{record[2][:15]:<15}\t{record[3]:<10}\t{record[4]}")
        else:
            print("No books found matching the criteria.")
            
    except mysql.connector.Error as error:
        print(f"Error searching books: {error}")
    except ValueError:
        print("Invalid input! Please enter correct data types.")
    
    finally:
        cursor.close()
        connection.close()

# Main program
def main():
    print("=== MySQL Database - Insert Records ===")
    
    # Create table
    create_table()
    
    while True:
        print("\n--- Menu ---")
        print("1. Insert single book")
        print("2. Insert multiple books (sample data)")
        print("3. Display all books")
        print("4. Search books")
        print("5. Exit")
        
        choice = input("\nEnter your choice (1-5): ")
        
        if choice == '1':
            insert_single_record()
        elif choice == '2':
            insert_multiple_records()
        elif choice == '3':
            display_all_books()
        elif choice == '4':
            search_books()
        elif choice == '5':
            print("Thank you for using the Library Management System!")
            break
        else:
            print("Invalid choice! Please try again.")

if __name__ == "__main__":
    main()