Automatically insert SQL data into a reStructuredText file

Check my latest blog post!

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.

1. Create the SQLite3 database productdb.db:

   #!/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:

   #!/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) <abbr title="?, ?">VALUES</abbr> ',
                          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:

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

4. Run the following Python script:

   #!/usr/bin/python
   # coding: utf8
   import sqlite3
   import jinja2
   
   env = jinja2.Environment(loader=jinja2.FileSystemLoader('./'))
   template = env.get_template('modele-sql.rst')
   
   print('Products and versions====================')
   
   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:

   Products and versions
   ====================
   
   Dianthus
   --------
   
   - 1.0
   
   - 1.1
   
   - 1.2
   
   Geum
   ----
   
   - 1.5
   
   - 1.7
   
   - 3.5
   
   Prunus
   ------
   
   - 2.3
   
   - 2.5
   
   - 2.7