1. GroupDocs Documentation
  2. /
  3. GroupDocs.Assembly Product Family
  4. /
  5. GroupDocs.Assembly for Python
  6. /
  7. Developer Guide
  8. /
  9. Basic Usage
  10. /
  11. Generate from Database

Generate Document from Database

Overview

GroupDocs.Assembly for Python via .NET generates documents from the results of your database queries. Python’s database drivers return rows; turn each row into a dict keyed by column name, and the template reads the columns by name, as it would read the columns of a .NET DataTable. The examples use Python’s built-in sqlite3 module with an in-memory database they create; any DB-API driver (PostgreSQL, MySQL, SQL Server, Oracle) returns rows the same way.

The main classes involved are:

  • DocumentAssembler - assembles documents
  • DataSourceInfo - wraps data sources
  • Python’s sqlite3 module (or your database’s DB-API driver): connections, cursors and rows

Here are the steps to generate documents from databases:

  • Connect to your database with its Python driver
  • Execute SQL queries and turn the rows into a list of dicts
  • Create a template document with expression tags referencing table and column names
  • Use DocumentAssembler to assemble the template with the rows
  • Save the assembled document
Note
The ADO.NET classes a .NET application passes to the engine (DataSet, DataTable, DataView, IDataReader) have no Python form. In Python, a table is a list of dicts, several tables are a dict of such lists, and related rows are nested lists. A column’s Python type decides what the template sees: sqlite3 returns int, float, str, bytes and None.

Generate Document from Query Rows

The simplest approach is a list of dicts, one per row:

import sqlite3

from groupdocs.assembly import DataSourceInfo, DocumentAssembler


def create_database():
    # An in-memory database standing in for your own
    connection = sqlite3.connect(":memory:")
    connection.executescript("""
        CREATE TABLE Customers (CustomerId INTEGER PRIMARY KEY, CustomerName TEXT, Email TEXT, Balance REAL);
        INSERT INTO Customers VALUES (1, 'Quayside Bistro', 'orders@quaysidebistro.example', 1250.75);
        INSERT INTO Customers VALUES (2, 'Lantern Street Café', 'orders@lanternstreetcafe.example', 2480.00);
        INSERT INTO Customers VALUES (3, 'Saltmarsh Deli', 'orders@saltmarshdeli.example', 640.50);
        INSERT INTO Customers VALUES (4, 'Pier Nine Kitchen', 'orders@piernine.example', 1890.20);
    """)
    return connection


def query(connection, sql):
    # Each row becomes a dict keyed by column name, as the template reads it
    connection.row_factory = sqlite3.Row
    return [dict(row) for row in connection.execute(sql)]


connection = create_database()
try:
    customers = query(connection, "SELECT CustomerName, Email, Balance FROM Customers")
finally:
    connection.close()

# Assemble document
assembler = DocumentAssembler()
assembler.assemble_document("customer-accounts.docx", "customer-accounts-report.docx",
                            DataSourceInfo(customers, "customers"))

print("Document generated from query rows successfully.")

The following sample file is used in this example: customer-accounts.docx

Binary file (DOCX, 8 KB)

Download full output

Template Example for Query Rows

In your template, reference columns by name:

Customers List:

<<foreach [in customers]>>
<<[CustomerName]>> - <<[Email]>> - Balance: <<[Balance]:"C">>
<</foreach>>

Generate Document from Several Tables

When working with multiple tables, put each table’s rows under its name in one dict:

import sqlite3

from groupdocs.assembly import DataSourceInfo, DocumentAssembler


def create_database():
    # An in-memory database standing in for your own
    connection = sqlite3.connect(":memory:")
    connection.executescript("""
        CREATE TABLE Customers (CustomerId INTEGER PRIMARY KEY, CustomerName TEXT, Email TEXT, Balance REAL);
        INSERT INTO Customers VALUES (1, 'Quayside Bistro', 'orders@quaysidebistro.example', 1250.75);
        INSERT INTO Customers VALUES (2, 'Lantern Street Café', 'orders@lanternstreetcafe.example', 2480.00);
        INSERT INTO Customers VALUES (3, 'Saltmarsh Deli', 'orders@saltmarshdeli.example', 640.50);
        INSERT INTO Customers VALUES (4, 'Pier Nine Kitchen', 'orders@piernine.example', 1890.20);
        CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustomerId INTEGER, Amount REAL);
        INSERT INTO Orders VALUES (10001, 1, 552.00);
        INSERT INTO Orders VALUES (10002, 2, 522.00);
        INSERT INTO Orders VALUES (10003, 3, 281.00);
        INSERT INTO Orders VALUES (10004, 1, 132.00);
        INSERT INTO Orders VALUES (10005, 2, 192.00);
        INSERT INTO Orders VALUES (10006, 1, 516.00);
        INSERT INTO Orders VALUES (10007, 4, 198.00);
        INSERT INTO Orders VALUES (10008, 3, 456.00);
    """)
    return connection


def query(connection, sql):
    # Each row becomes a dict keyed by column name, as the template reads it
    connection.row_factory = sqlite3.Row
    return [dict(row) for row in connection.execute(sql)]


connection = create_database()
try:
    # Load customers table and orders table
    data_set = {
        "Customers": query(connection, "SELECT * FROM Customers"),
        "Orders": query(connection, "SELECT * FROM Orders"),
    }
finally:
    connection.close()

assembler = DocumentAssembler()
assembler.assemble_document("customers-and-orders.docx", "customers-and-orders-report.docx",
                            DataSourceInfo(data_set, "ds"))

print("Document generated from several tables successfully.")

The following sample file is used in this example: customers-and-orders.docx

Binary file (DOCX, 8 KB)

Download full output

Template Example for Several Tables

Reference tables by name in your template:

Customers:
<<foreach [in ds.Customers]>>
<<[CustomerName]>> - <<[Email]>>
<</foreach>>

Orders:
<<foreach [in ds.Orders]>>
Order #<<[OrderId]>> - Amount: <<[Amount]:"C">>
<</foreach>>

Reading Rows as a Stream

A .NET application can hand the engine an open IDataReader and let it read the rows one by one. The engine cannot read from a Python cursor: read the rows into a list first, as above, and keep large results small by selecting only the columns and rows the report shows.

Filtering and Sorting Rows

Where .NET filters and sorts a DataView, Python lets the database do it in the query:

import sqlite3

from groupdocs.assembly import DataSourceInfo, DocumentAssembler


def create_database():
    # An in-memory database standing in for your own
    connection = sqlite3.connect(":memory:")
    connection.executescript("""
        CREATE TABLE Customers (CustomerId INTEGER PRIMARY KEY, CustomerName TEXT, Email TEXT, Balance REAL);
        INSERT INTO Customers VALUES (1, 'Quayside Bistro', 'orders@quaysidebistro.example', 1250.75);
        INSERT INTO Customers VALUES (2, 'Lantern Street Café', 'orders@lanternstreetcafe.example', 2480.00);
        INSERT INTO Customers VALUES (3, 'Saltmarsh Deli', 'orders@saltmarshdeli.example', 640.50);
        INSERT INTO Customers VALUES (4, 'Pier Nine Kitchen', 'orders@piernine.example', 1890.20);
    """)
    return connection


def query(connection, sql, parameters=()):
    # Each row becomes a dict keyed by column name, as the template reads it
    connection.row_factory = sqlite3.Row
    return [dict(row) for row in connection.execute(sql, parameters)]


connection = create_database()
try:
    # Filtered and sorted by the database
    customers = query(connection,
                      "SELECT * FROM Customers WHERE Balance > ? ORDER BY CustomerName ASC", (1000,))
finally:
    connection.close()

assembler = DocumentAssembler()
assembler.assemble_document("customer-accounts.docx", "customer-accounts-filtered.docx",
                            DataSourceInfo(customers, "customers"))

print("Document generated from filtered rows successfully.")

The following sample file is used in this example: customer-accounts.docx

Binary file (DOCX, 7 KB)

Download full output

A .NET DataSet relation lets a template call GetChildRows("CustomerOrders") on a customer row. Python rows have no such method: nest each customer’s orders under the customer instead, and the template iterates them as a member:

import sqlite3

from groupdocs.assembly import DataSourceInfo, DocumentAssembler


def create_database():
    # An in-memory database standing in for your own
    connection = sqlite3.connect(":memory:")
    connection.executescript("""
        CREATE TABLE Customers (CustomerId INTEGER PRIMARY KEY, CustomerName TEXT, Email TEXT, Balance REAL);
        INSERT INTO Customers VALUES (1, 'Quayside Bistro', 'orders@quaysidebistro.example', 1250.75);
        INSERT INTO Customers VALUES (2, 'Lantern Street Café', 'orders@lanternstreetcafe.example', 2480.00);
        INSERT INTO Customers VALUES (3, 'Saltmarsh Deli', 'orders@saltmarshdeli.example', 640.50);
        INSERT INTO Customers VALUES (4, 'Pier Nine Kitchen', 'orders@piernine.example', 1890.20);
        CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustomerId INTEGER, Amount REAL);
        INSERT INTO Orders VALUES (10001, 1, 552.00);
        INSERT INTO Orders VALUES (10002, 2, 522.00);
        INSERT INTO Orders VALUES (10003, 3, 281.00);
        INSERT INTO Orders VALUES (10004, 1, 132.00);
        INSERT INTO Orders VALUES (10005, 2, 192.00);
        INSERT INTO Orders VALUES (10006, 1, 516.00);
        INSERT INTO Orders VALUES (10007, 4, 198.00);
        INSERT INTO Orders VALUES (10008, 3, 456.00);
    """)
    return connection


def query(connection, sql, parameters=()):
    # Each row becomes a dict keyed by column name, as the template reads it
    connection.row_factory = sqlite3.Row
    return [dict(row) for row in connection.execute(sql, parameters)]


connection = create_database()
try:
    customers = query(connection, "SELECT * FROM Customers ORDER BY CustomerId")
    # Relate the tables: each customer gets the list of its own orders
    for customer in customers:
        customer["Orders"] = query(connection,
                                   "SELECT OrderId, Amount FROM Orders WHERE CustomerId = ? ORDER BY OrderId",
                                   (customer["CustomerId"],))
finally:
    connection.close()

assembler = DocumentAssembler()
assembler.assemble_document("customer-orders.docx", "customer-orders-report.docx",
                            DataSourceInfo(customers, "customers"))

print("Document generated with related rows successfully.")

The following sample file is used in this example: customer-orders.docx

Binary file (DOCX, 8 KB)

Download full output

Template with related rows:

<<foreach [in customers]>>
Customer: <<[CustomerName]>>
Orders:
<<foreach [in Orders]>>
  - Order #<<[OrderId]>>: <<[Amount]:"C">>
<</foreach>>
<</foreach>>
Warning
When using database connections, always release them. Close the connection in a finally block, as above, or with contextlib.closing. A customer with no orders gets an empty list, which works as long as another customer has orders: an empty list on its own carries no column names, and the template then fails with “Can not get the value of member … on type ‘System.Object’”.

Advanced Usage Topics

To learn more about working with complex data, several data sources, and performance optimization for large datasets, please refer to the advanced usage section.

More resources

GitHub Examples

You may easily run the code above and see the feature in action in our GitHub examples:

Free Online Apps

Along with the full-featured library, we provide simple but powerful free online apps.

To assemble documents from templates and data sources, you can use the online apps from the GroupDocs.Assembly App Product Family.

Close
Loading

Analyzing your prompt, please hold on...

An error occurred while retrieving the results. Please refresh the page and try again.

© Groupdocs 2001-2026. All Rights Reserved.