Trying to use uplink to MsSql

Anvil Q&A
1

I try to get data from Microsoft SQL to Anvil, using uplink.

I have tried many small test as proof of concept but I always get a message like this:

anvil.server.SerializationError: Cannot serialize return value from function. Cannot serialize <class ‘pyodbc.Row’> object at msg[‘response’]

What is the correct way to do this?

import pyodbc
import anvil.server

anvil.server.connect("server_3SEXNH5BWRGSVCYC437S5PQF-MLRZW5RC2CLD72DS")
conn = pyodbc.connect(
    "Driver={ODBC Driver 17 for SQL Server};Server=fm-sql05;Database=byod_staging;Trusted_Connection=yes;UID=mf_byod_reader;PWD=*****;"
    )

@anvil.server.callable
def urlmapper():
  cursor = conn.cursor()
  cursor.execute("select D365URL from urlmapper.Target")
  return cursor 

anvil.server.wait_forever()
2

The docs describe what you can return from a sever function:

2 Likes
3

I am afraid I still don’t get it. The documentation says the callable function can return “rows, tables, views or search iterators”, which I thought I did. Lists are also legal - so now I converted the query result to a list of tuples, using fetchall(), and I still get the serialization error:
‘’’
@anvil.server.callable
def urlmapper():
cursor = conn.cursor()
cursor.execute(“select TargetID,D365URL from urlmapper.Target”)
rv = cursor.fetchall()
return rv
‘’’

4

When it says ‘rows’, it’s talking about rows from the anvil data tables service, which you aren’t using. You can’t send back rows from some other db.

You’ll need to convert the content to some form of simple python objects before returning them. (Perhaps a list of dicts).

1 Like
5

Now I return a list. It still fails.
‘’’
@anvil.server.callable

def urlmapper():

cursor = conn.cursor()

cursor.execute(“select TargetID,D365URL from urlmapper.Target”)

rv = cursor.fetchall()

print(type(rv)) # checking that return value is a List

return rv

anvil.server.wait_forever()
‘’’

6

You need to use backticks, not quotes, to format your code on here.

Yes, you have a list, but each object within that list needs to be serialisable - which is why I suggested a list of dicts.

1 Like
7

OK Thanks. I will do that. (both)

8

I have not used pyodbc in a long time but I think this still works:

cursor = conn.cursor()

cursor.execute(“select TargetID,D365URL from urlmapper.Target”) #your code here

columns = [x[0] for x in cursor.description]

results = [ dict( zip(columns, row) ) for row in cursor.fetchall() ]
2 Likes
9

Thanks. I switched to pymssql and it works now. I do this mostly to learn, so I will try your method also.

10

Working with a MSSQL DB that was running on a .net machine meant in python I ended up using both libraries also. Each one has major defects for certain tasks that are much more of a pain to work around than switch between, iirc.