Read SQL Server Data Using Python, pyodbc and pandas

• 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 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.

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.

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.

pip install pandas pyodbc

3. Create the SQL Server Connection

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.

Execute a SQL Server SELECT Query with pyodbc

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.

Load pyodbc Query Results into a pandas DataFrame

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.

6. Display Newest and Oldest Samples

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

print("Oldest 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.

7. Calculate Numeric Process Statistics

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}"
    )

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.

8. Count Furnaces, Process Stages and Fan Status

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.

9. Error Handling and Safe Operation

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.

10. Complete Copy-Paste-Ready Python Program

"""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()

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.