Skip to Content

← All functionalities

Industrial mixing (touch HMI)TouchBerry PiMQTTSQL ServerCommunication

A two-way MQTT / SQL Server bridge with paho-mqtt and pyodbc

Plant-floor devices speak MQTT; the company database is SQL Server. This Python script bridges both worlds on a TouchBerry Pi: every 5 seconds it reads machine readiness from the database and publishes it for the HMI LEDs, and in the opposite direction it inserts every activation received over MQTT into a history table. It is the exact pattern from a real deployment on an industrial mixing line, with reconnection logic for both sides included.

Two directions, one script

The bridge polls an EstadoMaquinas table every five seconds and publishes each machine's ready flag as a retained MQTT message, so dashboards always show fresh state even after they restart. Meanwhile, paho-mqtt's callback thread receives JSON activations from the touch panel and writes them to RegistroActivaciones with a parameterised INSERT — placeholders, never string concatenation, so a stray quote in a comment field can't break the query.

Both links will drop — plan for it

loop_start() gives paho-mqtt its own background thread with automatic broker reconnection, and the on_connect callback re-subscribes every time, so a broker restart costs nothing. The database side wraps every cursor operation: on a pyodbc error the script enters a retry loop, reconnects with a 10-second backoff and replays the pending insert, instead of dying silently overnight and losing a whole shift of activation records.

Configuration stays out of the code

Server name, database, credentials and topic names sit together at the top of the script as obvious placeholders (SERVIDOR_SQL, bd_produccion), meant to be loaded from environment variables or a config file in production rather than committed to a repository. The ODBC connection string targets msodbcsql17, which Microsoft ships for ARM Linux, so the very same script runs unchanged on the Raspberry Pi inside the touch panel.

A snippet from the implementation

Straight from the example as deployed on the TouchBerry Pi — copy it freely:

def connect_db():
    """Returns a valid connection, retrying until it succeeds."""
    global db_connection
    while True:
        try:
            db_connection = pyodbc.connect(SQL_CONN_STR, timeout=5)
            db_connection.autocommit = True
            print("[DB] Connected to SQL Server")
            return db_connection
        except pyodbc.Error as e:
            print(f"[DB] No connection ({e}); retrying in {DB_RETRY_S} s")
            time.sleep(DB_RETRY_S)
View the full working example Get the full project pack

The full example is a complete program — wiring header, setup and main loop — ready to adapt to your application.

Frequently asked questions

Can a Raspberry Pi connect to Microsoft SQL Server?

Yes. Install the msodbcsql17/18 ODBC driver (or FreeTDS) and use pyodbc from Python; the connection string is the same as on Windows.

Why poll the database instead of pushing from SQL Server?

A 5-second SELECT on a small table is trivial load and needs no Service Broker or triggers. For faster reaction you can shorten the period or add a change-tracking column.

What happens to MQTT messages while the database is down?

The example retries the failed insert after reconnecting. For longer outages, add a local queue (a file or SQLite) so activations survive until SQL Server returns.

Related functionalities