I’m just looking on the Anvil tables query examples, and just wanted to clarify (now I’ve imported all my sql tables to anvil tables) how the functions might differ?
So my SQL Query is this
@anvil.server.callable
def daysQuery (currentdays):
conn = connect()
with conn.cursor() as cur:
cur.execute("SELECT DaysID, Days, DaysPercent FROM days ")
There’s no real joined query in Anvil data tables, but the way the linking fields work you generally don’t need them.
Your SQL is doing an INNER JOIN, but you’re not selecting any fields from the joined table, so I’m not sure why the join is there. So rather than try to convert that directly, I’ll make some assumptions and work from there:
You have a location table and a locationdetail table
The locationdetail table has a linking field named location that links to a single row in the location table
You want to pull all the location detail rows for a single location
On the client you have location rows that the user is choosing from for the query and are passing a full location row as the currentlocation parameter
Thanks yes you are right, there is a where clause there but I was just trying to figure out how the join looks.
so on the client side how would this look as amended
for the location with the join I have:- self.repeating_panel_location.items = anvil.server.call('get_all_location_matches', self.text_box_location.text)
for the other tables where I dont have a join in them I have:-
The user will input - say spain as the country they are visiting, then the locationdetail keyfield of location will find the value in location of Europe the actual premium rating is done on Europe, so this is the row I need to capture in order to get the correct rating.
Just to confirm, there are only 3 fields on the location table
Europe Worldwide (Excluding USA and Australia) Worldwide
one of these 3 will show on the linked field in the locationdetail table. We dont know until the user chooses the country, which of those 3 it would be. So that will only be known from the client side.
I got it done in MysQL with this full query, just to add I split the query into the below on the serverside
@anvil.server.callable
def locationQuery (currentcountry):
conn = connect()
with conn.cursor() as cur:
cur.execute("SELECT location.LocationID, location.Location,location.LocationPercent FROM location INNER JOIN locationdetail ON (locationdetail.LocationID = location.LocationID)WHERE locationdetail.LocationDetailCountry = '{currentcountry}' ORDER BY locationdetail.LocationDetailCountry LIMIT 1".format(currentcountry=currentcountry))
return cur.fetchall()
The original function was on the quotation client code on a submit button click event
That would give you all locationdetail rows that matched the currentcountry. If there will only be one, then you’d use get instead of search.
Once you have a locationdetail row, you access the location field in it to get to the location linking row (including all the fields in that location row).
I did a print output to show what each of the arguments pull out
the self.ageall prints (30, 40, 50, 60)
and the self.location prints spain
the repeating panel for location, prints nothing at all but Im not getting errors on it. on get_all_ages I am getting anvil.tables.TableError: Column 'Age' can only be searched with a number (not a string) in table 'age'
Both of my original SQL queries worked but the server side on ages was an SQL IN query
That’s the easy one. Your Age field is a number, you can’t search it using a string. Pass a number to your server function instead of a string. If ages is already a number, don’t format it:
anvil.server.call('get_all_ages', ages)
If age isn’t a number already, convert it to a number before passing it to your server function.
Without seeing more context, it’s hard to say about the other. Can you share a clone link?
yes here is the clone link the pages with code on are Quotation on the client side, and Queries on the server side. Theres not much else on it though other than blank forms.
You’ve got references to a global variable ages in there that should (probably) be self.ages. I made that change in my copy so it would get to the server calls
self.ages (and ages before it) is a list. You can’t convert a list to an int. Your server function is clearly expecting a single int, not a list of ints.
It looks like what you’re trying to do is to get the information for all the ages they input, and display that in the repeating_panel_2.
The most straightforward way of doing that is on the server, with something along these lines (pass self.ages to the server as a list, don’t convert it to anything):
@anvil.server.callable
def get_all_ages(ages):
results = []
for age in ages:
row = app_tables.age.get(Age=age)
if row:
results.append(row)
return results
There’s probably a shorter way of doing that using queries (Querying Data Tables), but I’m not a query expert.
I’m still not getting it working, so I’m going to walk away from it for the rest of the evening and pick it up tomorrow, its probably beacause I’m tired.
Thank you so much for all your help, its way over what I can expect, I do appreciate it.
So I have tried everything suggested but nothing is working so far. It seems any_of should produce the same as an SQL IN Statement
The code below will produce a result if only 1 argument is given, if more than one I get TypeError: float() argument must be a string or a number, not 'list'
def get_ages_again(ages):
result = app_tables.age.search()
if ages:
result = (
x for x in result
if ages in x('Age')
)
return result
and got this error anvil.server.SerializationError: Cannot serialize return value from function. Cannot serialize <class 'generator'> object at msg['response']
I left the client side the same as when I was using the SQL If statement, because that worked before and its only where I am storing the tables that has changed.
I also still have not been able to get the table join location one to work in the tables in Anvil format.
I have looked on the Anvil Docs and also the Tutorial Storing Data in Tables, I have cloned the App Query Example and there are no examples in there of dealing with a result that is a list or how linked tables work as you would a Join. If there is a resource out there I can use I would really appreciate a link to it, since I will likely need to make use of this on other queries.
In your locationdetail search, remember that strings search case sensitive by default. To get a case insensitive search, you need to use the query operators:
Hi thanks for that on the age query, I missed the * !!!OMG I cannot believe it was that simple, I almost had it.
I also figured out my issue with the locationdetail and it wasn’t the query, my import only did up to 9900 lines, I’m not sure if its the import or the fact we are on a free “education” plan thats limited it.
There is only one more issue with that query, I need to limit the result to 1, obviously in SQL I can put the limit on the Select but what about in this query.
I always question that second method, though, it seems to me that the query itself is not being used right if you’re getting multiple rows but only care about the first. In SQL, there aren’t that many times I’d use a LIMIT 1, especially without an ORDER BY clause.
In this case I would use the results method, because the table I am using splits countries down into cities. We arent using cities at this stage, so we only need to know the country for rating purposes, e.g spain has a lot of cities, but all of spain is in Europe.
I’m conitnuing the import now for the locations as I expect all it was, was that it timed out.
Your days table looks like the Days field in it is unique. So the basic query, assuming that you’re using a number format text box and passing the value from it into the currentdays parameter in a server function, would be:
row = app_tables.days.get(Days=currentdays)
You’d return that row back to the client, and then use row[‘DaysPercent’] to get the percentage out of it.
There’s no need for a search and a for loop, since the query will never return more than one row.
That makes sense, the 30 second server call time limit probably hit. It might make sense to have each server call do a different subset of the import (e.g. all the countries starting with A, then with B, etc).
Your days table has two fields in it: Days, and DaysPercent.
A query on a table may only include keyword arguments for fields that are in it. daystoadd is not a field in the table, so you may not use it as a keyword argument in the search.
Your original SQL query from the image was always getting one row out, using a WHERE Days=currentdays
The matching Anvil query is app_tables.days.get(Days=currentdays)
I’m not sure what task you’re trying to do with new_days_query. If it’s just to return a row from the days table, you don’t need anything but the currentdays parameter. If you’re trying to do something else, then a simple query isn’t going to work.
