Skip to content
TheChatArchiveThe Chat Archive
gpt-4-1106-preview· OpenAIHugging Face Arena Human Preference 55K

How to write clean Python code for database query

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with gpt-4-0613’s answer.
  1. User

    How to write clean Python code for database query

  2. Assistant

    Writing clean Python code for database queries involves several best practices, which can include using parameterized queries to avoid SQL injection, employing context managers to handle database connections, and organizing code for readability and maintainability. Below is a step-by-step guide to writing clean Python code for a database query using the sqlite3 module, which is built into Python and provides a lightweight disk-based database that doesn't require a separate server process.

    1. Import the necessary modules:
    import sqlite3
    
    1. Use context managers to handle the database connection and cursor: Context managers ensure that resources are properly managed, and the database connection is closed after the operation, even if an error occurs.
    # Assume 'example.db' is your database file
    DATABASE = 'example.db'
    
    with sqlite3.connect(DATABASE) as conn:
        cursor = conn.cursor()
        # Perform database operations using cursor
    
    1. Write parameterized queries: To avoid SQL injection and to separate code from data, use parameterized queries instead of string formatting.
    query = "SELECT * FROM users WHERE username = ?"
    parameters = ('john_doe',)
    cursor.execute(query, parameters)
    
    1. Fetch results: After executing the query, you can fetch the results using fetchone(), fetchmany(), or fetchall().
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    
    1. Handle exceptions: Use try-except blocks to handle any potential exceptions that may occur during database operations.
    try:
        with sqlite3.connect(DATABASE) as conn:
            cursor = conn.cursor()
            query = "SELECT * FROM users WHERE username = ?"
            parameters = ('john_doe',)
            cursor.execute(query, parameters)
            rows = cursor.fetchall()
            for row in rows:
                print(row)
    except sqlite3.Error as e:
        print(f"An error occurred: {e}")
    
    1. Use functions to encapsulate logic: Define functions to encapsulate database operations. This makes the code reusable and easier to maintain.
    def get_user_by_username(username):
        try:
            with sqlite3.connect(DATABASE) as conn:
                cursor = conn.cursor()
                query = "SELECT * FROM users WHERE username = ?"
                cursor.execute(query, (username,))
                return cursor.fetchall()
        except sqlite3.Error as e:
            print(f"An error occurred
    

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.