Why use pandas.to_numeric with errors set to coerce?

It converts valid numeric values while replacing invalid text with NaN, allowing statistical calculations to continue without treating bad text as process measurements.

Read SQL Server Data with Python, pyodbc & pandas

Quick answer

Connect to SQL Server with pyodbc.connect(), execute a parameterized SELECT TOP (?) query through a cursor, collect column names from cursor.description, and build a pandas DataFrame with DataFrame.from_records(). Then use head(), tail(), to_numeric(), aggregation methods and value_counts() to inspect furnace behavior.

The query is read-only and ordered newest-first by date and time.
Parameter binding supplies the record limit without SQL string interpolation.
Invalid numeric text becomes NaN, so it does not corrupt mean, minimum, maximum or standard deviation.

Connect Python to SQL Server and Load Data into pandas

This tutorial answers the common search intent “read SQL Server data with Python.” It uses pyodbc for the connection and SELECT query, converts cursor results into a pandas DataFrame, and then calculates useful process statistics and category counts.

1. Connect Python to SQL Server and Read Data

This Code 03 exercise reads the latest furnace event records and displays basic process statistics. A SQL SELECT statement retrieves data without modifying the table. The pyodbc driver handles communication with Microsoft SQL Server, and pandas holds the returned records in a DataFrame for inspection and analysis.

SQL SELECT • pandas Read Architecture

SQL Server → pyodbc → pandas DataFrame

Read-only reporting starts with an ODBC connection, executes SELECT, loads cursor rows into pandas and then performs inspection, statistics and grouped counts.

Connect

Open SQF_DB using pyodbc and the installed ODBC driver.

connection = pyodbc.connect(...)

SELECT Query

Request the newest furnace rows without modifying the table.

SELECT TOP (...) FROM dbo.tblEvent

Cursor Result

Fetch rows and column metadata from the SQL result set.

rows = cursor.fetchall() columns = [...]

DataFrame

Create a tabular pandas object for analysis.

df = pd.DataFrame(rows, columns=columns)

Analyze

Inspect newest/oldest samples, numeric statistics and grouped counts.

df.describe() df.value_counts()

Engineering View

Present values for verification and later reporting.

print(df.head()) process statistics

pyodbcSQL communication

SELECTRead-only query

pandasTabular analysis

DataFrameEngineering data view

Architecture:SQL Server→pyodbc→SELECT→Cursor→DataFrame→Analyze

Code-rendered diagram: the data remains read-only in this workflow; pandas operates on the returned result set in Python memory.

The four operating steps are: connect to SQF_DB, select the newest records, display newest and oldest samples, and calculate numeric statistics plus grouped record counts.

Lab Manual Index: 8 Guided Exercises

Every section of this tutorial carries its own lab manual — objective, procedure, a line-by-line explanation, copy-paste practice code, the exact console output to expect, a checkpoint and a troubleshooting table. Work through them in order; each lab builds on the checkpoint before it. Total bench time is about 61 minutes.

2. Requirements and Database Assumptions

Python 3.10 or later.
pandas and pyodbc installed in the same Python environment.
Microsoft ODBC Driver 17 for SQL Server installed on Windows.
Network and Windows permission to reach SOFTWELL\WINCC.
Database SQF_DB with table dbo.tblEvent and the selected columns.
Code 02 or another source has already inserted furnace-event data.

install_requirements.cmd

python -m pip install --upgrade pip
python -m pip install pandas pyodbc

Security note:Trusted_Connection=yes uses the Windows identity running the Python process. Give that identity only the database permissions it needs—normally SELECT permission for this reader.

3. Create the SQL Server Connection

01_connection.py

import pyodbc

SQL_SERVER = r"SOFTWELL\WINCC"
DATABASE = "SQF_DB"
DRIVER = "{ODBC Driver 17 for SQL Server}"


def create_connection():
    return pyodbc.connect(
        f"Driver={DRIVER};Server={SQL_SERVER};Database={DATABASE};"
        "Trusted_Connection=yes;"
    )

The raw-string server name preserves the backslash in the named SQL Server instance. The braces around the driver name are part of ODBC connection-string syntax. Keeping connection creation in a function makes testing and later configuration changes easier.

Lab Manual 3 — Create and Prove the SQL Server Connection

7 min

Objective

Prove that Python can open the exact SQL Server instance and database you intend to read. Every later lab assumes this one printed the server name you expected.

Procedure

Open ODBC Data Sources (64-bit), go to the Drivers tab and copy the driver name exactly as Windows shows it.
Edit SQL_SERVER, DATABASE and DRIVER to match your own lab machine.
Save the connection function as 01_connection.py.
Add the test block below and run python 01_connection.py.

What each line does

Code	Meaning in this lab
r"SOFTWELL\WINCC"	Raw string. Without the leading `r`, Python treats `\W` as an escape sequence and the named instance breaks.
"{ODBC Driver 17 for SQL Server}"	The braces are part of ODBC connection-string syntax, so here they are written into the constant itself.
f"Driver={DRIVER};Server=..."	An f-string builds the connection string. Only fixed constants are inserted — never user input.
Trusted_Connection=yes	Uses the Windows account running the script, so no password sits in the source file.
def create_connection()	Keeping connection creation in one function means a later change of server or driver touches a single place.

Copy-paste practice code

01_connection_test.py

import pyodbc

print("Installed ODBC drivers:")
for driver in pyodbc.drivers():
    print("  -", driver)

with create_connection() as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT @@SERVERNAME, DB_NAME(), SYSTEM_USER")
    server_name, database_name, login_name = cursor.fetchone()

    print("Server  :", server_name)
    print("Database:", database_name)
    print("Login   :", login_name)

Paste this below the create_connection() function from Section 3 and run the file as it is. Nothing is read from tblEvent yet.

Expected result

console output

Installed ODBC drivers:
  - SQL Server
  - ODBC Driver 17 for SQL Server
Server  : SOFTWELL\WINCC
Database: SQF_DB
Login   : SOFTWELL\Trainer

Checkpoint

Pass when: The server and database names printed by SQL Server match your constants, and the login shown is the Windows account that should hold read access.

If it fails

Symptom	What to check
IM002 — data source name not found	Driver string misspelled, or 32-bit driver installed while 64-bit Python is running. Compare with the list the script just printed.
08001 — SQL Server does not exist	Wrong instance name, SQL Server Browser service stopped, or TCP/IP disabled in SQL Server Configuration Manager.
28000 — login failed for user	The Windows account has no SQL login. Ask for a login with SELECT on the training database only.

4. Execute a SQL Server SELECT Query with pyodbc

02_select_query.py

query = """
    SELECT TOP (?)
        [DT], [TM], [SQF_No], [ChargeNo],
        [Event_From], [Event_To],
        [Temp_Set], [Temp_Act],
        [Cp_Set], [Cp_Act],
        [Oil_Set], [Oil_Act],
        [Jacket_Set], [Jacket_Act],
        [Fan_Status]
    FROM [dbo].[tblEvent]
    ORDER BY [DT] DESC, [TM] DESC;
"""

cursor = connection.cursor()
cursor.execute(query, top_n)

TOP (?) limits the result and the driver binds top_n as a parameter. The square brackets protect SQL Server identifiers. Ordering both DT and TM descending places the latest timestamp first.

If multiple records can have identical date and time values, add a unique descending key such as Event_ID DESC to make the order deterministic.

Lab Manual 4 — Execute the Parameterized SELECT TOP Query

8 min

Objective

Run a read-only query that returns a controlled number of rows in a defined order, with the limit passed as a bound parameter rather than formatted into the SQL text.

Procedure

Run the query with top_n = 5 first, so a mistake costs five rows and not a hundred.
Print the raw cursor rows before pandas is involved.
Compare the first and last timestamps with the same query run in SSMS.
Raise top_n to 100 only after the small batch looks right.

What each line does

Code	Meaning in this lab
SELECT TOP (?)	The row limit is a bound parameter. The parentheses around `?` are required by SQL Server for a parameterized TOP.
[DT], [TM], ...	Square brackets protect identifiers that may contain reserved words or unusual characters.
FROM [dbo].[tblEvent]	Schema and table are named explicitly, so the query does not depend on the default schema of your login.
ORDER BY [DT] DESC, [TM] DESC	Both date and time descend, so the newest event is row one. Sorting by date alone leaves minutes unordered.
cursor.execute(query, top_n)	The value is passed separately from the SQL text. Never build the limit with an f-string.

Copy-paste practice code

02_select_preview.py

top_n = 5

with create_connection() as connection:
    cursor = connection.cursor()
    cursor.execute(query, top_n)

    print("Columns returned:", [column[0] for column in cursor.description])

    for index, row in enumerate(cursor.fetchall(), start=1):
        print(index, row.DT, row.TM, row.SQF_No, row.Event_To, row.Temp_Act)

pyodbc rows support attribute access, so row.Temp_Act works as well as row[7] and is far easier to read in a lab.

Expected result

console output

Columns returned: ['DT', 'TM', 'SQF_No', 'ChargeNo', 'Event_From', 'Event_To', 'Temp_Set', 'Temp_Act', 'Cp_Set', 'Cp_Act', 'Oil_Set', 'Oil_Act', 'Jacket_Set', 'Jacket_Act', 'Fan_Status']
1 2026-09-19 12:43:00 3 CH-010 Charge Discharged 96.21
2 2026-09-19 12:42:00 3 CH-010 Cooling 148.77
3 2026-09-19 12:41:00 3 CH-010 Cooling 213.05
4 2026-09-19 12:40:00 2 CH-010 Cooling 287.64
5 2026-09-19 12:39:00 2 CH-010 Oil Quenching 402.18

Checkpoint

Pass when: Exactly five rows return, all fifteen column names appear, and the timestamps descend from the newest record.

If it fails

Symptom	What to check
Invalid object name 'dbo.tblEvent'	Connected to the wrong database, or the table sits in a different schema. Print `DB_NAME()` to confirm.
Incorrect syntax near '?'	The parentheses are missing. SQL Server requires `TOP (?)`, not `TOP ?`.
Rows return but the order looks wrong	`DT` or `TM` is stored as text. Text sorts alphabetically, so '9:05' lands after '10:05'. Use proper date and time types.
Identical timestamps swap order between runs	Ties have no defined order. Add a unique descending key such as `Event_ID DESC` as a tie-breaker.

5. Load pyodbc Query Results into a pandas DataFrame

03_build_dataframe.py

columns = [column[0] for column in cursor.description]
rows = cursor.fetchall()
dataframe = pd.DataFrame.from_records(rows, columns=columns)

cursor.description contains metadata for the returned columns; element zero of each description tuple is the column name. fetchall() returns the selected rows, and DataFrame.from_records() combines the rows and names into a labeled table.

This cursor-based approach avoids adding SQLAlchemy solely for the read operation. For very large result sets, retrieve rows in chunks rather than calling fetchall(); this tutorial deliberately limits the query to 100 rows.

Lab Manual 5 — Turn Cursor Results into a pandas DataFrame

7 min

Objective

Convert the raw cursor result into a labelled pandas DataFrame without adding SQLAlchemy, and confirm the shape and data types before any analysis is attempted.

Procedure

Build the DataFrame from the small five-row batch of Lab 4.
Print shape, columns and dtypes.
Note which numeric columns arrived as object rather than float64 — Lab 7 depends on this.

What each line does

Code	Meaning in this lab
cursor.description	Metadata for every returned column. Element zero of each tuple is the column name.
[column[0] for column in ...]	A list comprehension that pulls just the names, in the order SQL returned them.
cursor.fetchall()	Reads every remaining row into memory. Safe here because the query is limited to 100 rows.
pd.DataFrame.from_records(...)	Combines the row tuples with the column names into a labelled table.
columns=columns	Without this argument pandas would number the columns 0 to 14 and every later lookup by name would fail.

Copy-paste practice code

03_inspect_dataframe.py

with create_connection() as connection:
    dataframe = read_event_data(connection, top_n=20)

print("Shape (rows, columns):", dataframe.shape)
print("\nColumns:")
print(list(dataframe.columns))

print("\nData types:")
print(dataframe.dtypes.to_string())

print("\nMissing values per column:")
print(dataframe.isna().sum().to_string())

dtypes is the most useful line in this lab. Any numeric column showing object will need pd.to_numeric() before statistics.

Expected result

console output

Shape (rows, columns): (20, 15)

Columns:
['DT', 'TM', 'SQF_No', 'ChargeNo', 'Event_From', 'Event_To', 'Temp_Set', 'Temp_Act', 'Cp_Set', 'Cp_Act', 'Oil_Set', 'Oil_Act', 'Jacket_Set', 'Jacket_Act', 'Fan_Status']

Data types:
DT             object
TM             object
SQF_No          int64
Event_To       object
Temp_Act      float64
Fan_Status     object

Missing values per column:
DT            0
Temp_Act      0
Fan_Status    0

Checkpoint

Pass when: The row count matches top_n, all fifteen columns carry their SQL names, and you have written down which columns are not yet numeric.

If it fails

Symptom	What to check
Columns are numbered 0 to 14	The `columns=` argument was dropped from `from_records()`.
Shape shows 0 rows	The table is empty for this query. Run the insert practical (Code 02) first.
MemoryError on a larger table	`fetchall()` loads everything at once. Use `fetchmany(1000)` in a loop, or keep the TOP limit.
Numeric column shows dtype object	The SQL column is text, or the batch contains NULLs mixed with numbers. Lab 7 handles this with `to_numeric`.

6. Display Newest and Oldest Samples

04_newest_oldest.py

print("Newest 10 records:")
print(dataframe.head(10).to_string(index=False))

print("\nOldest 10 records:")
print(dataframe.tail(10).iloc[::-1].to_string(index=False))

Because SQL returns newest-first, head(10) displays the ten newest rows. tail(10) selects the oldest portion within the retrieved batch, while iloc[::-1] reverses those ten rows so they print oldest-first. These are the oldest among the selected TOP 100, not necessarily the oldest records in the entire table.

Lab Manual 6 — Display the Newest and Oldest Samples

6 min

Objective

Print the two ends of the retrieved batch in a readable form, and understand exactly which records tail() is showing you.

Procedure

Print the newest ten rows with head(10).
Print the oldest ten with tail(10) and reverse them with iloc[::-1].
Confirm from the timestamps that these are the oldest of the retrieved 100 rows, not the oldest in the table.

What each line does

Code	Meaning in this lab
dataframe.head(10)	The first ten rows. Because SQL returned newest-first, these are the ten newest events.
dataframe.tail(10)	The last ten rows of the retrieved batch — the oldest within TOP 100, not the oldest in the table.
.iloc[::-1]	Reverses those ten rows so they print oldest-first and read like a chart's time axis.
.to_string(index=False)	Prints the whole frame without truncation and hides the pandas index, which is not plant data.

Copy-paste practice code

04_ends_of_batch.py

print("Rows retrieved:", len(dataframe))

print("\nNewest 10 records:")
print(dataframe.head(10).to_string(index=False))

print("\nOldest 10 records (within this batch):")
print(dataframe.tail(10).iloc[::-1].to_string(index=False))

print("\nBatch time span:")
print("  newest:", dataframe.iloc[0]["DT"], dataframe.iloc[0]["TM"])
print("  oldest:", dataframe.iloc[-1]["DT"], dataframe.iloc[-1]["TM"])

The last three lines are the ones worth keeping. They state the time window of the batch, which belongs in any report header you generate later in this series.

Expected result

console output

Rows retrieved: 100

Newest 10 records:
        DT       TM  SQF_No ChargeNo  Event_From         Event_To  Temp_Act Fan_Status
2026-09-19 12:43:00       3   CH-010     Cooling Charge Discharged     96.21        OFF
2026-09-19 12:42:00       3   CH-010     Cooling          Cooling    148.77         ON

Batch time span:
  newest: 2026-09-19 12:43:00
  oldest: 2026-09-19 11:04:00

Checkpoint

Pass when: The newest row's timestamp is later than the oldest row's, and the span is close to 100 minutes for a one-minute logging interval.

If it fails

Symptom	What to check
Output wraps into unreadable columns	The console is too narrow. Widen it, or select fewer columns before calling `to_string()`.
Oldest rows look newer than expected	You are seeing the oldest of TOP 100 only. Raise `NUMBER_OF_RECORDS` or add a date filter in SQL.
An index column appears in the output	`index=False` was omitted from `to_string()`.

7. Calculate Numeric Process Statistics

05_statistics.py

for column, unit in (
    ("Temp_Act", "°C"),
    ("Oil_Act", " bar"),
    ("Cp_Act", ""),
):
    values = pd.to_numeric(dataframe[column], errors="coerce")
    print(
        f"{column}: count={values.count()}, mean={values.mean():.2f}{unit}, "
        f"min={values.min():.2f}{unit}, max={values.max():.2f}{unit}, "
        f"std={values.std():.2f}{unit}"
    )

Statistic	Meaning	Important detail
`count()`	Valid numeric samples	Excludes missing/invalid values converted to NaN
`mean()`	Arithmetic average	Useful summary, but sensitive to outliers
`min()` / `max()`	Observed range	Check against engineering and sensor limits
`std()`	Sample standard deviation	pandas uses `ddof=1` by default

errors="coerce" converts non-numeric text to NaN. It keeps the program running, but the count tells you how many valid samples were actually analyzed. Production systems should also log or flag invalid source values instead of silently ignoring data-quality problems.

Lab Manual 7 — Calculate Numeric Process Statistics

9 min

Objective

Produce trustworthy statistics for temperature, oil pressure and carbon potential, and treat the valid-sample count as part of the result rather than a detail.

Procedure

Run the statistics loop over Temp_Act, Oil_Act and Cp_Act.
Compare each printed count with the total number of retrieved rows.
Verify one mean by hand against a small known batch from Lab 4.
Check min and max against the engineering limits of the furnace, not only against each other.

What each line does

Code	Meaning in this lab
pd.to_numeric(..., errors="coerce")	Converts valid numbers and turns unparseable text into `NaN` instead of raising an exception.
values.count()	Counts valid numeric samples only. If this is lower than the row count, some source values were not numbers.
values.mean()	Arithmetic average. Sensitive to outliers, so always read it beside min and max.
values.std()	Sample standard deviation. pandas uses `ddof=1` by default, which differs from NumPy's default of 0.
f"...{values.mean():.2f}{unit}"	Formats to two decimals and appends the engineering unit so the console output is readable by a process engineer.

Copy-paste practice code

05_statistics_audit.py

total_rows = len(dataframe)

for column in ("Temp_Act", "Oil_Act", "Cp_Act", "Jacket_Act"):
    values = pd.to_numeric(dataframe[column], errors="coerce")
    valid = values.count()
    print(
        f"{column:<12} valid={valid:>3}/{total_rows}  "
        f"invalid={total_rows - valid:>3}  "
        f"mean={values.mean():.2f}  min={values.min():.2f}  max={values.max():.2f}"
    )

suspect = dataframe.loc[pd.to_numeric(dataframe["Temp_Act"], errors="coerce").isna()]
print("\nRows where Temp_Act is not numeric:", len(suspect))
if not suspect.empty:
    print(suspect[["DT", "TM", "SQF_No", "Temp_Act"]].to_string(index=False))

This version reports the invalid count and lists the offending rows. Silently coercing bad data to NaN and never looking at it is how a data-quality problem survives into a monthly report.

Expected result

console output

Temp_Act     valid=100/100  invalid=  0  mean=487.32  min=92.41  max=854.88
Oil_Act      valid=100/100  invalid=  0  mean=4.87   min=0.00   max=9.41
Cp_Act       valid=100/100  invalid=  0  mean=0.81   min=0.00   max=1.19
Jacket_Act   valid=100/100  invalid=  0  mean=61.44  min=38.02  max=88.73

Rows where Temp_Act is not numeric: 0

Checkpoint

Pass when: Every valid count equals the retrieved row count, and each min/max pair sits inside the physical range of the furnace.

If it fails

Symptom	What to check
Statistics print as nan	No valid numeric samples. The column is text or entirely NULL — the invalid count in this lab tells you which.
mean is far outside the expected range	A sensor fault or a unit mismatch has entered the data. Inspect min and max before trusting the mean.
std differs from a NumPy result	pandas uses `ddof=1` and NumPy uses `ddof=0`. Pass `ddof` explicitly if the two must agree.
Valid count is lower than the row count	Some rows hold non-numeric text. This lab prints those rows — fix them at source rather than ignoring them.

8. Count Furnaces, Process Stages and Fan Status

06_value_counts.py

print(dataframe["SQF_No"].value_counts(dropna=False).sort_index().to_string())
print(dataframe["Event_To"].value_counts(dropna=False).to_string())
print(dataframe["Fan_Status"].value_counts(dropna=False).to_string())

value_counts() produces a frequency table. dropna=False deliberately includes missing values, which helps expose incomplete records. Furnace numbers are sorted by index for easy comparison; process-stage and fan-status results retain frequency order so the most common values appear first.

Lab Manual 8 — Count Furnaces, Process Stages and Fan Status

6 min

Objective

Build category counts for furnace number, process stage and fan status, and reconcile the totals against the DataFrame length so nothing is quietly dropped.

Procedure

Run the three value_counts() calls.
Add the counts in each table and compare the total with len(dataframe).
Confirm that dropna=False is present so NaN appears as its own category.

What each line does

Code	Meaning in this lab
value_counts()	Returns a frequency table, sorted by count with the most common value first.
dropna=False	Includes missing values as a visible category. Without it, incomplete records disappear from the summary.
.sort_index()	Sorts furnace numbers 1, 2, 3 instead of by frequency, so comparison between furnaces is easy.
.to_string()	Prints the full series without the pandas truncation that hides long category lists.

Copy-paste practice code

06_category_audit.py

for column in ("SQF_No", "Event_To", "Fan_Status"):
    counts = dataframe[column].value_counts(dropna=False)
    if column == "SQF_No":
        counts = counts.sort_index()

    print(f"\n{column} ({counts.sum()} of {len(dataframe)} rows accounted for)")
    print(counts.to_string())

    missing = dataframe[column].isna().sum()
    if missing:
        print(f"  WARNING: {missing} row(s) have no {column} value")

The reconciliation line is the point of this lab. A category table whose total does not match the row count is hiding something.

Expected result

console output

SQF_No (100 of 100 rows accounted for)
1    34
2    33
3    33

Event_To (100 of 100 rows accounted for)
Heating              25
Charging             20
Soaking              20
Cooling              15
Oil Quenching        15
Charge Discharged     5

Fan_Status (100 of 100 rows accounted for)
ON     80
OFF    20

Checkpoint

Pass when: Each table's total equals the DataFrame length, all three furnace numbers appear, and the stage names match the six documented cycle stages.

If it fails

Symptom	What to check
A stage name appears twice with different spelling	Trailing spaces or inconsistent case in the source. Apply `.str.strip()` and standardise at insert time.
Totals are lower than the row count	`dropna=False` is missing, so NULL rows are being excluded from the table.
A furnace number is absent	That furnace produced no events inside the TOP 100 window. Widen the query before concluding it is offline.

9. Error Handling and Safe Operation

07_error_handling.py

try:
    with create_connection() as connection:
        dataframe = read_event_data(connection)
except pyodbc.Error as error:
    raise SystemExit(f"Database connection/query failed: {error}") from error

if dataframe.empty:
    print("No records found. Run Code 02 first.")
    return

The context manager closes the connection when the block finishes, including after an exception. The handler catches ODBC-related failures and exits with a useful message while preserving the original exception chain. The empty check avoids later column/statistics operations when no records were returned.

Entry-point correction: the supplied text ended with Markdown-formatted if **name** == "**main**":. Valid Python is if __name__ == "__main__":.

Lab Manual 9 — Error Handling and Safe Operation

8 min

Objective

Make the reader fail clearly rather than with a stack trace, and prove the connection is released on both the success and the failure path.

Procedure

Run the script normally and confirm it completes.
Break the server name deliberately and confirm one clear message appears instead of a traceback.
Point the query at an empty table and confirm the empty-result guard stops it before the statistics run.
Restore the correct constants.

What each line does

Code	Meaning in this lab
with create_connection() as connection	The context manager closes the connection when the block ends, including after an exception.
except pyodbc.Error as error	Catches ODBC failures specifically. A bare `except` would also swallow typing mistakes in your own code.
raise SystemExit(f"...") from error	Exits with a readable message while `from error` preserves the original exception chain for the log.
if dataframe.empty	Stops before column and statistics operations that would fail on an empty frame.
return	Ends `main()` cleanly. In a scheduled task this is a normal exit, not a failure.

Copy-paste practice code

07_failure_drill.py

import pyodbc

BROKEN_SERVER = r"SOFTWELL\NOSUCHINSTANCE"


def create_broken_connection():
    return pyodbc.connect(
        f"Driver={DRIVER};Server={BROKEN_SERVER};Database={DATABASE};"
        "Trusted_Connection=yes;",
        timeout=5,
    )


try:
    with create_broken_connection() as connection:
        print("This line should never run.")
except pyodbc.Error as error:
    print("Handled cleanly.")
    print("SQLSTATE:", error.args[0])
    print("Message :", str(error.args[1])[:120])

Run this drill once so you recognise the SQLSTATE codes in a real failure. timeout=5 keeps the lab short instead of waiting for the default login timeout.

Expected result

console output

Handled cleanly.
SQLSTATE: 08001
Message : [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].

Checkpoint

Pass when: The broken run prints one handled message with a SQLSTATE and no traceback, and the normal run still completes end to end.

If it fails

Symptom	What to check
A full traceback still appears	The failing call sits outside the `try` block. The connection must be created inside it.
The script hangs instead of failing	No login timeout is set. Add `timeout=5` to `pyodbc.connect()`.
UnboundLocalError on dataframe	The exception path did not exit. `SystemExit` must be raised, not merely printed.
if __name__ == "__main__" does not run	The original source carried a Markdown-mangled `if name == "main":`. The correct form uses double underscores.

10. Complete Copy-Paste-Ready Python Program

read_events.py

"""Code 03: Read the latest furnace events and display basic statistics."""

import pandas as pd
import pyodbc

SQL_SERVER = r"SOFTWELL\WINCC"
DATABASE = "SQF_DB"
DRIVER = "{ODBC Driver 17 for SQL Server}"
NUMBER_OF_RECORDS = 100


def create_connection():
    return pyodbc.connect(
        f"Driver={DRIVER};Server={SQL_SERVER};Database={DATABASE};"
        "Trusted_Connection=yes;"
    )


def read_event_data(connection, top_n=NUMBER_OF_RECORDS):
    query = """
        SELECT TOP (?)
            [DT], [TM], [SQF_No], [ChargeNo],
            [Event_From], [Event_To],
            [Temp_Set], [Temp_Act],
            [Cp_Set], [Cp_Act],
            [Oil_Set], [Oil_Act],
            [Jacket_Set], [Jacket_Act],
            [Fan_Status]
        FROM [dbo].[tblEvent]
        ORDER BY [DT] DESC, [TM] DESC;
    """
    cursor = connection.cursor()
    cursor.execute(query, top_n)
    columns = [column[0] for column in cursor.description]
    return pd.DataFrame.from_records(cursor.fetchall(), columns=columns)


def display_statistics(dataframe):
    print("\nTEMPERATURE AND PROCESS STATISTICS")
    print("=" * 80)

    for column, unit in (
        ("Temp_Act", "°C"),
        ("Oil_Act", " bar"),
        ("Cp_Act", ""),
    ):
        values = pd.to_numeric(dataframe[column], errors="coerce")
        print(
            f"{column}: count={values.count()}, mean={values.mean():.2f}{unit}, "
            f"min={values.min():.2f}{unit}, max={values.max():.2f}{unit}, "
            f"std={values.std():.2f}{unit}"
        )

    print("\nRecords by furnace:")
    print(dataframe["SQF_No"].value_counts(dropna=False).sort_index().to_string())

    print("\nRecords by process stage:")
    print(dataframe["Event_To"].value_counts(dropna=False).to_string())

    print("\nFan status:")
    print(dataframe["Fan_Status"].value_counts(dropna=False).to_string())


def main():
    print("CODE 03: READ AND DISPLAY FURNACE DATA")

    try:
        with create_connection() as connection:
            dataframe = read_event_data(connection)
    except pyodbc.Error as error:
        raise SystemExit(f"Database connection/query failed: {error}") from error

    print(f"\nRetrieved {len(dataframe)} records.")

    if dataframe.empty:
        print("No records found. Run Code 02 first.")
        return

    print("\nNewest 10 records:")
    print(dataframe.head(10).to_string(index=False))

    print("\nOldest 10 records:")
    print(dataframe.tail(10).iloc[::-1].to_string(index=False))

    display_statistics(dataframe)


if __name__ == "__main__":
    main()

Lab Manual 10 — Run the Complete Reader End to End

10 min

Objective

Execute the finished program on a clean machine so the whole workflow — connect, query, build the DataFrame, summarise — runs from one command.

Procedure

Create a project folder and a virtual environment so lab packages stay isolated.
Install pandas and pyodbc inside it.
Save the Section 10 listing as read_events.py.
Edit only four constants: SQL_SERVER, DATABASE, DRIVER and NUMBER_OF_RECORDS.
Run it and compare the console output with the expected output below.

What each line does

Code	Meaning in this lab
python -m venv .venv	Creates an isolated environment so this lab never disturbs other projects on the PC.
.venv\Scripts\activate	Activates it on Windows. The prompt changes to show `(.venv)`.
pip install pandas pyodbc	Installs both packages. The ODBC driver itself is a separate Windows installation.
NUMBER_OF_RECORDS	Start at 20 on an unfamiliar database, then raise it to 100.
if __name__ == "__main__":	Lets another script import `read_event_data()` without running the report.

Copy-paste practice code

setup_and_run.bat

cd C:\SoftwellLabs\python-sql
python -m venv .venv
.venv\Scripts\activate
python -m pip install --upgrade pip
python -m pip install pandas pyodbc
python read_events.py

On macOS or Linux, activate with source .venv/bin/activate and install the Microsoft ODBC driver for your distribution first.

Expected result

console output

CODE 03: READ AND DISPLAY FURNACE DATA

Retrieved 100 records.

Newest 10 records:
...

TEMPERATURE AND PROCESS STATISTICS
================================================================================
Temp_Act: count=100, mean=487.32°C, min=92.41°C, max=854.88°C, std=271.06°C
Oil_Act: count=100, mean=4.87 bar, min=0.00 bar, max=9.41 bar, std=3.12 bar
Cp_Act: count=100, mean=0.81, min=0.00, max=1.19, std=0.42

Records by furnace:
1    34
2    33
3    33

Checkpoint

Pass when: The retrieved count matches NUMBER_OF_RECORDS, no statistic prints as nan, and the furnace counts add up to the retrieved total.

If it fails

Symptom	What to check
ModuleNotFoundError: No module named 'pandas'	The virtual environment is not active, or pip installed into a different Python. Run `where python` and confirm it points inside `.venv`.
Retrieved 0 records	The table is empty. Run the insert practical (Code 02) before this one.
Statistics print as nan	The numeric columns are text or NULL. Lab 7 identifies the offending rows.
UnicodeEncodeError on the degree sign	An old Windows console codepage. Run `chcp 65001` first, or use Windows Terminal.

11. Expected Output and Troubleshooting

A successful run prints the number of records, the newest ten rows, the oldest ten within the retrieved batch, statistics for Temp_Act, Oil_Act and Cp_Act, followed by category counts.

Problem	Likely cause	Action
Data source name not found / driver error	ODBC Driver 17 is missing or driver name differs	Check installed ODBC drivers and update `DRIVER`
Login failed	Windows identity lacks SQL permission	Confirm the executing user and grant only required access
Server not found or timeout	Instance name, SQL Browser, firewall or network problem	Test server reachability and SQL Server instance settings
Invalid object or column name	Database schema differs from the example	Verify `dbo.tblEvent` and every selected column
Statistics show `nan`	No valid numeric samples	Inspect source types and values; compare valid count with row count
Latest row seems inconsistent	Date/time columns are text, null, or tied	Use appropriate SQL date/time types and add a unique tie-breaker

Hands-On Lab: Read and Verify Furnace Data

Hands-on

Before you start

Use a test or approved read-only SQL Server environment.
Verify that the table contains sample data and that the Windows user has SELECT access.
Install pandas, pyodbc and the matching Microsoft ODBC driver.
Estimated time: 25 minutes.

Prove the connection

Set the server, database and driver constants, then run only create_connection() inside a with block.

The connection opens and closes without an ODBC exception.

Retrieve a small controlled batch

Call read_event_data(connection, top_n=20) and print the DataFrame columns and length.

No more than 20 rows are returned and all 15 expected columns are present.

Verify ordering

Compare the first and last date/time values with an approved SQL query in SQL Server Management Studio.

The first DataFrame row is the newest record according to the defined ordering.

Validate statistics and categories

Run display_statistics(), manually verify one mean using known test values, and compare category totals with the DataFrame length.

Valid counts are understood, category counts reconcile with missing-value handling, and the test evidence is saved.

Frequently asked questions

Can pandas read data directly from SQL Server?

Yes. pandas provides SQL-reading APIs. This example uses a pyodbc cursor plus DataFrame.from_records() to remain copy-paste ready without adding SQLAlchemy.

Why use parameter binding for TOP?

It separates the integer limit from the SQL text and lets the driver handle the value safely. Do not insert untrusted text into the query using string formatting.

Why use to_numeric(errors="coerce")?

It converts valid numbers and replaces invalid text with NaN, allowing calculations to continue. Always compare the valid count with the retrieved row count so data-quality problems remain visible.

Should I use pd.read_sql() instead of a pyodbc cursor?

pd.read_sql() is shorter, but pandas raises a warning for raw DBAPI connections and officially supports SQLAlchemy engines. The cursor approach here keeps the dependency list to pandas and pyodbc only.

How do I read a large table without running out of memory?

Replace fetchall() with fetchmany(1000) in a loop and process each chunk, or keep a TOP limit and a date filter in SQL. Never pull a full historian table into a DataFrame.

Why is my newest record not first?

Either DT and TM are stored as text, so they sort alphabetically, or several rows share the same timestamp. Use proper date and time types and add a unique descending tie-breaker.

Why do the statistics print as nan?

No valid numeric samples were found in that column, usually because the SQL column is text or entirely NULL. Compare the valid count printed by count() with the retrieved row count.

Can this script run automatically every shift?

Yes. Use a dedicated least-privilege SQL login with SELECT only, run it through Windows Task Scheduler, and write the output to a log file rather than the console.

Does the connection close if the query fails?

Yes. with create_connection() as connection: releases the connection when the block ends, including when an exception leaves it.

Reviewed by Bhawesh Kumar SinghIndustrial Automation Trainer and Industry 4.0 Consultant · Softwell Automation · 21+ years industry experience

Python Scripting TrainingPython automation, pandas, file handling and industrial reporting projects.View syllabus →SQL Reporting TrainingSQL Server queries, plant data extraction and scheduled reports.View syllabus →Industry 4.0 TrainingOPC UA, SQL, Python, dashboards and IT/OT integration.View syllabus →

Read SQL Server Data Using Python, pyodbc and pandas

Lab Overview

Prerequisites / What You’ll Need

Connect Python to SQL Server and Load Data into pandas

Related Search Topics

1. Connect Python to SQL Server and Read Data

SQL Server → pyodbc → pandas DataFrame

Connect

SELECT Query

Cursor Result

DataFrame

Analyze

Engineering View

Lab Manual Index: 8 Guided Exercises

2. Requirements and Database Assumptions

3. Create the SQL Server Connection

Lab Manual 3 — Create and Prove the SQL Server Connection

Objective

Procedure

What each line does

Copy-paste practice code

Expected result

Checkpoint

If it fails

4. Execute a SQL Server SELECT Query with pyodbc

Lab Manual 4 — Execute the Parameterized SELECT TOP Query

Objective

Procedure

What each line does

Copy-paste practice code

Expected result

Checkpoint

If it fails

5. Load pyodbc Query Results into a pandas DataFrame

Lab Manual 5 — Turn Cursor Results into a pandas DataFrame

Objective

Procedure

What each line does

Copy-paste practice code

Expected result

Checkpoint

If it fails

6. Display Newest and Oldest Samples

Lab Manual 6 — Display the Newest and Oldest Samples

Objective

Procedure

What each line does

Copy-paste practice code

Expected result

Checkpoint

If it fails

7. Calculate Numeric Process Statistics

Lab Manual 7 — Calculate Numeric Process Statistics

Objective

Procedure

What each line does

Copy-paste practice code

Expected result

Checkpoint

If it fails

8. Count Furnaces, Process Stages and Fan Status

Lab Manual 8 — Count Furnaces, Process Stages and Fan Status

Objective

Procedure

What each line does

Copy-paste practice code

Expected result

Checkpoint

If it fails

9. Error Handling and Safe Operation

Lab Manual 9 — Error Handling and Safe Operation

Objective

Procedure

What each line does

Copy-paste practice code

Expected result

Checkpoint

If it fails

10. Complete Copy-Paste-Ready Python Program

Lab Manual 10 — Run the Complete Reader End to End