Connection problems to mySQL

Anvil Q&A
1

I can successfully connect to a mySQL db (‘localhost’) from python (in jupyter notebook) but when I use the code in a server module I get this error:

OperationalError: (2003, “Can’t connect to MySQL server on ‘localhost’ ([Errno 111] Connection refused)”)

The relevant code is:
db_connection = pymysql.connect(
host=‘localhost’,
user=‘xxxxxx’,
password=‘xxxxxx’,
database=‘charts’,
port=3306
)

Services tell me that mySQL is running fine and as I say it works just fine in normal code environment. Any ideas?
Thanks, Dave

2

Where is this code running? If it’s at anvil, ‘localhost’ isn’t going to work. It needs to point to wherever your MySql db is.

3

It is not going to work because the Anvil server module is running on a server somewhere in the UK and not on your local computer. Therefore, localhost is not going to work, because it will look on that server for the mySQL database.

4

Ok, thanks to both of you for replying. That makes perfect sense.

Originally I was using sqLite and uplinking code to anvil - which worked fine when the jupyter notebook was running but not otherwise. I thought that by moving to mySQL it might bypass that issue but it’s obviously not that easy! I’m now assuming I will have to host the mySQL db in the cloud somewhere and pay for the privilege. I’m not prepared to do that yet but that does seem like the next step. If I’m making bad assumptions here please let me know. Thanks for your help!

Dave

5

The way you are using mySQL is exactly the same way you were using SqLite, on your local machine (you would have to use uplink).

There are plenty of ways to connect to a mySQL server remotely, but you would have to learn those ways, and about security permissions for user accounts, opening/protecting ports, resolving a domain or using a static ip, etc.

The easiest and most secure way will still be to run the database, SqLite, mySQL, or any other flavor, on local host (even in a cloud somewhere) and use uplink.

Plenty of people “do it the hard way” and that is fine, they don’t usually start with the anvil solution so they learn all those things you need to know first.

Or just use anvil data tables if you can. It’s easier than you might think.