如何将SQL数据读取至Python DataFrame？已完成部分步骤求指导

Hey Lynn, nice work getting the database connection sorted out! Let's tweak your code to make sure it smoothly loads your SQL data into a pandas DataFrame. Here are the key fixes and explanations:

First, Fix the SQL Query Syntax #

Your table name Table 1 includes a space, which SQL Server won't interpret correctly by default. You need to wrap it in square brackets [Table 1] to avoid a syntax error.

Second, Capture the Result as a DataFrame #

The pd.read_sql_query() function directly returns a pandas DataFrame—you just need to assign it to a variable so you can interact with the data later.

Don’t Forget the pyodbc Import #

I noticed your code uses pyodbc.connect() but doesn’t show the import statement. Make sure to add that at the top of your script, otherwise it’ll throw an error.

Corrected Full Code #

import pandas as pd
import pyodbc  # Critical import you were missing!

# Establish the database connection
Cap = pyodbc.connect(
    'Driver={SQL Server};'
    'Server=Test\SQLTest;'
    'Database=Cap;'
    'Trusted_Connection=yes;'
)

# Read SQL results directly into a DataFrame
df = pd.read_sql_query("SELECT dbo.Catalogs_ID_History$.Location FROM [Table 1]", Cap)

# Test it out by viewing the first 5 rows
print(df.head())

# Clean up: close the connection when you're done with it
Cap.close()

Extra Pro Tips #

• For larger datasets, consider using pd.read_sql() instead—it works with both queries and table names, and has built-in optimizations for bigger loads.
• Use a context manager (with block) to auto-close your connection, so you don’t have to remember to call Cap.close() manually:

  import pandas as pd
  import pyodbc
  
  with pyodbc.connect(
      'Driver={SQL Server};'
      'Server=Test\SQLTest;'
      'Database=Cap;'
      'Trusted_Connection=yes;'
  ) as Cap:
      df = pd.read_sql_query("SELECT dbo.Catalogs_ID_History$.Location FROM [Table 1]", Cap)
      print(df.head())
  # Connection closes automatically when exiting the 'with' block
  

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