Extract Bluetooth data from database to csv

The following project logs down all Bluetooth devices seen by HA at fixed intervals. After studying the original device_tracker.py and some testing, I have confirmed that:

  • Bluetooth devices are recognized and registered at first encounter between the devices and HA.
  • Subsequent scans may not “see” the device even though its Bluetooth is turned on and is in the vicinity. Instead, a directed look up for specific MAC addresses is needed to update status of the devices. HA is doing this in the background and “interviews” all devices in its database at regular interval. The codes are here: core/device_tracker.py at f269135ae925de9a0cf8d90750da9a6ff323415e · home-assistant/core · GitHub
  • HA saves the data in its database and display it in History.

For further analysis, the data can be extracted using following method. Note that this method is usable for all kinds of data saved in HA database. Just modify the query and you will get the data you want.
In configuration.yaml, create a “sensor” to run python script. I set it to run every 24 hours:

#Define a sensor to invoke python on a script
sensor:
  - platform: command_line
    name: "Check BT Devices"
    command: "python3 /config/log_bluetoothdevices.py"
    scan_interval: 86400

The python script, /config/log_bluetoothdevices.py contains:

import csv
import io
import sys
from sqlalchemy import create_engine
import pandas as pd
from datetime import datetime

# define your home assistant db location below:
db = "/config/home-assistant_v2.db"

# connect using SQLalchemy
engine = create_engine(f"sqlite:///{db}")
conn = engine.connect()

# build the query
query = f"""
SELECT
    entity_id,
    state,
    last_changed
FROM states 
WHERE 
    attributes LIKE '%bluetooth%'
"""

# execute the query and load into a pandas dataframe:
df = pd.read_sql_query(query, conn)
conn.close()

# Create time stamp
now = datetime.now()
dt_string = now.strftime("%Y-%m-%d %H-%M-%S")
print("Bluetooth devices logged at: ", dt_string)

with io.open('/config/Logs/bluetooth_log '+dt_string+'.csv', mode='w', newline='',encoding="utf-8") as outputfile:
        outputwriter = csv.writer(outputfile, delimiter=',')
    
        #Push header to output file
        outputwriter.writerow(df)
        
        #Push query results to file
        for index, row in df.iterrows():
            outputwriter.writerow(row)