如何通过SQL查询一次性获取数据库全部数据并导出至CSV文件

Hey there! Let's break down your two tech questions with practical, developer-friendly solutions:

If you're targeting a single table, the simplest approach is to use the SQL wildcard *—it pulls every column and row from the table in one query:

SELECT * FROM your_table_name;

A few quick notes to keep in mind:

• Columns will be returned in the exact order they’re defined in your table schema.
• If you need data from multiple tables across the entire database, there’s no single SQL command to fetch everything at once. You’ll either need to loop through each table and run SELECT * on each, or use database-specific export tools (like mysqldump for MySQL, pg_dump for PostgreSQL) if you don’t need to handle it programmatically.

Your current code only extracts the name column, but we can tweak it to automatically grab every column (including headers) using QSqlQuery’s built-in record methods, plus Python’s csv module to handle CSV formatting cleanly.

Here’s a refined version of your function:

import csv

def create_csv():
    query = QSqlQuery(db=db)
    # Important: Replace "your_table_name" with your actual table name (not database name!)
    query.prepare("SELECT * FROM your_table_name")
    
    # Add basic error handling for the query
    if not query.exec():
        print(f"Query failed: {query.lastError().text()}")
        return
    
    # Extract column headers from the query result
    result_record = query.record()
    total_columns = result_record.count()
    headers = [result_record.fieldName(col_index) for col_index in range(total_columns)]
    
    # Write data to CSV file
    with open("exported_data.csv", "w", newline="", encoding="utf-8") as csv_file:
        csv_writer = csv.writer(csv_file)
        # First write the header row
        csv_writer.writerow(headers)
        
        # Loop through every row and extract all column values
        while query.next():
            row_data = [query.value(col_index) for col_index in range(total_columns)]
            csv_writer.writerow(row_data)
    
    print("CSV file created successfully with all columns!")

Key improvements here: #

• Automatic header detection: We use result_record.count() to get the total number of columns, then loop through each index to fetch column names with fieldName()—no more hardcoding column names!
• Full row extraction: Instead of targeting a single column index, we loop through all indices for each row to grab every value.
• Robust CSV handling: Python’s csv module takes care of edge cases like commas in data, proper line endings, and encoding—way less error-prone than building CSV strings manually.
• Error checking: Added a check for query execution failures, so you can debug if something goes wrong with the SQL call.

Quick reminder: Make sure you replace "your_table_name" with the actual name of your table (your original code used "database_name", which is likely a mix-up between database and table names).

内容的提问来源于stack exchange，提问作者Nullschall