Python to SQL Server Data Type Mapping with pyodbc

• Match value range, precision, scale, length and nullability—not only the general type name.
• Use Unicode SQL columns such as nvarchar when multilingual text must be preserved.
• Inspect returned Python types and pandas dtypes before calculations or Excel export.

Why Python-to-SQL Server Type Mapping Matters

Python and SQL Server each have their own type system. The ODBC driver translates between them when a query is executed or rows are returned. A compatible mapping protects values from truncation, overflow, rounding, timezone loss and invalid comparisons.

Parameter binding is central: cursor.execute(sql, values) carries values separately from SQL syntax. It improves security and lets the driver process Python objects using database type information.

Python and SQL Server Data Type Mapping Table

3. Strings and Unicode Text

charge_no = "CHG-20260802-001"
event_to = "Soaking"
operator_note = "भट्ठी जाँच पूर्ण"  # Unicode text

cursor.execute(
    "INSERT INTO dbo.Events (ChargeNo, Event_To, OperatorNote) VALUES (?, ?, ?)",
    charge_no, event_to, operator_note,
)

Choose nvarchar for Unicode text. varchar depends on a code page/collation and may not preserve all scripts. Validate maximum lengths before the insert; SQL Server may raise truncation errors when a value exceeds the defined column size.

Do not manually add quotes or escape apostrophes when using parameters—the driver handles the value.

4. Integers, Boolean Values and Ranges

Python integers have arbitrary precision, but SQL integer types have fixed ranges. A value valid in Python can overflow a SQL int. Choose the SQL type based on the real engineering range and long-term growth.

sqf_no = int(user_text)
if sqf_no not in {1, 2, 3}:
    raise ValueError("Furnace must be 1, 2 or 3.")

fan_running = True
cursor.execute("UPDATE dbo.Furnace SET FanRunning=? WHERE SQF_No=?",
               fan_running, sqf_no)

Python float vs Decimal for SQL Server

Python float uses binary floating-point and is suitable for approximate scientific/process measurements when tiny representation differences are acceptable. SQL Server float is also approximate. For exact decimal rules, use Decimal with SQL decimal(p,s).

from decimal import Decimal

temperature = 850.25             # approximate float
carbon_potential = Decimal("0.810")
report_cost = Decimal("1250.50")

cursor.execute(
    "INSERT INTO dbo.ProcessValues (Temperature, CP, ReportCost) VALUES (?, ?, ?)",
    temperature, carbon_potential, report_cost,
)

6. Date, Time and Datetime Values

from datetime import date, datetime, time

event_time = datetime.now()
production_date = date.today()
shift_start = time(6, 0, 0)

cursor.execute(
    "INSERT INTO dbo.EventTimeDemo (EventTime, ProductionDate, ShiftStart) "
    "VALUES (?, ?, ?)",
    event_time, production_date, shift_start,
)

Pass temporal objects directly rather than formatting dates into locale-sensitive strings. Prefer SQL datetime2 for new event timestamps because it provides a wider range and configurable fractional-second precision. Define whether stored timestamps represent plant local time or UTC, and handle timezone conversion explicitly—SQL datetime2 itself has no timezone offset.

7. None, SQL NULL and Missing Data

None is normally bound as SQL NULL. When reading, a SQL NULL normally returns as None through pyodbc. NULL means unknown/not supplied; it is not the same as zero, an empty string or False.

operator_note = None
cursor.execute(
    "INSERT INTO dbo.Events (ChargeNo, OperatorNote) VALUES (?, ?)",
    "CHG-001", operator_note,
)

cursor.execute("SELECT * FROM dbo.Events WHERE OperatorNote IS NULL")

Use IS NULL, not = NULL, in SQL. The target column must permit NULL or have a suitable default. In pandas, missing values may appear as NaN, NaT, None or pd.NA depending on dtype.

8. Bytes and Binary Values

payload = b"SQF
"
cursor.execute(
    "INSERT INTO dbo.BinaryLog (RawPayload) VALUES (?)",
    payload,
)

row = cursor.execute("SELECT TOP (1) RawPayload FROM dbo.BinaryLog").fetchone()
restored = bytes(row.RawPayload) if row.RawPayload is not None else None

Use bytes for SQL varbinary. Do not place ordinary text into binary columns to avoid designing a proper character encoding. For large files, consider external/object storage with a database reference rather than loading the entire file into a normal row.

9. Lists, Tuples, Dictionaries and Database Rows

Collections do not normally map to one scalar SQL column. They organize parameters and rows in Python:

• A tuple can represent one insert row.
• A list of tuples can feed executemany().
• A dictionary can represent a named application record, but pyodbc uses positional ? markers.
• A set is useful for validation/uniqueness but has no guaranteed positional order.

records = [
    (1, "CHG-001", Decimal("850.25"), True),
    (2, "CHG-002", Decimal("848.75"), False),
]

cursor.fast_executemany = True
cursor.executemany(
    "INSERT INTO dbo.Batch (SQF_No, ChargeNo, Temperature, FanRunning) "
    "VALUES (?, ?, ?, ?)",
    records,
)
connection.commit()

Use JSON text or normalized child tables only when the data model truly requires nested/repeating information; do not stringify arbitrary lists into a column merely for convenience.

10. pandas Data Types and SQL Data

A pandas Series has its own dtype layer between Python objects and SQL values. Inspect and normalize it before analysis or export.

dataframe["SQF_No"] = pd.to_numeric(dataframe["SQF_No"], errors="coerce").astype("Int64")
dataframe["Temp_Act"] = pd.to_numeric(dataframe["Temp_Act"], errors="coerce")
dataframe["Event_Timestamp"] = pd.to_datetime(
    dataframe["Event_Timestamp"], errors="coerce"
)
dataframe["Fan_Status"] = dataframe["Fan_Status"].astype("string")

print(dataframe.dtypes)
print(dataframe.isna().sum())

11. Complete Parameterized Insert and Read Example

Create a matching demonstration table before running this program:

CREATE TABLE dbo.PythonTypeDemo
(
    DemoId           bigint IDENTITY(1,1) PRIMARY KEY,
    SQF_No           int             NOT NULL,
    ChargeNo         nvarchar(50)    NOT NULL,
    EventTime        datetime2(3)    NOT NULL,
    Temperature      decimal(10,2)   NULL,
    CarbonPotential  decimal(6,3)    NULL,
    FanRunning       bit             NULL,
    OperatorNote     nvarchar(200)   NULL,
    RawPayload       varbinary(100)  NULL
);

"""Demonstrate safe Python-to-SQL Server data-type integration."""

from datetime import datetime
from decimal import Decimal

import pyodbc

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


def create_connection():
    return pyodbc.connect(
        f"Driver={DRIVER};Server={SERVER};Database={DATABASE};"
        "Trusted_Connection=yes;TrustServerCertificate=yes;",
        timeout=15,
    )


def prepare_record():
    return (
        1,                              # int -> int
        "CHG-20260802-001",             # str -> nvarchar
        datetime.now(),                 # datetime -> datetime2
        Decimal("850.25"),              # Decimal -> decimal(10,2)
        Decimal("0.81"),                # Decimal -> decimal(6,3)
        True,                           # bool -> bit
        None,                           # None -> NULL
        b"SQF",                         # bytes -> varbinary
    )


def insert_record(connection, record):
    query = """
        INSERT INTO dbo.PythonTypeDemo
        (
            SQF_No, ChargeNo, EventTime, Temperature,
            CarbonPotential, FanRunning, OperatorNote, RawPayload
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?);
    """
    connection.cursor().execute(query, record)
    connection.commit()


def read_record(connection):
    cursor = connection.cursor()
    cursor.execute("""
        SELECT TOP (1)
            SQF_No, ChargeNo, EventTime, Temperature,
            CarbonPotential, FanRunning, OperatorNote, RawPayload
        FROM dbo.PythonTypeDemo
        ORDER BY EventTime DESC;
    """)
    row = cursor.fetchone()
    if row is None:
        return None
    return {
        "sqf_no": row.SQF_No,
        "charge_no": row.ChargeNo,
        "event_time": row.EventTime,
        "temperature": row.Temperature,
        "carbon_potential": row.CarbonPotential,
        "fan_running": bool(row.FanRunning),
        "operator_note": row.OperatorNote,
        "raw_payload": bytes(row.RawPayload) if row.RawPayload is not None else None,
    }


def main():
    try:
        with create_connection() as connection:
            insert_record(connection, prepare_record())
            result = read_record(connection)
    except pyodbc.Error as error:
        raise SystemExit(f"SQL Server integration failed: {error}") from error

    print("Latest record and returned Python types:")
    for name, value in result.items():
        print(f"{name:20} value={value!r} type={type(value).__name__}")


if __name__ == "__main__":
    main()

12. Troubleshooting and Best Practices

Integration checklist

• Define business meaning, engineering unit and valid range for every field.
• Match SQL length, range, precision, scale and nullability.
• Bind values; never concatenate user/process data into SQL.
• Use Unicode columns for multilingual text.
• Use Decimal for exact quantities and float only for approximate quantities.
• Adopt one documented timestamp/timezone policy.
• Validate values before writes and verify returned types after reads.
• Use transactions, logging and deterministic error handling.