Hello! 
I have a database in postgresql that contains 4 columns of information from a web parser python program. I would like to display the information in a data grid. What would be the simplest way to do so?
The postgresql table is updated quite frequently, the four columns are ā time, source, title, link
Iām fairly new to coding, only a week or so into Python
Would love some assistance!
Thanks
Welcome to the Forum!
Youāve come to the right place. Other Forum members do this sort of thing on a regular basis.
From a really high level (not sure of your programming background outside of Python), letās outline a reasonable plan:
So, all the supporting pieces are available. I donāt connect to Postgres directly, but there are examples, that you can clone and study. Look at the links atop this pageā¦
One last suggestion: get familiar with Pythonās Virtual Environments. Think of a Python Virtual Environment as a kind of sandbox, or walled garden, where you can add all the libraries you need, without polluting the global, system-wide Python installation. And if some other Python program needs a conflicting set of libraries, you can give that program its own virtual environment. Makes such coexistence possible, maybe even easy.
I wish Python could create such environments automatically, but each userās situation is different, so thereās no one-size-fits-all place to put these environments.
First off, thanks for being so welcoming!
This makes sense to me. Iāve used psycopg2 to send python program data to postgres, so I should be able to figure out how to do the reverse. All I need to get familiar with now is Uplink.
This is a great starting point. Thank you!
It is pretty much the same, what you probably want to retrieve from psycopg2 the data as an iterator of the results of whatever data you are trying to gather in the format of a ālist of dictsā so when the data is transmitted to anvil, the format will already match what the data grid is expecting.
What this means from database speak to python speak is you want a list containing the rows from the database as a result, in the format of a {dictionary} that contains the values of that row in a format where they key/value pairs are the column names as the keys, and the values are the cells in each row.
So a basic example would look like this if you printed out a few rows:
[
{'time':time_data, 'source':source_data, 'title':title_data, 'link':link_data},
{'time':time_data, 'source':source_data, 'title':title_data, 'link':link_data},
{'time':time_data, 'source':source_data, 'title':title_data, 'link':link_data},
{'time':time_data, 'source':source_data, 'title':title_data, 'link':link_data},
]
I found this link, it seems pretty standard for a python SQL library:
Edit I forgot to add:
The standard way results are gathered from a SQL database in python (not exclusive to psycopg2) is to execute a command to the database server using a cursor object, if you are retrieving from, instead of sending data to the database, the cursor object itself is where you will get the results from, either by retrieving it through a cursor.method(), or directly iterating over the cursor object itself.
For databases that do not have cursors like MySQL, python creates a ācursor-like-objectā (A pseudo-cursor) that acts kind of like a data streaming object, which is a trap some people fall into, closing the connection before reading the cursor results.
Thank you! Hoping to get some free time to dive into all of this over the next few days.
If it starts with
list. This should be the outermost object used with a Data Grid.set or a dict.
dict. This is what you want as the list members, when feeding a Data Gridset.tuple.A Data Grid will not know what to do with a set or a tuple.
Thank you, that helped clarify. For some reason, when using the RealDictCursor code from the article you provided, the output is a list?
The output is always a list, where each item of the list represents a table row.
By default itās a list of tuples, something like:
[
(1, 2, 3),
(2, 3, 4)
]
With RealDictCursor you get a list of dictionaries instead of tuples, something like this:
[
{"col1": 1, "col2": 2, "col3": 3},
{"col1": 2, "col2": 3, "col3": 4}
]
Each DataGrid row needs a dictionary to know where to put what value.
Ah I see. Why is my RealDictCursor outputting in a different format than what I want? Should it be outputting with curly brackets, quotes, colon etc. ?
My current output looks like a ātupleā, like the example you provided.
Iām on my cell now, so I canāt tryā¦ perhaps because a RealDictCursor is not a real dict 
Itās something similar and behaves in a similar way, but it apparently prints itself out in a different way.
Try to convert it to dict with print(dict(row)).
@ianb posted a link up half a dozen replies that talks about getting the cursor to behave like a dict. Thereās a different way to construct the cursor.
If you pass the results back through an anvil uplink to the server module or a client, this should work, but you are correct that your print statements lead you to the unintuitive conclusion that the format is incorrect.
If you were returning the results to a datagrid from an uplink you should be able to just use
return results
If that does not work, return list(results) should work. If even that does not work, try:
# yes, python will just take a pile of key/value tuples and turn them into a dict
return [dict(x) for x in results]
If you want to know why, keep reading:
The cursor.fetchall() results coming from psycopg2 will always be an iterator of tuples (the ( ) things) long story short, tuples cant be changed in memory once they are created (they can only be replaced), so you cant reach inside the row and change the values.
(again this makes sense if you think about it, the database sent you data, if you changed it, its not going to change in the database soā¦ why would you need to change it.)
When you set the cursor_factory what you are telling it is to give you an iterator of the row tuples that are wrapped by an object (a RealDictCursor) that will apply the column names as keys āon-the-flyā if you request the column name from the object, this is the same behavior that a dictionary would have if it were a dictionary.
The difference goes back to those tuples you arenāt supposed to change, dictionaries can be changed, so it just makes an object that acts like a dictionary when you use it, but not when you look at it with print()
The principle in python is called duck typing (if it acts like a duckā¦ etc). When you send this object through the anvil uplink, it will also on-the-fly check to see if the objects can be easily converted to the standard types that anvil accepts (list, dict etc) and if it looks like a dict on the other side of the anvil-uplink connection it will be one.
So long story short (too late)
Your results object should just work in the data grid if you send it through uplink.
I gotta admit, youāre the best. I will try this out when I get home later. Thanks!
print(dict(row)) worked! thank you
So now that I have the dict, where to I begin with uplink? Do I use server or client?
The docs for anvil-uplink are pretty good:
you are going to pip install anvil-uplink
import anvil.server
anvil.server.connect("<your Server Uplink key>")
@anvil.server.callable
def get_postgresql_data(some_parameter):
results = get_data_from_psycopg2_as_list_of_dicts(some_parameter)
return results
anvil.server.wait_forever()
then in the client browser you can do a:
list_d_for_data_grid = anvil.server.call('get_postgresql_data', some_parameter)
As long as your uplink is running.
Also FYI for the future, if you donāt very specifically treat your db connection objects well, they are probably not thread safe, so with multiple calls at the same time wellā¦ youāre gonna have a bad time.
