Best practice on calling external database?

Anvil Q&A
1

Currently, I call an external database with some variation of this code. It works, but it requires that I convert the list of tuples into a list of dictionaries.

import psycopg2
import psycopg2.extras
import json

def calldb():
	PGSQL_CONN = psycopg2.connect("dbname=name host=host user=user password=password")
	PGSQL_CURSOR = PGSQL_CONN.cursor()

	sql_query = """
				SELECT 
				  dateadded
				  , firstname
				  , lastname
				FROM table"""
	PGSQL_CURSOR.execute(sql_query)
	result = PGSQL_CURSOR.fetchall()

	result_list = []
	for row in result:
		result_list.append({"dateadded":dateadded
			, "firstname":firstname
			, "lastname":lastname})
	return result_list

Outside of anvil/skulpt if I were to change this row:

PGSQL_CURSOR = PGSQL_CONN.cursor(cursor_factory=psycopg2.extras.DictCursor)

Then I would get an object. I don’t fully understand it because it appears to be a list of lists. However I can treat it (call it) as a list of dictionaries - which would be what anvil needs. Except when I pass this to the front end from the server, I get an error.

Lastly, I can use pandas dataframes. Though I imagine that the same frontend/backend voodoo will cause issues for me.

What is the best practice (if any)?

2

The issue here is one of serialisation - whatever object you retrieve from your db needs to be serialised before it can be passed to the front end.

I’ve used Marshmallow (https://marshmallow.readthedocs.io/en/latest/) for that task in the past. It works particularly well when paired with sqlalchemy for the database access: https://marshmallow-sqlalchemy.readthedocs.io/en/latest/

In my case, I used this technique in code that runs on a local server to provide data via uplink - but there’s no reason it shouldn’t work within an Anvil server module if marshmallow-sqlalchemy were installed.

3

Except when I pass this to the front end from the server, I get an error.

What happens if, instead of returning foo, you return list(foo)? That should turn it into a list of dicts, which Anvil can pass very happily from server to client.

4

Another thought - I’ve not tried this, but have you tried psycopg’s RealDictCursor rather than just DictCursor?

RealDictCursor uses an actual Python dict and so should be serialisable by default. DictCursor just provides a dictionary-like interface.

1 Like
5

You’re right! I completely forgot about RealDictCursor, but now it returns a list of dictionaries which transfer over to the front end without an issue. Thanks

1 Like
6

Thanks. That’s useful for me to know. Though Owen reminded me of the RealDictCursor extra in psycopg2 which returns a list of dictionaries natively. For my purposes that is pretty much the best case scenario.

1 Like
7

Glad it worked! There’s a piece of knowledge I shall tuck away for future reference…