请求编写高效Python代码：SQL结果转带表头DataFrame并导出CSV

Hey there! As someone who’s fumbled through Python-SQL connections as a newbie, let me help you wrap this up cleanly and efficiently. Let’s break down what you need to do: add column headers, handle data types, and export to CSV—with both a fix for your existing code and a more efficient approach tailored for beginners.

Option 1: Tweak Your Existing Code #

You’re already halfway there! The missing pieces are grabbing column names from your cursor and ensuring data types are converted to native Python formats. Here’s how to adjust your code:

import pyodbc
import pandas as pd

# Your existing connection & query code
cnxn = pyodbc.connect("DSN=acc_DB")
cursor = cnxn.cursor()
cursor.execute("select top 10 * from Table_XX")
rows = cursor.fetchall()

# Step 1: Pull column names from the cursor's metadata
column_names = [desc[0] for desc in cursor.description]

# Step 2: Create DataFrame with proper column headers
df = pd.DataFrame(rows, columns=column_names)

# Step 3: Convert pyodbc-specific types (like Decimal or datetime) to native Python types
# This ensures your CSV doesn't have weird type labels
df = df.apply(lambda x: x.astype(str) if x.dtype == 'object' else x)
# For numeric columns specifically, you can use:
# df = df.apply(pd.to_numeric, errors='ignore')

# Step 4: Export to CSV (skip the index to keep the output clean)
df.to_csv('query_results.csv', index=False, encoding='utf-8')

Option 2: More Efficient Approach (Recommended) #

Instead of manually fetching rows, let pandas handle the heavy lifting with pd.read_sql_query. This method cuts down on boilerplate, automatically handles column names and data type conversion, and is faster for larger datasets—perfect for boosting your code efficiency:

import pyodbc
import pandas as pd

cnxn = pyodbc.connect("DSN=acc_DB")
query = "select top 10 * from Table_XX"

# Let pandas read the query directly (no manual fetching needed!)
df = pd.read_sql_query(query, cnxn)

# Export to CSV (same clean output as before)
df.to_csv('query_results.csv', index=False, encoding='utf-8')

Why This Is Better: #

• Less code: No need to manually extract columns or fetch rows—pandas does all the tedious work.
• Smarter type handling: Pandas automatically converts pyodbc’s specialized types (like Decimal or datetime) into formats that play nicely with CSV exports.
• Faster execution: Pandas uses optimized database reading logic, which is more efficient than manual fetchall for bigger datasets.

Quick Tips: #

• Use index=False in to_csv to avoid adding an extra row index column to your CSV.
• Adjust encoding='utf-8' if you’re working with non-English characters (e.g., encoding='gbk' for Chinese text).
• If you need to tweak specific column types later, use df['your_column'] = df['your_column'].astype(int) (or float, str) to force a format.

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