Brand new to Anvil - looks pretty great so far. However for my first application I’ll need to be able to connect to an Azure hosted MSSQL Server database. Has anyone done this before?
Thanks!
Hi there - welcome to Anvil!
I haven’t connected specifically to Azure before, but you should be able to just use the standard pymssql library, which is already available in our full-Python server modules. Do give it a try!
How is Anvil dealing with external databases, in comparisson to Anvil internal data service? It is quite elegant to work with internal data service when dealing with ids, one to many relationships (you deal with rows and not with technicalities). How is that if you choose pymssql or SQLAlchemy?
I just today tried setting up a MySQL server database in Ubuntu on Linode and connecting it to Anvil. It worked. Here were the steps I followed:
- I set up a MySQL database & serer on a Linode virtual machine.
- In Anvil, I tested the URL from a server module and found it to be 35.177.218.83
- Back in the MySQL database- I logged in as root and executed
create user ‘anvil_arbitrary_name’’@‘35.177.218.83’ identified by ‘password’;
grant all on database_name.* to ‘anvil_arbitrary_name’’@‘35.177.218.83’;
commit; - In anvil, the form calls a anvil server function.
- In the server module, I entered the following:
import pymysql
host = ‘ip for my virtual machine’
user = ‘anvil_arbitrary_name’
passwd = ‘password’
db = ‘database_name’
conn = pymysql.connect(host=host, user=user, passwd=passwd, db=db)
cur = conn.cursor()
def mysql_func(arg):
return * some function of cur.execute() and cur.fetchall()
1 Like
Many thanks! I suppose you can then bind the controls directly to the db objects and update db when necessary. I’ll try it out.
Yes. You might do something like this in the Form:
def text_box_1_pressed_enter (self, **event_args):
# This method is called when the user presses Enter in this text box
query_string = self.text_box_1.text
rows = anvil.server.call("mysql_func",query_string)
self.label_1.text = # rows, str(rows), or some function of the rows
Let me know if you get stuck. It took me a while to get the remote connection to mysql successfully.
1 Like
Just to let you know that it most probably works as described, I tested all the necessary elements except for the connection to MS SQL server, since I’m still on ‘free trial of our individual plan’ and it doesn’t allow me to use pymssql package. I wonder if ‘individual plan’ allows these connections (since I’m on free trial of that plan, so I would expect that you get the same experience), but this is another matter.
I’m on that plan and it lets me.
Thanks, good to know!
@navigate, how do you return rows from the server function? With:
rez = list(cursor)
conn.close()
print(type(rez))
return rez
I get:
The connection to the remote database runs very nice and fast.
Worth a read of the following thread.
Chances are your return values may contain invalid types.
Best thing to do is print the DB result before the return so we can see that’s causing the issue.
Basically, you’ll probably see things like Decimal('xxx') in there. You would need to convert those to float for example. There may also be others, but that one was the killer for me in that thread.
If you can post your output showing the result I can help identify the issue.
Here is one record:
(UUID('e718d84a-b286-4d73-9c92-a633010b89b4'), UUID('8f6f178c-50b9-4d5a-9311-a2f301703f88'), 'miškina tipka', UUID('d661b6d7-6884-47a2-9f8b-a4070126395b'), UUID('da85402e-fd8f-4ba8-b5c2-a432017347e7'), False)
Could be UUIDs, or maybe unicode characters?
Edit: it’s UUIDs, I narrowed it down.
Many thanks for pointing me in the right direction!
1 Like
Yeah, that would do it.
Converting them to strings before returning should fix that.
1 Like
I was going to reply but see that David beat me to it!
The other thing I always forget is that you have to convert sets to lists to serialize.
Yeah, there’s always something or other that I forget, too 
Thanks anway! I’ll try it out also with SQLAlchemy.
1 Like