DataGrid - Postgres

Anvil Q&A
1

Pulling data from a Postgres table and want to display in the grid, but nothing is coming up.

ServerComponent:

def hd_log_pull():
  cur = conn.cursor()
  cur.execute("select date,media_type,qty,unit_purpose,by from hd_log order by date desc;")
  hd_data = cur.fetchall()
  #print(hd_data)
  return hd_data

returns

[(datetime.date(2019, 2, 1), ‘1 TB USB HDD’, 1, ‘-’, ‘-’), (datetime.date(2019, 1, 31), ‘1 TB USB HDD’, 1, ‘-’, ‘-’), (datetime.date(2019, 1, 30), ‘1 TB’, 1, ‘-’, -’)]

Code on the panel:

class hd_log(hd_logTemplate):
  def __init__(self, **properties):
    # Set Form properties and Data Bindings.
    
    
    # Any code you write here will run when the form opens.
    self.repeating_panel_1.items = anvil.server.call('hd_log_pull')

I’m missing something, but don’t know what…

2

Have you bound the item variable to the data table’s row template form?

Also, I would recommend doing the form side in the forms “Show” event rather than in the init to make sure everything visual is loaded. That might not be relevant tbh, but I’ve been bitten with things like that before and I do it as a matter of course now.

3

I have not…how should I go about doing that?

4

(just eating dinner - I;'ll give you an example in 20)

1 Like
5

Don’t rush, enjoy your meal!

1 Like
6

Usually you pass a list (or an iterator) of dictionaries to a repeating_panel.items, but your cursor gets an iterator of lists.

You can use a list comprehension to create a list of dictionaries or you can get psycopg2 to create dictionaries for you with this:

import psycopg2.extras
[...]
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
7

Ok, there are a couple of ways to do this with the data grid. This is the most straightforward.

You make the columns names in the data grid the same as the database fields (eg “date”, “media_type”, etc.) and let the grid tie them up automatically.

So, in your SQL you need to ensure you return the data as a dictionary by setting the correct cursor type (@stefano.menci beat me to that one :slight_smile: ). You will get something like this back then :

[
  {
    "date":datetime.date(2019, 2, 1),
    ..etc.
  }
]

See this example, then just replace my hard coded data[] with the result set from the db and it will work.

https://anvil.works/build#clone:F7ZO6XKRTHYNN5MT=KDEVVR3MUNI5RHYTE24F43PM

8

Just a heads-up, to say that we have a working example of a Postgres-backed Anvil app available in our Knowledge Base (just search “postgres” in the Anvil editor or the Learning Centre):

Using Postgres with Anvil apps

9

As always thanks all! Got it work it perfectly!!

10

How would I refresh the panel after I add data? As an example, at the top of the panel I have text boxes for data and a button to add it to my Postgres database. I’d like the panel to refresh after clicking the button displaying the new data entered.

11

From another thread :

I believe that refreshes it, too.

1 Like