#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    13
    Rep Power
    0

    Making links from a database generated table


    I have generated a table of data using sqlalchemy.

    Now, I was wondering if it's possible to get user input from whatever the user clicks on (e.g a link) and storing it as a string value?

    For example, I have a link "hello" and when the user clicks on that link, the string "hello" will be stored in a variable to be used for filtering later.

    For example:
    |School_Name|Location
    ==========================
    |Jackson |San Francisco
    |Sample |San Jose
    |Sample B |New York

    Let's say I want to change the location values into links. Once the user clicks on those links, it'll send them to a filtered page showing only items with the selected value.

    I was going to include the variable into a html template file. Which is why I was wondering if there was a way to store the string of whatever value was clicked on.

    The url would be in an html file while the actual function would go in the view python2.7 file.
    Code:
    <a href="{{ url_for("school.index") }}?school_names{{ names.school_names }}">{{ name_list }}</a>
    Something like the above code.

    I can't seem to find reference on reading mouse clicks, so I'm guessing I'm thinking about this in the wrong way?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    13
    Rep Power
    0
    bump

    Can anyone help me?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    This isn't really a Python question, but...

    The web server won't know the text within the <a href> tag unless you pass it explicitly. You need to pass whatever is between <a> and </a> for the server to know what is there. In the case of your table above, however, if the column "school_name" is UNIQUE, then the name is the key and you can just reference it in the background -- or even if its not the key, however you are linking to the school row can be used to pull the text that was clicked if the text in the <a> tag is coming from the database (and in your example it is).

    But if you want to pass arbitrary text like "hello" then you need to explicitly pass "hello" as either GET or POST data and pick it up from the server request like any other request data.
    html Code:
    <a href="{{ url_for("some.view") }}?id={{ context.variable }}&text=hello">hello</a>

    If this is a recurring need for you then I would recommend writing your own template tag that can generate this for you so you can just do something like {{ context_url("some.view", "context_dict") }} instead of writing all that html poo by hand.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    13
    Rep Power
    0
    Is it possible for you to provide a different explanation? I don't quite understand the one you gave.. I'm rather new to python and still starting out with programming.

    The reason I posted this in the python section is I used python and sqlalchemy to generate the table data.

    I was thinking of doing it this way:

    Code:
    def function():
       name_list = sqlalchemy query to generate the table (this I already have_
       filter_field = user input mouse click
       filtered_string = taking whatever was clicked and setting it as a string value
       return render_template('filter_page.html', filter_field=filter_field, filtered_string=filtered_string)
    Then in the html file I can reference filtered_string
    Code:
    <a href="{{ url_for("school.filter_page") }}?school_names{{ filtered_string.school_names }}">{{ name_list }}</a>
    Is this the right way to do this? I haven't been able to find any python examples of user input through mouse clicks..

    Again, I want to turn the values of a selected column into links, let the user click on those links as a filter
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    You're aiming in the right direction, but unless you are only putting a single link on a page at a time this won't work.

    Can you paste your data model/class definitions so I can see what you're dealing with?

    Also, what is the problem you are trying to solve? Why is it important that you know what the text a user clicked was? Often when you're new to a system or especially new to programming in general you wind up having "X Y problems" -- a common enough situation that we have a phrase for it. (You want to do X, and think Y is a way to accomplish that, so instead of asking about X you ask about Y.)
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    13
    Rep Power
    0
    Table that was generated from my sqlalchemy query
    Code:
    location           |  total_schools
    ----------------------------------
    california         |  13
    new york           |  100
    washington state   |  25
    My python class definition:

    And in the accompanied html file, something like:

    What I'm trying to do now:
    1. change each value in the location column into links (which I've done by modifying the code in the html file - previous post)
    2. allow users to click on those links, which sends them to a new page that filters/sort and shows only the schools within the selected location in another table

    The reason I was trying to know what the text a user clicked on was, was because I thought "how would it know what to sort by?"
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    Do you actually have a list of the schools within each location?

    If you do, and its a table, then the easiest way to know what to sort/filter by is by using the relational nature of your data. So consider a schema:
    sql Code:
    CREATE TABLE location
       (id    SERIAL PRIMARY KEY,
        name  VARCHAR(50) NOT NULL);
     
    CREATE TABLE school
       (id        SERIAL PRIMARY KEY,
        location  INTEGER REFERENCES location ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
        name      VARCHAR(50) NOT NULL);
     
    CREATE VIEW school_counts AS
      WITH counts AS
         (SELECT location, COUNT(location) AS total_schools
              FROM school
              GROUP BY location)
      SELECT l.id, l.name, c.total_schools
          FROM location AS l JOIN counts AS c ON (l.id = c.location);

    Here we added the primary key of location to the result -- which is the part you're missing. We will filter, sort, reference by and use this as a definitive URL element because we know that school_counts.id is always an accurate reference to a location.

    I'm assuming you'll use the ORM to build the db relations you want, but you should understand what is happening in the database above any other thing you are doing right now (otherwise you will develop a mental barrier to creation of non-trivial schemas).
    python Code:
    def school_counts(template='school_counts.html'):
        context = gen_context()
        context['school_counts'] = session.query(SchoolCounts).all()
        return render(template, context)

    The gen_context() function isn't required, but you will find that there are certain things you want to include in every page view (the user's name, current messages, tasks, location name, breadcrumbs, or whatever) and having a standard place to put that code is useful -- it also means you only have to add to that dictionary to access things in your template (and there are slicker ways than always including a function call at the beginning, but we'll ignore that for now). I also made the template name an argument with a default -- you may find this simplifies making your page view functions more flexible in various ways later on.
    html Code:
    <table>
      <tr>
        <th>Location Name</th>
        <th>Number of Schools</th>
      </tr>
      {% for l in context.school_counts %}
        <tr>
          <td><a href="{% url school_list location_id=l.id %}">{{ l.name }}</a></td>
          <td>{{ l.total_schools }}</td>
        </tr>
      {% endfor %}
    </table>

    The text the user sees within the <a> tag link is immaterial -- there could be several locations with the same name. The thing you need to pass to the next function is the primary key of the location's entry in the database. The url above points to school_list, which would be defined something like:
    python Code:
    def school_list(location_id, template='school_list.html'):
        context = gen_context()
        context['school_list'] = session.query(School)\
                                       .filter(School.location == location_id)
        return render(template, context)

    Not everything I wrote above is entirely faithful to SQLAlchemy -- I've been using Django and Snap a lot more lately -- but this should give you some idea how to proceed. You shouldn't worry what text the user is clicking, you should be passing a definitive key to the next view function the user will be accessing.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    13
    Rep Power
    0
    Thank you that does help a lot.

    But what if the id does not reference the location but rather the school names? Would the logic doing it the way that you showed still be the same?
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    tldr:
    The logic would be the same, but you would have to declare that the natural names themselves are UNIQUE within the database and use them as keys. There are many complications to using natural names as keys, however, and so I recommend against this.

    Longer explanation:
    The whole magic behind using a serial id and not a name is that the id is guaranteed to be a unique key within the database, while it is very common for two locations to have the same name. You could make the names UNIQUE, and in doing so the names themselves would be keys and then referencing names directly would work.

    But doing so makes the assumption that the code you write today will never, ever be used across city, county or state lines. So in the case of arbitrary names I use an intrinsically meaningless id as the primary key to differentiate between "Arlington" and "Arlington", for example. (One being in Texas and the other in Virginia -- which is something you can't know until I tell you, because there are more than two Arlingtons out there... and the database doesn't know which one I'm talking about, either.)

    Another advantage of this approach is cleaning up references. Not every part of every system can handle spaces, for example. In the case of URLs you have to make each space become a %20 or break it with a + or some other delimiter, and then translate it back before hitting the database with the string. That's silly. But since there are spaces in many real names (and other characters that are annoying to escape, like apostrophes) you suddenly have to deal with this new problem because you decided to use natural names as primary keys.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    13
    Rep Power
    0
    I just noticed you made two tables (location and school). The database I was given to work with consists of one table

    id | school_name | location
    ----------------------------------
    123 | School A | california
    111 | School B | new york
    895 | School C | washington state
    899 | School D | washington state

    So I can't do a join and reference l.id = c.location. Also, the location_id that you used, that was just a name that you gave it right?

    edit: nevermind I got it to work. Thanks again
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    Whenever you are handed a stupid database schema refactor it for your own purposes. If that is forbidden for some (stupid, usually administrative) reason, create views that at least simulate a better schema, otherwise you will wind up writing convoluted queries even to get simple answers/behaviors whether you're doing it in SQLAlchemy or SQL directly.

    Sometimes a dumb schema is used by existing programs somewhere else and this is why you can't change it. When this is the case you can refactor the database to a better schema and then write views that match exactly the old schema, thereby gaining the advantages of a decent schema without breaking backward compatibility.

    Most schemas written by people who don't understand them look like spreadsheets. The single-table layout you show looks the way an answer to a question should look (the result of a query), not the way the raw facts should be stored. Incidentally this is how lists of things kept in spreadsheets tend to look as well, which I think is how the mistake arises in the first place.

    I'm harping on this at length here because the problems a bad data schema introduce into even essentially trivial projects grows exponentially with each new requirement that gets tacked on over time. (Well formed schemas, on the other hand, magically seem to flex to meet new requirements.)

IMN logo majestic logo threadwatch logo seochat tools logo