Create a SQL Server Database and Table with Python pyodbc

• You cannot initially connect to a database that does not exist.
• CREATE DATABASE needs an autocommit connection because it cannot run inside a user transaction.
• Existence checks make reruns non-destructive, but they do not upgrade an old or incorrect schema.

Python pyodbc Database-Creation Workflow

The supplied fragments contain two valid operations but were merged out of order. Database creation and table creation require separate connections because the target database is unavailable until the first operation finishes.

DDL—Data Definition Language—creates or changes database objects. CREATE DATABASE defines a database; CREATE TABLE defines a table and its columns. This script changes server state and should be executed only by an authorized user against the intended SQL Server instance.

2. Requirements and Permissions

• Python 3.10 or later.
• pyodbc: python -m pip install pyodbc.
• Microsoft ODBC Driver 17 for SQL Server.
• Network access to SOFTWELL\WINCC.
• Windows authentication enabled for the executing identity.
• Server-level permission to create a database and database-level permission to create a table.

3. Build a Reusable Connection Helper

def connect(database_name, *, autocommit=False):
    return pyodbc.connect(
        f"DRIVER={{{DRIVER}}};"
        f"SERVER={SERVER};"
        f"DATABASE={database_name};"
        "Trusted_Connection=yes;"
        "TrustServerCertificate=yes;",
        autocommit=autocommit,
        timeout=15,
    )

The helper accepts the database name and transaction mode so the same connection settings can serve both stages. The raw server string preserves the named-instance backslash. Braces around the driver name are required ODBC syntax.

TrustServerCertificate=yes is common in labs using a self-signed certificate. A production server should use a trusted certificate so normal validation can remain enabled.

Create a SQL Server Database from master with pyodbc

with connect("master", autocommit=True) as connection:
    cursor = connection.cursor()
    cursor.execute(f"""
        IF DB_ID(N'{DATABASE}') IS NULL
        BEGIN
            CREATE DATABASE [{DATABASE}];
        END;
    """)

DB_ID(N'SQF_DB') returns the database ID when the database exists and NULL otherwise. The conditional prevents a second run from raising “database already exists.”

Autocommit is essential here because SQL Server does not allow CREATE DATABASE inside an explicit user transaction. The operation completes before Python proceeds to the new connection.

5. Reconnect to the New Database

with connect(DATABASE) as connection:
    cursor = connection.cursor()
    # CREATE TABLE runs here

A connection remains associated with its original database context. After creating SQF_DB from master, open a new connection whose DATABASE setting is SQF_DB. This also provides a clear failure point if database creation was not successful or access was not granted.

Create a SQL Server Table with Python

IF OBJECT_ID(N'dbo.tblEvent', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.tblEvent
    (
        DT          datetime      NOT NULL,
        TM          varchar(10)   NULL,
        SQF_No      int           NULL,
        ChargeNo    varchar(50)   NULL,
        Event_From  varchar(100)  NULL,
        Event_To    varchar(100)  NULL,
        Temp_Set    float         NULL,
        Temp_Act    float         NULL,
        Cp_Set      float         NULL,
        Cp_Act      float         NULL,
        Oil_Set     float         NULL,
        Oil_Act     float         NULL,
        Jacket_Set  float         NULL,
        Jacket_Act  float         NULL,
        Fan_Status  varchar(10)   NULL
    );
END;

OBJECT_ID(..., N'U') asks for an object ID specifically for a user table. The condition protects an existing table from being overwritten. It does not compare the existing table’s columns or types; schema migration must be handled separately.

7. Understand the Furnace Event Columns

For a production design, consider datetime2 instead of legacy datetime, decimal(p,s) instead of approximate float where exact decimal behavior matters, a primary key, defaults/check constraints, and a single timestamp rather than duplicated DT/TM representations.

8. Commit and Verify the Created Objects

connection.commit()

cursor.execute("""
    SELECT DB_NAME() AS DatabaseName,
           OBJECT_ID(N'dbo.tblEvent', N'U') AS TableObjectId;
""")
database_name, table_object_id = cursor.fetchone()

if table_object_id is None:
    raise RuntimeError("dbo.tblEvent was not created.")

The table-creation connection uses normal transaction behavior and explicitly commits. Verification confirms both the active database context and the table object ID. A complete deployment check should additionally query sys.columns for types, lengths, nullability and constraints.

9. Identifier Safety and Idempotent Reruns

SQL parameters such as ? can represent data values, but not database/table identifiers. The database name is therefore interpolated into DDL. This is safe only because DATABASE is a trusted application constant. Never accept an unchecked database name from a web form, command line or file and insert it into SQL text.

The checks make this script create-if-missing: rerunning it leaves existing objects in place. That is useful, but not the same as schema versioning. If an existing tblEvent lacks a column or uses the wrong type, this script reports success without correcting it. Use reviewed migration scripts for schema changes.

10. Complete Corrected Python Program

"""Create SQF_DB and dbo.tblEvent when they do not already exist."""

import pyodbc

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


def connect(database_name, *, autocommit=False):
    return pyodbc.connect(
        f"DRIVER={{{DRIVER}}};"
        f"SERVER={SERVER};"
        f"DATABASE={database_name};"
        "Trusted_Connection=yes;"
        "TrustServerCertificate=yes;",
        autocommit=autocommit,
        timeout=15,
    )


def create_database():
    # DATABASE is a trusted constant. SQL identifiers cannot use ? parameters.
    with connect("master", autocommit=True) as connection:
        cursor = connection.cursor()
        cursor.execute(f"""
            IF DB_ID(N'{DATABASE}') IS NULL
            BEGIN
                CREATE DATABASE [{DATABASE}];
            END;
        """)


def create_event_table():
    with connect(DATABASE) as connection:
        cursor = connection.cursor()
        cursor.execute("""
            IF OBJECT_ID(N'dbo.tblEvent', N'U') IS NULL
            BEGIN
                CREATE TABLE dbo.tblEvent
                (
                    DT          datetime      NOT NULL,
                    TM          varchar(10)   NULL,
                    SQF_No      int           NULL,
                    ChargeNo    varchar(50)   NULL,
                    Event_From  varchar(100)  NULL,
                    Event_To    varchar(100)  NULL,
                    Temp_Set    float         NULL,
                    Temp_Act    float         NULL,
                    Cp_Set      float         NULL,
                    Cp_Act      float         NULL,
                    Oil_Set     float         NULL,
                    Oil_Act     float         NULL,
                    Jacket_Set  float         NULL,
                    Jacket_Act  float         NULL,
                    Fan_Status  varchar(10)   NULL
                );
            END;
        """)
        connection.commit()


def verify_objects():
    with connect(DATABASE) as connection:
        cursor = connection.cursor()
        cursor.execute("""
            SELECT DB_NAME() AS DatabaseName,
                   OBJECT_ID(N'dbo.tblEvent', N'U') AS TableObjectId;
        """)
        database_name, table_object_id = cursor.fetchone()
        if table_object_id is None:
            raise RuntimeError("dbo.tblEvent was not created.")
        return database_name, table_object_id


def main():
    try:
        create_database()
        print(f"Database [{DATABASE}] is available.")

        create_event_table()
        print("Table [dbo].[tblEvent] is available.")

        database_name, table_object_id = verify_objects()
        print(f"Verified database: {database_name}")
        print(f"Verified table object ID: {table_object_id}")
    except pyodbc.Error as error:
        raise SystemExit(f"SQL Server setup failed: {error}") from error


if __name__ == "__main__":
    main()

11. Troubleshooting and Design Improvements

Recommended production upgrades

• Use a DBA-managed deployment account separate from the runtime application account.
• Add a primary key such as Event_ID bigint IDENTITY.
• Use datetime2 and carefully selected decimal precision/scale.
• Add check constraints or reference tables for valid fan and process-stage values.
• Create useful indexes only after studying read/write patterns.
• Store configuration securely and validate every identifier against an allowlist.
• Use a migration tool or versioned SQL scripts for future schema changes.