# Automatically insert SQL data into a reStructuredText file

> **Note: Specialist technique**
>
> SQL-driven generation keeps product data in a database and generates the corresponding text at build time. The toolchain used here (Python, SQLite, Jinja, and reStructuredText) is one implementation of that idea; see [SQL database](https://docs.redaction-technique.org/en/tech-writing-process/sql-database/) for why the text itself is better kept in files.

## Objective

We're going to create a database of products with their versions, then format this information in a reStructuredText file. This information can then be easily formatted and published in PDF, HTML, or other formats. For a simpler variant that reads data from Python lists rather than a database, see [automatically insert data into a reStructuredText file](https://docs.redaction-technique.org/en/tutorials/auto-insert-data-restructuredtext/).

1. Create the *SQLite3* database `productdb.db`:

    ```python
    #!/usr/bin/python
    # coding: utf8
    import sqlite3
    
    try:
        db = sqlite3.connect('productdb.db')
    
        cursor = db.cursor()
    
        cursor.execute('''CREATE TABLE products (
            product text,
            version text)''')
    
        db.commit()
    
        cursor.close()
        db.close()
    
    except:
        print('Error creating database.')
        exit(1)
    
    print('The database has been created.')
    ```

2. Insert data into the database:

    ```python
    #!/usr/bin/python
    # coding: utf8
    import sqlite3
    
    prods = [('dianthus', '1.0'), ('geum', '1.5'), ('prunus', '2.3'),
             ('dianthus', '1.1'), ('geum', '1.7'), ('prunus', '2.5'),
             ('dianthus', '1.2'), ('geum', '3.5'), ('prunus', '2.7'), ]
    
    try:
        db = sqlite3.connect('productdb.db')
    
        cursor = db.cursor()
    
        for data in prods:
            cursor.execute('INSERT INTO products (product, version) VALUES (?, ?)',
                           data)
    
        db.commit()
    
        cursor.close()
        db.close()
    
    except:
        print('Error inserting data into database.')
        exit(1)
    
    print('Data has been inserted.')
    ```

3. Create the following `modele-sql.rst` file:

    ```rst
    {% for prod in product %}
    {{ prod | capitalize }}
    {% for c in prod %}-{% endfor %}
       {% for ver in version %}
    - {{ ver }}
       {% endfor %}
    {% endfor %}
    ```

4. Run the following Python script:

    ```python
    #!/usr/bin/python
    # coding: utf8
    import sqlite3
    import jinja2
    
    env = jinja2.Environment(loader=jinja2.FileSystemLoader('./'), lstrip_blocks=True)
    template = env.get_template('modele-sql.rst')
    
    print('Products and versions\n=====================')
    
    try:
        db = sqlite3.connect('productdb.db')
    
        cursor = db.cursor()
    
        for myprod in ("dianthus", "geum", "prunus"):
            t = (myprod,)
            cursor.execute('SELECT version FROM products WHERE product = ?', t)
            mylist = []
            for row in cursor:
                mylist.append('{}'.format(*row))
    
            data = {
                'product': [myprod],
                'version': mylist
            }
    
            print(template.render(data))
    
        cursor.close()
        db.close()
    
    except:
        print('Error reading database.')
        exit(1)
    ```

    The following content is displayed:

    ```md
    Products and versions
    =====================

    Dianthus
    --------

    - 1.0

    - 1.1

    - 1.2

    Geum
    ----

    - 1.5

    - 1.7

    - 3.5

    Prunus
    ------

    - 2.3

    - 2.5

    - 2.7
    ```

## Related articles

- [Create different documents from the same sources using Jinja](https://docs.redaction-technique.org/en/tutorials/conditional-text-jinja/)
- [Regular expressions in Python](https://docs.redaction-technique.org/en/tutorials/python-regular-expressions/)
- [sed: modify your text without opening your files](https://docs.redaction-technique.org/en/tutorials/sed-text-editing/)

## Related reading

- [Boost documentation efficiency: how YAML outperforms XML, Markdown, and databases](https://redaction-technique.org/scalable-maintainable-technical-docs-with-yaml) — structured data as a single source for generated docs.

---

Source: https://docs.redaction-technique.org/en/tutorials/auto-insert-sql-data-restructuredtext/