Sqlite format output in python

Question

I've got a simple flask web-app set up where the user can add and delete tasks from a database. All the entries in the database are displayed in a template, sorted by the type they're assigned. I can't format the output to be at least somewhat readable, though. How do i do that?

The actual database uses different values and whatnot, so not all of them may make sense right now.

This is the function that gets all the entries from my sqlite database:

def get_tsk_by_type(type):
  c.execute("SELECT * FROM tasks WHERE type=:type", {'type': type})
  result = c.fetchall()
  return result

The database:

c.execute("""CREATE TABLE IF NOT EXISTS tasks (
            type text,
            description text,
            amount integer,
            id integer
            )""")

And here's how i'm returning all the entries that are then displayed in in a template. There is also a function to delete tasks if you input their id.

@app.route('/', methods = ['GET', 'POST'])
def index():
  form = DeleteForm()
  curr_homework = str(get_tsk_by_type("homework"))
  curr_cleaning = str(get_tsk_by_type("cleaning"))
  curr_cooking = str(get_tsk_by_type("cooking"))
  if form.validate_on_submit():
    try:
      conn = sqlite3.connect('tasks.db', check_same_thread=False)
      c = conn.cursor()
      delete = request.form['delete']
      if (delete):
        remove_tsk(delete)
      return redirect('/')
      conn.close()
    except:
      return "Something went wrong while submitting the form"
  return render_template('index.html', curr_homework = curr_homwork, curr_cleaning = curr_cleaning, curr_cooking = curr_cooking, form = form)

The relevant parts of my index.html look like this:

{% block content %}
    <div>
        <p>
            <span>Currently registered homework: {{ curr_homework }}</span><br />
            <span>Currently registered cleaning tasks: {{ curr_cleaning }}</span><br />
            <span>Currently registered cooking tasks {{ curr_cooking }}</span>
        </p>
    </div>
{% endblock content %}

However, the output i'm getting looks like this:

Currently registered homework: [('homework', 'math', 1, 'df19c0b1-a2128-431274-2e32-3a2f901b1b26')]
Currently registered cleaning tasks: [('cleaning', 'kitchen', 1, 'df19c0b1-aa18-4874-9e32-3a2f901b1b26')]
Currently registered cooking tasks: [('cooking', 'lunch', 1, '0697c139-0299-4c93-88ac-c07d77377796')]

I've tried for-loops and that kinda thing but it only ever returns the first tuple inside the list that get_tsk_by_type() returns. I also tried panda but i couldn't get that to output the way i want it to, either. How do i prettify it to the point that it's easily readable? Without brackets etc.? I later want to display each individual task separately, preferably in divs.

mechanical_meat · Accepted Answer · 2020-05-01T21:08:48.137

0

I recommend using a dict cursor so you can access the result elements by name.
You can do that like this (from: https://stackoverflow.com/a/3300514/42346):

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

conn.row_factory = dict_factory

Then you will get this kind of result:

result = c.fetchall()
result
# [{'type':'homework','description':'math',
#   'amount':1,'id':'df19c0b1-a2128-431274-2e32-3a2f901b1b26'}]

Then in your template you can do something like:

  {% for homework in curr_homework %}
    <div>
      <h6>{{ homework['type'] }}</h6>
      <div>{{ homework['description'] }}</div>
    </div>
    {% if not loop.last %}
      <hr>
    {% endif %}
  {% endfor %}

You may benefit from a little bit of re-organization of the database-specific code.
You can do this:

from flask import g

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

def get_db():
    if 'db' not in g:
        conn = sqlite3.connect('tasks.db', check_same_thread=False)
        conn.row_factory = dict_factory
        g.db = conn.cursor()
    return g.db

And then in your view do this:

db = get_db()
db.execute('your query here')

edited May 01 '20 at 21:08

answered May 01 '20 at 19:18

mechanical_meat

163,903
24
228
223

Thanks for your help! Does `conn.row_factory = dict_factory` have to go somewhere specific? `get_tsk_by_type()` is returning tuples, still. Hence the code in my template is returning all the
and divs but not the actual data. Or am i supposed to change `dict_factory()` in any way? – Lacrowx May 01 '20 at 20:56
Yes, it needs to go before your cursor is initialized for the `get_tsk_by_type()` function. – mechanical_meat May 01 '20 at 20:57
Let me add an example of how you might better organize this. – mechanical_meat May 01 '20 at 20:59
@Lacrowx: added example of how to move the connection and cursor row factory code into another function. – mechanical_meat May 01 '20 at 21:04
Thanks a lot, that worked and `get_db()` has made my code a lot cleaner! – Lacrowx May 01 '20 at 21:58
Cheers! Thanks for accepting the answer. Happy coding to you. – mechanical_meat May 01 '20 at 21:58

Sqlite format output in python

1 Answers1