Read, write, or execute SQL against a Microsoft Access database.
Install pyodbc and a compatible system ODBC driver. Supply database, or a complete connection_string that takes precedence over database, driver, and password. The Python package alone does not install the Access ODBC driver.
read:
- access:
database: products.accdb
command: SELECT * FROM products WHERE category = ?
params: [Bearings]
| Parameter | Required | Data Type | Notes |
|---|---|---|---|
| database | string | Access database file path. Not required if connection_string is supplied. | |
| connection_string | string | Full ODBC connection string. If provided, database, driver, and password are ignored. | |
| driver | string | ODBC driver name. Defaults to Microsoft Access Driver (*.mdb, *.accdb). | |
| password | string | Optional database password. | |
| command | ✓ | string | SQL command to select data. Note - using variables here can make your recipe vulnerable to sql injection. Use params if using variables from untrusted sources. |
| columns | string, array | A list with a subset of the columns to import. This is less efficient than specifying in the command. | |
| params | array | Variables to pass to a parameterized query. |
write:
- access:
database: products.accdb
table: products
action: INSERT
| Parameter | Required | Data Type | Notes |
|---|---|---|---|
| database | string | Access database file path. Not required if connection_string is supplied. | |
| connection_string | string | Full ODBC connection string. If provided, database, driver, and password are ignored. | |
| driver | string | ODBC driver name. Defaults to Microsoft Access Driver (*.mdb, *.accdb). | |
| password | string | Optional database password. | |
| table | ✓ | string | The table to write to |
| action | string | INSERT appends, REPLACE recreates the table, FAIL errors if the table exists. Defaults to INSERT. Allowed values: INSERT, REPLACE, FAIL. |
|
| columns | string, array | A list of the columns to write to the table. If omitted, all columns will be written. |
run:
on_success:
- access:
database: products.accdb
command: DELETE FROM staging WHERE batch_id = ?
params: [completed-batch]
| Parameter | Required | Data Type | Notes |
|---|---|---|---|
| database | string | Access database file path. Not required if connection_string is supplied. | |
| connection_string | string | Full ODBC connection string. If provided, database, driver, and password are ignored. | |
| driver | string | ODBC driver name. Defaults to Microsoft Access Driver (*.mdb, *.accdb). | |
| password | string | Optional database password. | |
| command | ✓ | string, array | SQL command or a list of SQL commands to execute |
| params | array | Variables to pass to a parameterized query. |
Use params for SQL values. For writes, INSERT appends, REPLACE recreates the table, and FAIL rejects an existing table. REPLACE discards existing table data. Shared recipe options such as columns, not_columns, and if apply where supported by the recipe engine.