This is my first post over here… I’m a Data Scientist with zero experience with web stuffs. So, I found Anvil a very nice tool.
In my first real project with Anvil I will try to publish a client table to the web. I do have a MySQL server in my company. So, the data will come from there.
I’m stuck because I was not able to bind my db into Anvil, as well as, I really didn’t find an example showing how can I move the cursor of my table along the records.
My experience with Anvil is that, even if it’s something the developers didn’t anticipate (or haven’t gotten to yet!), there’s almost always a way to get the job done. Perhaps with slightly different aesthetics, or some additional behind-the-scenes code.
To stimulate group discussion and involvement:
Which (visual) elements of your example web page are essential to what you’re trying to accomplish?
Is it essential that your table reside only on your own computer(s)?
Hi,
if you are using MySQL you will need to import a library (such as PyMySQL - there are others but I use that one). It may not be available by default but on a pro account the Anvil guys will load it for you (they once described it as an email pip service).
In a server module you would then do something like this :
import pymysql.cursors
...
connection=pymysql.connect(
host="ip.of.server",
user="username",
password="password",
port=3306, # 3306 is standard & default so you can omit this.
db="my_database",
cursorclass=pymysql.cursors.DictCursor
)
with connection.cursor() as cursor:
connection.execute("SELECT * FROM mytable")
result = connection.fetchall()
print(result) # python 3 print syntax
This is just example code with no error checking, etc., but hopefully it will help you connected to a MySQL server.
Moving up and down the records is not quite the same as EWB (I used to use that as well). You would need to either manually step through the resulting data array “result”, updating your controls as required, or check out the recently added form data binding feature - I reckon that might work (though I’ve not tried it myself).
Welcome to the forums! Can you tell us a bit more about what you’re building?
This sort of app is something Anvil is good at, even though we don’t have an example just like this in our documentation (yet!). If you’re using an existing MySQL database with Anvil, your app will have three parts:
A server module that connects to your database and retrieves database rows. You can connect and retrieve data from your database using the standard Python libraries - nothing Anvil-specific here! (This is what @david.wylie is talking about. He’s spot on - we do include support for the PyMySQL library for paid users in our “Full Python 2.7” and “Full Python 3.6” runtimes.) The server function will return your data to your client code as Python lists and dictionaries.
.
For more information on this, you might want to look at our Dashboard example, where I extract data from a Postgres table.
Client code that calls your server function to fetch your data, and then displays it. If you want to graph your data, use our Plot component. If you want to display text boxes/check boxes/labels/etc, I would suggest using data bindings for this (check out our tutorial). The data bindings tutorial uses Anvil Data Tables, but data bindings work just as well with ordinary dicts and lists! The only difference is that ordinary dicts and lists don’t automatically store data in the database when you change it, so you’ll need…
(If you’re allowing the user to make changes:) A “save” button that calls a server function with updated values to write back into the database.
@p.colbert, I think the navigator bar working both ways (forward and back) is better than just once. But, of course, it is not necessarily essential.
@david.wylie / @meredydd, the explanation about how to connect Anvil to my outside DB (and vice-versa) sounds interesting. I see a light in the end of tunnel… finally.
However, as a begginer, sounds a bit complex to implement the navigation bar… I mean, I would need to either manually step through the resulting data array. Anyway, I will try to do some tests. This can be a hard job to do for newbies like me. Just my 2 cents.
@Alfred - what size do you think your result sets might be? If you know they are going to be small-ish you could fetch them all into a list variable. Then your next/etc buttons could retrieve the data based on an index stored in another variable.
Another question - are you comfortable in Python generally. I might have to do something similar myself shortly. I’ll happily share if I do but need to know what level to pitch it to you at.
@david.wylie thank you. I also might have to do something similar myself. Please, let us know your progress. I’m sure the community will be happy to contribute with this.
But, of course, it is not necessarily essential.