Showing posts with label python. Show all posts
Showing posts with label python. Show all posts

Tuesday, February 11, 2025

Windows MSI for a Python Service With Cx_Freeze

The excellent cx_Freeze project makes it easy to build Windows executables for Python scripts. It also has some handy glue code to enable you to install and run a Python script as a Windows service; and includes a simple Windows service example in the project's samples directory.

It's also possible to package up the service into an MSI file (which can be installed via the msiexec tool included with Windows). However, I didn't find any good examples for how to do this; so this is what I did for a recent project:

  1. Set Up Basic Project With UV
  2. Set Up Basic Windows Service
  3. Build and Test Windows Service
  4. Build Basic MSI Package
  5. Configure MSI to Install and Start the Service
  6. Extra: Add Other Executables
  7. Extra: Add Icons
  8. Extra: Add Other Files
  9. Extra: Add Start Menu

Set Up Basic Project With UV

I set up a basic project with UV, using a pyproject.toml file that looks like this:

# pyproject.toml [project] name = "my_service" dynamic = ["version"] description = "My service description." readme = "README.md" license = { file = "LICENSE" } requires-python = ">=3.12" dependencies = [ "pywin32~=306.0 ; sys_platform == 'win32'", ] [project.scripts] my-service-cli = "my_service.cli:main" [build-system] requires = ["hatchling"] build-backend = "hatchling.build" [dependency-groups] dev = [ "pytest>=8.3.4", "ruff>=0.8.6", ] freeze = [ "cx-freeze>=6.13.2", "cx-logging>=3.0", ] [tool.hatch.version] path = "my_service/__init__.py"

I put the cx_Freeze-specific dependencies in my own custom freeze dependency group, as I only need them to be installed when running cx_Freeze commands.

In the my_service package, I set up globals for the version number and service details:

# src/my_service/__init__.py """My Service.""" __version__ = "1.0.0" DISPLAY_NAME = "My Service" DESCRIPTION = "My service description." SERVICE_NAME = "my-service"

Set Up Basic Windows Service

Next I set up a basic service class (similar to the Handler class in the ServiceHandler.py file of cx_Freeze's service sample) to run my service code (and to set up some log files):

# src/my_service/windows_service.py """cx_Freeze Win32Service for the my service.""" import sys from pathlib import Path try: import cx_Logging except ImportError: cx_Logging = None from my_service.service import run_my_service class Service: """cx_Freeze Win32Service for the agent.""" def initialize(self, cnf_file): """Called when the service is starting. Arguments: cnf_file (str): Path to configuration file. """ try: init_log() except Exception: cx_Logging.LogException() def run(self): """Called when the service is running.""" try: cx_Logging.Debug("running my service") run_my_service() except Exception: cx_Logging.LogException() def stop(self): """Called when the service is stopping.""" self.cnf.loop = 0 def init_log(): """Initializes service logging.""" log_dir = get_log_dir() cx_Logging.StartLogging(str(log_dir / "init.log"), cx_Logging.DEBUG) sys.stdout = open(log_dir / "stdout.log", "a") def get_log_dir(): """Gets service logging directory. Returns: str: Path to logging directory. """ executable_dir = Path(sys.executable).parent log_dir = executable_dir / "log" Path.mkdir(log_dir, parents=True, exist_ok=True) return log_dir

Then I set up the service-definition config for it (similar to the Config.py file of cx_Freeze's service sample), using some of the constants I had defined in my root my_service module:

# src/my_service/windows_service_config.py """cx_Freeze config for my service as a Win32Service.""" import my_service NAME = f"{my_service.SERVICE_NAME}-%s" DISPLAY_NAME = f"{my_service.DISPLAY_NAME} %s" MODULE_NAME = "my_service.windows_service" CLASS_NAME = "Service" DESCRIPTION = my_service.DESCRIPTION AUTO_START = False SESSION_CHANGES = False

And then I put together a cx_Freeze setup script (similar to the setup.py file of cx_Freeze's service sample) to run the cx_Freeze build_exe build, again using some of the constants I had defined in my root my_service module:

# cx_freeze_setup.py """cx_Freeze setup script.""" from cx_Freeze import Executable, setup from my_service import DESCRIPTION, SERVICE_NAME from my_service import __version__ as VERSION EXECUTABLES = [ Executable( script="src/my_service/windows_service_config.py", base="Win32Service", target_name=SERVICE_NAME, ), ] setup( name=SERVICE_NAME, version=VERSION, description=DESCRIPTION, options={ "build_exe": { "excludes": [ "test", "tkinter", "unittest", ], "includes": [ "_cffi_backend", "cx_Logging", ], "include_msvcr": True, "packages": [ "my_service", ], }, }, executables=EXECUTABLES, )

Build and Test Windows Service

With that in place, I could run cx_Freeze's build_exe command to build my service as a Windows executable, generating a my-service-svc.exe file in my project's build\exe.win-amd64-3.12 directory; and alongside the exe file, a lib folder containing all the compiled Python modules needed for the executable, plus some core DLLs:

> uv run --group freeze cx_freeze_setup.py build_exe Using CPython 3.12.9 Creating virtual environment at: .venv Built my-service @ file:///C:/my_project Installed 31 packages in 44.15s running build_exe creating directory C:\my_project\build\exe.win-amd64-3.12 copying C:\my_project\.venv\Lib\site-packages\cx_Freeze\bases\Win32Service-cpython-312-win_amd64.exe -> C:\my_project\build\exe.win-amd64-3.12\my-service.exe ... copying C:\my_project\.venv\Lib\site-packages\pywin32_system32\pywintypes312.dll -> C:\my_project\build\exe.win-amd64-3.12\lib\pywintypes312.dll writing zip file C:\my_project\build\exe.win-amd64-3.12\lib\library.zip Name File ---- ---- m BUILD_CONSTANTS C:\Users\JUSTIN~1\AppData\Local\Temp\2\cxfreeze-8edcizah\BUILD_CONSTANTS.py ... m zipimport C:\Users\Justin\AppData\Roaming\uv\python\cpython-3.12.9-windows-x86_64-none\Lib\zipimport.py Missing modules: ? OpenSSL.SSL imported from urllib3.contrib.pyopenssl ... ? zstandard imported from urllib3.response, urllib3.util.request This is not necessarily a problem - the modules may not be needed on this platform. Missing dependencies: ? api-ms-win-core-path-l1-1-0.dll ... ? api-ms-win-crt-utility-l1-1-0.dll This is not necessarily a problem - the dependencies may not be needed on this platform. > dir build\exe.win-amd64-3.12 Volume in drive C is Windows Volume Serial Number is 7EC2-1A39 Directory of C:\my_project\build\exe.win-amd64-3.12 02/13/2025 12:19 AM <DIR> . 02/13/2025 12:19 AM <DIR> .. 02/13/2025 12:10 AM 44,032 cx_Logging.cp312-win_amd64.pyd 02/13/2025 12:14 AM 3,326 frozen_application_license.txt 02/13/2025 12:19 AM <DIR> lib 02/13/2025 12:19 AM 139,264 my-service.exe 02/13/2025 12:04 AM 6,925,312 python312.dll 02/13/2025 12:06 AM 99,840 vcruntime140.dll 02/13/2025 12:06 AM 29,184 vcruntime140_1.dll 6 File(s) 7,623,119 bytes 3 Dir(s) 10,450,911,232 bytes free

This build\exe.win-amd64-3.12 directory is basically what I want to package up and ship to users. In this state, I can also test out the Windows service by manually installing it and running it on the build box:

> .\build\exe.win-amd64-3.12\my-service.exe --install default Service installed. > sc start my-service-default SERVICE_NAME: start my-service-default TYPE : 10 WIN32_OWN_PROCESS STATE : 2 START_PENDING (NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN) WIN32_EXIT_CODE : 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x0 WAIT_HINT : 0x7d0 PID : 1732 FLAGS : > sc stop my-service-default SERVICE_NAME: my-service-default TYPE : 10 WIN32_OWN_PROCESS STATE : 1 STOPPED WIN32_EXIT_CODE : 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x0 WAIT_HINT : 0x0 > .\build\exe.win-amd64-3.12\my-service.exe --uninstall default Service uninstalled.

Build Basic MSI Package

Next I can use cx_Freeze's bdist_msi command to create an MSI package that doesn't install or start my service, but simply installs the contents of the build\exe.win-amd64-3.12 into the Program Files directory of a user's Windows machine. First, however, I added a bit more configuration to my cx_Freeze setup script for the bdist_msi command options:

# cx_freeze_setup.py """cx_Freeze setup script.""" from cx_Freeze import Executable, setup from my_service import DESCRIPTION, SERVICE_NAME from my_service import __version__ as VERSION EXECUTABLES = [ Executable( script="src/my_service/windows_service_config.py", base="Win32Service", target_name=SERVICE_NAME, ), ] setup( name=SERVICE_NAME, version=VERSION, description=DESCRIPTION, options={ "build_exe": { "excludes": [ "test", "tkinter", "unittest", ], "includes": [ "_cffi_backend", "cx_Logging", ], "include_msvcr": True, "packages": [ "my_service", ], }, "bdist_msi": { "all_users": True, "initial_target_dir": f"[ProgramFiles64Folder]{DISPLAY_NAME}", # IMPORTANT: generate a unique UUID for your service "upgrade_code": "{26483DF9-540E-43D7-B543-795C62E3AF2D}", }, }, executables=EXECUTABLES, )

You should generate a separate UUID for each project, and then keep than UUID constant for the project's liftetime, so that Windows will know which existing package to upgrade when the user tries to install a new version of it. I generated the UUID for my service by running this command on a Linux box:

$ tr [a-z] [A-Z] < /proc/sys/kernel/random/uuid

Building the MSI is now as simple as running the following command:

> uv run --group freeze cx_freeze_setup.py bdist_msi running bdist_msi running build running build_exe creating directory C:\my_project\build\exe.win-amd64-3.12 copying C:\my_project\.venv\Lib\site-packages\cx_Freeze\bases\Win32Service-cpython-312-win_amd64.exe -> C:\my_project\build\exe.win-amd64-3.12\my-service.exe ... ? api-ms-win-crt-utility-l1-1-0.dll This is not necessarily a problem - the dependencies may not be needed on this platform. installing to build\bdist.win-amd64\msi running install_exe creating build\bdist.win-amd64\msi ... copying build\exe.win-amd64-3.12\vcruntime140_1.dll -> build\bdist.win-amd64\msi creating dist removing 'build\bdist.win-amd64\msi' (and everything under it) > dir dist Volume in drive C is Windows Volume Serial Number is 7EC2-1A39 Directory of C:\my_project\dist 02/13/2025 01:05 AM <DIR> . 02/13/2025 01:05 AM <DIR> .. 02/13/2025 01:05 AM 10,788,864 my-service-1.0.0-win64.msi 1 File(s) 10,788,864 bytes 2 Dir(s) 10,826,158,080 bytes free

This will automatically run the same build_exe command as before, but after that will also create an MSI file with the contents of the generated build\exe.win-amd64-3.12 directory, and save it as the dist\my-service-1.0.0-win64.msi file.

This dist\my-service-1.0.0-win64.msi file can be run directly to launch a graphical installer; or it can be run with the msiexec utility to install silently with no prompts:

> msiexec /i dist\my-service-1.0.0-win64.msi /qn

Configure MSI to Install and Start the Service

To enable my service to install and start automatically when the user installs the MSI package, I had to configure the bdist_msi command with some special MSI data tables for installing a Windows service (ServiceInstall), and for running it (ServiceControl):

# cx_freeze_setup.py """cx_Freeze setup script.""" from re import sub from cx_Freeze import Executable, setup from my_service import DESCRIPTION, SERVICE_NAME from my_service import __version__ as VERSION SERVICE_DEFAULT=default SERVICE_WIN32_OWN_PROCESS = 0x10 SERVICE_AUTO_START = 0x2 SERVICE_ERROR_NORMAL = 0x1 MSIDB_SERVICE_CONTROL_EVENT_START = 0x1 MSIDB_SERVICE_CONTROL_EVENT_STOP = 0x2 MSIDB_SERVICE_CONTROL_EVENT_UNINSTALL_STOP = 0x20 MSIDB_SERVICE_CONTROL_EVENT_UNINSTALL_DELETE = 0x80 EXECUTABLES = [ Executable( script="src/my_service/windows_service_config.py", base="Win32Service", target_name=SERVICE_NAME, ), ] def _make_component_id(executables, index): executable = executables[index] component = f"_cx_executable{index}_{executable}" return sub(r"[^\w.]", "_", component) setup( name=SERVICE_NAME, version=VERSION, description=DESCRIPTION, options={ "build_exe": { "excludes": [ "test", "tkinter", "unittest", ], "includes": [ "_cffi_backend", "cx_Logging", ], "include_msvcr": True, "packages": [ "my_service", ], }, "bdist_msi": { "all_users": True, "initial_target_dir": f"[ProgramFiles64Folder]{DISPLAY_NAME}", # IMPORTANT: generate a unique UUID for your service "upgrade_code": "{26483DF9-540E-43D7-B543-795C62E3AF2D}", "data": { "ServiceInstall": [ ( f"{SERVICE_NAME}-{SERVICE_DEFAULT}Install", # ID f"{SERVICE_NAME}-{SERVICE_DEFAULT}", # Name DISPLAY_NAME, # DisplayName SERVICE_WIN32_OWN_PROCESS, # ServiceType SERVICE_AUTO_START, # StartType SERVICE_ERROR_NORMAL, # ErrorControl None, # LoadOrderGroup None, # Dependencies None, # StartName None, # Password None, # Arguments _make_component_id(EXECUTABLES, 0), # Component DESCRIPTION, # Description ), ], "ServiceControl": [ ( f"{SERVICE_NAME}-{SERVICE_DEFAULT}Control", # ID f"{SERVICE_NAME}-{SERVICE_DEFAULT}", # Name ( MSIDB_SERVICE_CONTROL_EVENT_START + MSIDB_SERVICE_CONTROL_EVENT_STOP + MSIDB_SERVICE_CONTROL_EVENT_UNINSTALL_STOP + MSIDB_SERVICE_CONTROL_EVENT_UNINSTALL_DELETE ), # Event None, # Arguments 0, # Wait _make_component_id(EXECUTABLES, 0), # Component ), ], }, }, }, executables=EXECUTABLES, )

In the above, I've set up a row for the ServiceInstall MSI table to direct the Windows installer to install my service as a Windows service, and configure it to auto-start on system boot. This is basically the equivalent of running the following two commands:

> .\build\exe.win-amd64-3.12\my-service.exe --install default > sc config my-service-default start=auto

I also set up a row for the ServiceControl MSI table to direct the Windows installer to:

  1. Start my service on install (MSIDB_SERVICE_CONTROL_EVENT_START)
  2. Stop the old version of my service on upgrade (MSIDB_SERVICE_CONTROL_EVENT_STOP)
  3. Stop my service on uninstall (MSIDB_SERVICE_CONTROL_EVENT_UNINSTALL_STOP)
  4. Delete my service on uninstall (MSIDB_SERVICE_CONTROL_EVENT_UNINSTALL_DELETE)

This is basically the equivalent of running this command on install:

> sc start my-service-default

These commands on upgrade:

> sc stop my-service-default ... install the new executable and support files ... > sc start my-service-default

And these commands on uninstall:

> sc stop my-service-default > .\build\exe.win-amd64-3.12\my-service.exe --uninstall default

The one tricky part of the above is that in the ServiceInstall and ServiceControl tables, you have to reference the service executable by the component ID that cx_Freeze auto-generates for it — which is based on the index of the corresponding Executable object that you've configured in the setup command's executables option, as well as the string representation of the Executable object itself. I encapsulated the logic to derive this ID in my _make_component_id() function, which takes as arguments the list that will be used for the executables option, as well as the index in that list to the service's Executable object.

Now when building and installing my service's MSI file, we can see that my service is started automatically after install:

> uv run --group freeze cx_freeze_setup.py bdist_msi ... > msiexec /i dist\my-service-1.0.0-win64.msi /qn > sc query my-service-default SERVICE_NAME: my-service-default TYPE : 10 WIN32_OWN_PROCESS STATE : 4 RUNNING (STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN) WIN32_EXIT_CODE : 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x0 WAIT_HINT : 0x0 > sc qc my-service-default [SC] QueryServiceConfig SUCCESS SERVICE_NAME: my-service-default TYPE : 10 WIN32_OWN_PROCESS START_TYPE : 2 AUTO_START ERROR_CONTROL : 1 NORMAL BINARY_PATH_NAME : "C:\Program Files\My Service\my-service.exe" LOAD_ORDER_GROUP : TAG : 0 DISPLAY_NAME : My Service DEPENDENCIES : SERVICE_START_NAME : LocalSystem

Extra: Add Other Executables

That's all I needed for the service itself; but for my project, I also had some CLI (Command Line Interface) scripts I wanted to include in its installed Program Files directory.

For each script, I simply had to add another Executable object to my EXECUTABLES list — for example, I had a my-service-cli script defined in my pyproject.toml file:

# pyproject.toml [project.scripts] my-service-cli = "my_service.cli:main"

So I added a corresponding my-service-cli executable definition to my cx_Freeeze script:

# cx_freeze_setup.py EXECUTABLES = ... Executable( script="src/my_service/cli.py", base="console", target_name="my-service-cli", ), ]

Which resulted in a my-service-cli.exe executable generated by cx_Freeze — and added to my project's Program Files directory when the MSI is installed:

C:\Program Files\My Service\my-service-cli.exe

Extra: Add Icons

To add icons to my executables — and to my installer MSI file — I created a custom ICO icon, saved it in my project's source directory as installer/my_icon.ico, and annotated my Executable and bdist_msi configuration with it:

# cx_freeze_setup.py EXECUTABLES = Executable( script="src/my_service/windows_service_config.py", base="Win32Service", target_name=SERVICE_NAME, icon="installer/my_icon.ico", ), Executable( script="src/my_service/cli.py", base="console", target_name="my-service-cli", icon="installer/my_icon.ico", ), ] ... setup( ... options={ "bdist_msi": { ... "install_icon": "installer/my_icon.ico", ... }, }, )

Extra: Add Other Files

I also had some other miscellaneous files I wanted to include in my project's Program Files directory. That turned out to be as easy as using the include_files option of the build_exe command:

# cx_freeze_setup.py setup( ... options={ "build_exe": { ... "include_files": [ ("LICENSE", "LICENSE.txt"), ("installer/help.url", "help.url"), ("installer/log.txt", "log/README.txt"), ], ... }, }, )

This enabled me to take these files from my project source directory:

LICENSE installer/help.url installer/log.txt

And install them into the C:\Program Files\My Service directory as these files:

LICENSE.txt help.url log\README.txt

The installer automatically creates any necessary subdirectories for these files (such as the log subdirectory).

Extra: Add Start Menu

Finally, I also wanted to add some custom commands and links to things for my project in its own Start Menu folder. That turned out to require adding several more custom MSI tables to the bdist_msi config.

First, I had to create a custom Start Menu folder for my project, using the Directory table:

# cx_freeze_setup.py setup( ... options={ "bdist_msi": { ... "data": { "Directory": [ ( "ProgramMenuFolder", # ID "TARGETDIR", # DirectoryParent ".", # DefaultDir ), ( f"{SERVICE_NAME}Folder", # ID "ProgramMenuFolder", # DirectoryParent DISPLAY_NAME, # DefaultDir ), ], ... }, }, }, )

The first row in the Directory table sets up a reference to Window's Start Menu folder (using special ProgramMenuFolder and TARGETDIR keywords); and the second row creates a new folder named "My Service" in it.

Next, I used the Property table to define installation properties for the Windows' cmd and explorer commands, so that I could use them for several of the Start Menu items themselves:

# cx_freeze_setup.py setup( ... options={ "bdist_msi": { ... "data": { ... "Property": [ ("cmd", "cmd"), ("explorer", "explorer"), ], ... }, }, }, )

Finally, I used the Shortcut table to define each item I wanted to add to my custom Start Menu:

# cx_freeze_setup.py setup( ... options={ "bdist_msi": { ... "data": { ... "Shortcut": [ ( f"{SERVICE_NAME}VersionMenuItem", # ID f"{SERVICE_NAME}Folder", # Directory f"{DISPLAY_NAME} Version", # Name "TARGETDIR", # Component "[cmd]", # Target "/k my-service-cli.exe --version", # Arguments None, # Description None, # Hotkey None, # Icon None, # IconIndex None, # ShowCmd "TARGETDIR", # WkDir ), ( f"{SERVICE_NAME}HelpMenuItem", # ID f"{SERVICE_NAME}Folder", # Directory "Help", # Name "TARGETDIR", # Component "[TARGETDIR]help.url", # Target None, # Arguments None, # Description None, # Hotkey None, # Icon None, # IconIndex None, # ShowCmd "TARGETDIR", # WkDir ), ( ( f"{SERVICE_NAME}LogsMenuItem", # ID f"{SERVICE_NAME}Folder", # Directory "Logs", # Name "TARGETDIR", # Component "[explorer]", # Target "log", # Arguments None, # Description None, # Hotkey None, # Icon None, # IconIndex None, # ShowCmd "TARGETDIR", # WkDir ), ], ... }, }, }, )

In each of the above rows, the first column (like f"{SERVICE_NAME}VersionMenuItem") is an arbitrary ID for the row; the second column (f"{SERVICE_NAME}Folder") is a reference to the second row in the Directory table (directing the installer to create the shortcut in my service's custom Start Menu folder); and the third column (like f"{DISPLAY_NAME} Version") is the display name for the shortcut.

The fifth and sixth columns are the command and command arguments to use for the shortcut; and the last column is a reference to the working directory in which to run the command. So my first row defined a shortcut that is equivalent to opening up a command prompt and running the following commands:

> cd C:\Program Files\My Service > my-service-cli.exe --version

My second row defined a shortcut that's equivalent to double-clicking on the help.url file that I installed into my project's Program Files directory with the include_files config from the earlier Add Other Files section. And my third row defined a shortcut that's equivalent to opening Windows' File Explorer to the C:\Program Files\My Service\log directory (created via the same include_files config).

Finished Product

With the above extras, my complete cx_Freeze setup script ended up looking like the following:

# cx_freeze_setup.py """cx_Freeze setup script.""" from re import sub from cx_Freeze import Executable, setup from my_service import DESCRIPTION, SERVICE_NAME from my_service import __version__ as VERSION SERVICE_DEFAULT=default SERVICE_WIN32_OWN_PROCESS = 0x10 SERVICE_AUTO_START = 0x2 SERVICE_ERROR_NORMAL = 0x1 MSIDB_SERVICE_CONTROL_EVENT_START = 0x1 MSIDB_SERVICE_CONTROL_EVENT_STOP = 0x2 MSIDB_SERVICE_CONTROL_EVENT_UNINSTALL_STOP = 0x20 MSIDB_SERVICE_CONTROL_EVENT_UNINSTALL_DELETE = 0x80 EXECUTABLES = [ Executable( script="src/my_service/windows_service_config.py", base="Win32Service", target_name=SERVICE_NAME, icon="installer/my_icon.ico", ), Executable( script="src/my_service/cli.py", base="console", target_name="my-service-cli", icon="installer/my_icon.ico", ), ] def _make_component_id(executables, index): executable = executables[index] component = f"_cx_executable{index}_{executable}" return sub(r"[^\w.]", "_", component) setup( name=SERVICE_NAME, version=VERSION, description=DESCRIPTION, options={ "build_exe": { "excludes": [ "test", "tkinter", "unittest", ], "includes": [ "_cffi_backend", "cx_Logging", ], "include_files": [ ("LICENSE", "LICENSE.txt"), ("installer/help.url", "help.url"), ("installer/log.txt", "log/README.txt"), ], "include_msvcr": True, "packages": [ "my_service", ], }, "bdist_msi": { "all_users": True, "initial_target_dir": f"[ProgramFiles64Folder]{DISPLAY_NAME}", "install_icon": "installer/my_icon.ico", # IMPORTANT: generate a unique UUID for your service "upgrade_code": "{26483DF9-540E-43D7-B543-795C62E3AF2D}", "data": { "Directory": [ ( "ProgramMenuFolder", # ID "TARGETDIR", # DirectoryParent ".", # DefaultDir ), ( f"{SERVICE_NAME}Folder", # ID "ProgramMenuFolder", # DirectoryParent DISPLAY_NAME, # DefaultDir ), ], "Property": [ ("cmd", "cmd"), ("explorer", "explorer"), ], "ServiceInstall": [ ( f"{SERVICE_NAME}-{SERVICE_DEFAULT}Install", # ID f"{SERVICE_NAME}-{SERVICE_DEFAULT}", # Name DISPLAY_NAME, # DisplayName SERVICE_WIN32_OWN_PROCESS, # ServiceType SERVICE_AUTO_START, # StartType SERVICE_ERROR_NORMAL, # ErrorControl None, # LoadOrderGroup None, # Dependencies None, # StartName None, # Password None, # Arguments _make_component_id(EXECUTABLES, 0), # Component DESCRIPTION, # Description ), ], "ServiceControl": [ ( f"{SERVICE_NAME}-{SERVICE_DEFAULT}Control", # ID f"{SERVICE_NAME}-{SERVICE_DEFAULT}", # Name ( MSIDB_SERVICE_CONTROL_EVENT_START + MSIDB_SERVICE_CONTROL_EVENT_STOP + MSIDB_SERVICE_CONTROL_EVENT_UNINSTALL_STOP + MSIDB_SERVICE_CONTROL_EVENT_UNINSTALL_DELETE ), # Event None, # Arguments 0, # Wait _make_component_id(EXECUTABLES, 0), # Component ), ], "Shortcut": [ ( f"{SERVICE_NAME}VersionMenuItem", # ID f"{SERVICE_NAME}Folder", # Directory f"{DISPLAY_NAME} Version", # Name "TARGETDIR", # Component "[cmd]", # Target "/k my-service-cli.exe --version", # Arguments None, # Description None, # Hotkey None, # Icon None, # IconIndex None, # ShowCmd "TARGETDIR", # WkDir ), ( f"{SERVICE_NAME}HelpMenuItem", # ID f"{SERVICE_NAME}Folder", # Directory "Help", # Name "TARGETDIR", # Component "[TARGETDIR]help.url", # Target None, # Arguments None, # Description None, # Hotkey None, # Icon None, # IconIndex None, # ShowCmd "TARGETDIR", # WkDir ), ( ( f"{SERVICE_NAME}LogsMenuItem", # ID f"{SERVICE_NAME}Folder", # Directory "Logs", # Name "TARGETDIR", # Component "[explorer]", # Target "log", # Arguments None, # Description None, # Hotkey None, # Icon None, # IconIndex None, # ShowCmd "TARGETDIR", # WkDir ), ], }, }, }, executables=EXECUTABLES, )

Friday, October 25, 2019

Adapting PostgreSQL Timestamps To Arrow With Psycopg2

I did some digging the other day to try to figure out how to use the excellent Python datetime library Arrow with the workhorse psycopg2 Python-PostgreSQL database adapter (plus the nifty Peewee ORM on top of psycopg2). I was pleasantly surprised how easy and painless it was to implement, with help from a blog post by Omar Rayward, and the psycopg2 docs (and source code) as a guide.

There are 5 core PostgreSQL date/time types that Arrow can handle, which psycopg2 maps to the 3 core Python date/time classes — by default through 4 core psycopg2 datatypes:

PostgreSQL Type Example Output Psycopg2 Type Python Type
timestamp [without time zone] 2001-02-03 04:05:06 PYDATETIME datetime
timestamp with time zone 2001-02-03 04:05:06-07 PYDATETIMETZ datetime
date 2001-02-03 PYDATE date
time [without time zone] 04:05:06 PYTIME time
time with time zone 04:05:06-07 PYTIME time

Arrow can be used to handle each of these 5 types, via its single Arrow class. Here's how you set up the mappings:

import arrow import psycopg2.extensions def adapt_arrow_to_psql(value): """Formats an Arrow object as a quoted string for use in a SQL statement.""" # assume Arrow object is being used for TIME datatype if date is 1900 or earlier if value.year <= 1900: value = value.format("HH:mm:ss.SZ") elif value == arrow.Arrow.max: value = "infinity" elif value == arrow.Arrow.min: value = "-infinity" return psycopg2.extensions.AsIs("'{}'".format(value)) # register adapter to format Arrow objects when passed as parameters to SQL statements psycopg2.extensions.register_adapter(arrow.Arrow, adapt_arrow_to_psql) def cast_psql_date_to_arrow(value, conn): """Parses a SQL timestamp or date string to an Arrow object.""" # handle NULL and special "infinity"/"-infinity" values if not value: return None elif value == "infinity": return arrow.Arrow.max elif value == "-infinity": return arrow.Arrow.min return arrow.get(value) def cast_psql_time_to_arrow(value, conn): """Parses a SQL time string to an Arrow object.""" # handle NULL if not value: return None # handle TIME, TIME with fractional seconds (.S), and TIME WITH TIME ZONE (Z) return arrow.get(value, ["HH:mm:ss", "HH:mm:ss.S", "HH:mm:ssZ", "HH:mm:ss.SZ"]) # override default timestamp/date converters # to convert from SQL timestamp/date results to Arrow objects psycopg2.extensions.register_type(psycopg2.extensions.new_type( ( psycopg2.extensions.PYDATETIME.values + psycopg2.extensions.PYDATETIMETZ.values + psycopg2.extensions.PYDATE.values ), "ARROW", cast_psql_date_to_arrow, )) # override default time converter to convert from SQL time results to Arrow objects psycopg2.extensions.register_type(psycopg2.extensions.new_type( psycopg2.extensions.PYTIME.values, "ARROW_TIME", cast_psql_time_to_arrow ))

The 3 slightly tricky bits are:

  1. Deciding whether to format an Arrow object as a date or a time (in adapt_arrow_to_psql()) — you may want to handle it differently, but since Arrow will parse times without dates as occurring on "0001-01-01", the simplest thing to do is assume a date with an early year (like 1900 or earlier) represents a time instead of a date (which allows round-tripping of times from PostgreSQL to Arrow and back).
  2. Handling PostgreSQL's special "-infinity" and "infinity" values when converting between PostgreSQL and Arrow dates (in adapt_arrow_to_psql() and cast_psql_date_to_arrow()) — Arrow.min and Arrow.max are the closest equivalents.
  3. Handling the 4 different time variants that PostgreSQL emits (in cast_psql_time_to_arrow()):
    • "12:34:56" (no fractional seconds or time zone)
    • "12:34:56.123456" (fractional seconds but no time zone)
    • "12:34:56-07" (no fractional seconds but time zone)
    • "12:34:56.123456-07" (fractional seconds and time zone)

With those mappings in place, you can now use Arrow objects natively with psycopg2:

import arrow import psycopg2 def test_datetimes(): conn = psycopg2.connect(dbname="mydbname", user="myuser") try: cur = conn.cursor() cur.execute(""" CREATE TABLE foo ( id SERIAL PRIMARY KEY, dt TIMESTAMP, dtz TIMESTAMP WITH TIME ZONE, d DATE, t TIME, twtz TIME WITH TIME ZONE ) """) cur.execute( "INSERT INTO foo (dt, dtz, d, t, twtz) VALUES (%s, %s, %s, %s, %s)", ( arrow.get("2001-02-03 04:05:06"), arrow.get("2001-02-03 04:05:06-07"), arrow.get("2001-02-03"), arrow.get("04:05:06", "HH:mm:ss"), arrow.get("04:05:06-07", "HH:mm:ssZ"), ), ) cur.execute("SELECT * FROM foo") result = cur.fetchone() assert result[1] == arrow.get("2001-02-03 04:05:06") assert result[2] == arrow.get("2001-02-03 04:05:06-07") assert result[3] == arrow.get("2001-02-03") assert result[4] == arrow.get("04:05:06", "HH:mm:ss") assert result[5] == arrow.get("04:05:06-07", "HH:mm:ssZ") finally: conn.rollback()

Or with the Peewee ORM, you can use Peewee's built-in date/time fields, and pass and receive Arrow objects to/from those fields:

import arrow import peewee import playhouse.postgres_ext db = playhouse.postgres_ext.PostgresqlExtDatabase("mydbname", user="myuser") class Foo(peewee.Model): dt = peewee.DateTimeField( default=arrow.utcnow, constraints=[peewee.SQL("DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')")], ) dtz = playhouse.postgres_ext.DateTimeTZField( default=arrow.utcnow, constraints=[peewee.SQL("DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')")], ) d = peewee.DateField( default=arrow.utcnow, constraints=[peewee.SQL("DEFAULT (CURRENT_DATE AT TIME ZONE 'UTC')")], ) t = peewee.TimeField( default=lambda: arrow.utcnow().time(), constraints=[peewee.SQL("DEFAULT (CURRENT_TIME AT TIME ZONE 'UTC')")], ) class Meta: database = db def test_datetimes(): with db.transaction() as tx: try: Foo.create_table() result = Foo.get_by_id( Foo.create( dt=arrow.get("2001-02-03 04:05:06"), dtz=arrow.get("2001-02-03 04:05:06-07"), d=arrow.get("2001-02-03"), t=arrow.get("04:05:06", "HH:mm:ss"), ).id ) assert result.dt == arrow.get("2001-02-03 04:05:06") assert result.dtz == arrow.get("2001-02-03 04:05:06-07") assert result.d == arrow.get("2001-02-03") assert result.t == arrow.get("04:05:06", "HH:mm:ss") finally: tx.rollback()

Sunday, May 22, 2016

Powerline Subversion Status

For a while I've been using Jasper N. Brouwer's Powerline Gitstatus plugin for the nifty Powerline shell prompt, and it's become a pretty much indispensable part of my Git workflow. I haven't been able to find something comparable for subversion, so I created something myself: Powerline SVN Status. Here's a screenshot of it in action:

Powerline SVN Status Screenshot

I don't use Subversion all that much any more — mainly for a few older projects — but when I do, having a plugin like this makes my life easier, reminding me when I have some changes I need to check in, or if I have some untracked files I need to add. It's also really helpful when you've switched to a branch, having a branch indicator to remind you that you're not looking at the trunk anymore.

This plugin is just a simple Python class that calls svn info and svn status when in a Subversion working directory. With Powerline all set up, you can simply install it via Pip:

pip install powerline-svnstatus
And then add the following block to your Powerline segment configuration to activate it (I put this in my ~/.config/powerline/themes/shell/default_leftonly.json file):
{
    "function": "powerline_svnstatus.svnstatus",
    "priority": 40
},

Sunday, May 1, 2016

Xenial Ansible

I started building out some Ubuntu 16.04 (Xenial Xerus) servers this weekend with Ansible, and was impressed by how smoothly it went. The only major issue I encountered was that Ansible requires Python 2.x, whereas Ubuntu 16.04 ships Python 3.5 by default. Fortunately, it's not too hard to work around; here's how I fixed that — and a couple of other issues specific to the servers I was building out:

Python 2

Since Ansible doesn't work with Python 3, and that's what Ubuntu 16.04 provides by default, this is the error I got when I tried running Ansible against a newly-booted server:

/usr/bin/python: not found

So I had to make this the very first Ansible play (bootstrapping the ability of Ansible to use Python 2 for the rest of its tasks, as well as for its other record keeping — like gathering facts about the server):

- name: bootstrap python 2
  gather_facts: no
  tasks:
  - raw: sudo apt-get update -qq && sudo apt-get install -qq python2.7

And in the inventory variables (or group variables) for the server, I had to add this line (directing it to use Python 2 instead of the server's default Python):

ansible_python_interpreter: /usr/bin/python2.7

Aptitude

The next hiccup I ran into was using the Ansible apt module with the upgrade=full option. This option is implemented by using the aptitude program — which Ubuntu no longer installs by default. I was getting this error trying to use that option:

Could not find aptitude. Please ensure it is installed.

So I just tweaked my playbook to install the aptitude package first before running apt: upgrade=full:

- name: install apt requirements
  become: yes
  apt: pkg=aptitude

- name: update pre-installed packages
  become: yes
  apt: upgrade=full update_cache=yes

Mount with nobootwait

Then I started running into some minor issues that were completely unrelated to Ansible — simply changes Ubuntu had picked up between 15.10 and 16.04. The first of these was the nobootwait option for mountall (eg in /etc/fstab mountpoints). This option seems to be no longer supported — the server hung after rebooting, with this message in the syslog:

Unrecognized mount option "nobootwait" or missing value

Maybe this is just an issue with AWS EC2 instance-store volumes, but I had to change the /etc/fstab definition for the server's instance-store volume from this:

/dev/xvdb /mnt auto defaults,noatime,data=writeback,nobootwait,comment=cloudconfig 0 2

To this:

/dev/xvdb /mnt auto defaults,noatime,data=writeback,comment=cloudconfig 0 2

Java 7

The Ubuntu 16.04 repo no longer includes Java 6 or 7 — only Java 8 and 9. I got this error message trying to install Java 7:

No package matching 'openjdk-7-jdk' is available

So I first had to add a PPA for OpenJDK 7, and then could install it:

- name: register java 7 ppa
  become: yes
  apt_repository: repo=ppa:openjdk-r/ppa

- name: install java 7
  become: yes
  apt: pkg=openjdk-7-jdk

But the PPA doesn't include the timezone database, so the time formatting in our app was restricted to GMT. So I had to "borrow" the timezone data from Ubuntu 14.04:

- name: download tzdata-java
  get_url:
    url: http://mirrors.kernel.org/ubuntu/pool/main/t/tzdata/tzdata-java_2016d-0ubuntu0.14.04_all.deb
    dest: ~/tzdata-java.deb
    checksum: sha256:5131aa5219739ac58c00e18e8c9d8c5d6c63fc87236c9b5f314f7d06b46b79fb

- name: install tzdata-java
  become: yes
  command: dpkg --ignore-depends=tzdata -i tzdata-java.deb

That's probably going to be broken in a month or two after the next tzdata update, but it's good enough for now. Later I'll put together some Ansible tasks to build the data from source (although obviously the long-term solution is to upgrade to java 8).

Update 5/14/2016: I created a Xenial tzdata-java package to simplify the installation of Java 7 timezone data.

MySQL root

The final issue I hit was with the MySQL root user. Unlike previous versions of Ubuntu, which by default would come with 3 or 4 MySQL root users (covering all the different variations for naming your local host), and all with empty passwords, Ubuntu 16.04 comes with just a single root user — with which you can login only via MySQL's auth_socket plugin. So I was getting this error trying to login to MySQL as root the old way:

unable to connect to database, check login_user and login_password are correct or /home/me/.my.cnf has the credentials

The new way is simply to login to MySQL using the system's root user (ie sudo mysql). In Ansible tasks (such as those using the mysql_db module), this just means using the become (aka sudo) directive, and omitting the login_user and login_password options:

- name: create new database
  become: yes
  mysql_db: name=thedata

Saturday, May 29, 2010

Free Web Hosting on App Engine

I don't have much going on at www.swwomm.com; just a few static mockups and other documents (this blog is hosted by blogger.com). So I finally got around to transferring it from a paid host (lylix; no complaints other than that they have the gall to charge money for their services) to a free one: Google's App Engine (GAE).

I'm not sure why there isn't already a cookbook for transferring a static site to GAE, since it's pretty easy and painless — so here's my recipe.

Gotchas

But first, note that your lunch is not completely free — Google will 503 your ass if you exceed its (extremely generous) daily quotas for bandwidth and processing power.

Also, there are a few other GAE gotchas which apply to static content:

  1. Can't host a "naked" domain (ie example.com instead of www.example.com).
  2. Limit of 3000 files per app.
  3. No automatic directory listings.
  4. No automatic directory redirect (ie redirect from /foo to /foo/).
  5. No custom 404 page.

You can get around #3 and #4, however (as I describe below).

Setup

The first thing you need to do is get the GAE SDK. I'm using the version for python on linux.

With the SDK installed, you can start developing right away on your local dev machine. To deploy an app, of course, you need to sign up for a GAE account. They make you verify your account with an SMS message, so have your cell phone ready.

Hello World

Building a static app is really simple:

  1. Create a directory for your project (ie myapp).
  2. Create a sub-directory named static (or really, whatever you want to name it) inside the project directory. This will be the web root.
  3. Copy your static files into static.
  4. Add a boilerplate app.yml to the project directory.

This is the boilerplate app.yml:

application: myapp version: 1 runtime: python api_version: 1 default_expiration: "1d" handlers: # show index.html for directories - url: (.*/) static_files: static\1index.html upload: static(.*)index.html # all other files - url: / static_dir: static

The first line (application: myapp) specifies the name of your app. This name doesn't matter for development on your local machine; but when you use the GAE dashboard to create a new app, you'll be prompted for a name. The name must be unique globally on GAE (ie it has to be a name no other GAE user has claimed for his or her app), and GAE uses it in the default url for your application (ie http://myapp.appspot.com/). Once you've created the name and set up the app in the GAE dashboard, go back and change it here in app.yml.

The default_expiration setting is the default http cache-age for your static files; "1d" = one day, "4h" = 4 hours, etc. You can configure a separate expiration time for each url handler, but "1d" is probably good for most static content.

The first url in the handlers section captures all urls which end with a trailing-slash. These are directories; with static content you usually either want to display the index.html file of the requested directory, or, if there's no index.html, just the bare directory listing. Unfortunately GAE doesn't support listing static files, so this handler always just tries to display the index.html file in the requested directory.

The second url in the handlers section captures all other urls, and simply serves the requested static file.

Test It Out

At this point, you've already built a fully-functioning GAE app. You can test it out by running the test appserver (where $APPENGINE_HOME is the path to the GAE SDK on your local box, and $MYAPP_HOME is the path to your project directory):

$ $APPENGINE_HOME/dev_appserver.py $MYAPP_HOME

This boots up a test GAE appserver on port 8080. Enter http://localhost:8080/ into your browser address bar; it should load up the index.html from the root of your static directory.

Directory Woeage

But as I pointed out above, if you navigate to a sub-directory, and omit the trailing slash (ie http://localhost:8080/foo), you won't see the index.html for that sub-directory — you'll just get a blank page (and Google's generic 404 page when deployed to GAE).

This we can fix, however, by implementing a trivial RequestHandler in python. Create a file called directories.py (or whatever the heck you want to call it) in your project directory, and dump this into it:

import cgi from google.appengine.ext import webapp from google.appengine.ext.webapp.util import run_wsgi_app class RedirectToDirectory(webapp.RequestHandler): def get(self): self.redirect(self.request.path + "/", permanent=True) application = webapp.WSGIApplication([('/.*', RedirectToDirectory)], debug=True) def main(): run_wsgi_app(application) if __name__ == "__main__": main()

This creates a RequestHandler called RedirectToDirectory; this class simply appends a trailing slash to the current url and redirects. The other non-boilerplate line is just below, where RedirectToDirectory is registered to be used for all urls (/.*) handled by this directories.py script.

Next, drop in a url entry for the directories.py script into the handlers section of your app.yml (and yes, the order of the url entries is important):

application: myapp version: 1 runtime: python api_version: 1 default_expiration: "1d" handlers: # show index.html for directories - url: (.*/) static_files: static\1index.html upload: static(.*)index.html # redirect to directories (/foo to /foo/) - url: .*/[^.]+ script: directories.py # all other files - url: / static_dir: static

This url entry will capture all the urls which don't end in a trailing slash and don't have a file extension. It will handle requests for these urls by sending them to your directories.py script, which in turn will redirect them back to your app — but with a trailing slash this time.

Directory Listings

But if your directory doesn't have a index.html file, you're still SOL — unlike a normal webserver, you can't configure GAE to just display the directory listing. And you can't just implement a handler to do this — GAE apps don't have access to their static filesystem.

One possible workaround to this would be to store all your files as entries in the Big Table DB, and then serve them (and the directory listings) dynamically. This would require writing a bunch of code, however, when all you really want is just to serve some freaking static files already.

So I compromised and wrote a simple perl script which automatically creates static index.html files for a configurable list of static directories. To make it work, create a directories sub-directory in your project, and add to it four files:

make.pl
#!/usr/bin/perl -w open LIST, "directories/list.txt" or die $!; while (<LIST>) { s/\n//; # strip newline $title = $dir = $_; $title =~ s!.*/!!; # strip path from directory name # open directory index.html for writing open INDEX, ">$dir/index.html" or die $!; # dump header template into index.html # replacing %title% with directory name open HEAD, "directories/head.html" or die $!; while (<HEAD>) { s/%title%/$title/g; print INDEX; } close HEAD; # dump directory listing into index.html open DIR, "ls -lh $dir |" or die $!; while (<DIR>) { s/\n//; # strip newline # parse fields listed for each file @fields = split / +/, $_, 8; # skip lines that aren't file listings # and also skip this index.html next if ($#fields < 7 || $fields[7] eq 'index.html'); # print a table row for this file print INDEX '<tr><td class="name"><a href="' . $fields[7] . '">' . $fields[7] . '</a></td><td class="size">' . format_size($fields[4], $fields[0]) . '</td><td class="modified">' . $fields[5] . ' ' . $fields[6] . '</td></tr>' . "\n"; } close DIR; close INDEX; # dump footer into index.html `cat directories/foot.html >> $dir/index.html`; } close LIST; # format file size a little nicer than ls sub format_size { my($size, $perm) = @_; # skip for subdirectories return '-' if $perm =~ /^d/; # add bytes abbr $size .= 'B' if $size =~ /\d$/; return $size; }

This is the perl script. When you create it, make sure you make it executable:

$ chmod +x directories/make.pl

When you run it (after you've created the other three files), make sure you run it from your project directory:

$ cd myapp $ directories/make.pl
list.txt
static/foo static/foo/bar static/baz

This is the list of directories for which to auto-generate index.html files. Please note that the make.pl script will delete the existing index.html files in these directories. So make sure that you list only directories which don't have a custom index.html. (Plus this is another good reason to be using version control on your project.)

head.html
<html> <head> <title>%title% - My App</title> </head> <body> <h1>%title%</h1> <table> <thead> <tr><th>Name</th><th>Size</th><th>Modified</th></tr> </thead> <tbody>

This is the first part of template for the auto-generated index.html files. The perl script will replace the instances of %title% in this file with the directory name.

foot.html
</tbody> </table> </body> </html>

This is the second part of the template.

So add to directories/list.txt the paths of the directories you want to have listings, customize directories/head.html and directories/foot.html to your liking, and run directories/make.pl. This will create your directory-listing index.html files.

Deploy

And now you're ready to deploy. Make sure you've created the app in the GAE dashboard and updated your app.yml with its appspot name; then upload it:

$ $APPENGINE_HOME/appcfg.py update $MYAPP_HOME

You'll have to enter your GAE credentials, and wait for a minute or two while your app boots up on GAE; when your app is deployed and ready to use, the script will let you know. If you screw up your credentials, delete your ~/.appcfg_cookies file (which caches them), and try again.

If you've also signed up for Google Apps, you can use your (separate) Google Apps dashboard to configure a sub-domain of a domain you own to point to your deployed app (ie http://www.example.com/). Otherwise, you have to access it via its sub-domain of the appspot domain (ie http://myapp.appspot.com/).

Bon appétit!