Pagination of postgres query using cursor in python flask

Question

I just want to create pagination in flask using cursor, but i really don't know how to do it since the other solutions that i found were very complicated and it's hard for me to implement it. can anyone here help me with this? here's my simple code

@web_initials.route('/webpage/gmsi/list_of_users', defaults={'page': 1})
@web_initials.route('/webpage/gmsi/list_of_users/<page>')
@login_required
def list_of_users(page):

    conn2 = psycopg2.connect(database='mydb', user='myuser', host='myhost.host', password='mypassword')

    cur2 = conn2.cursor()
    cur2.execute('SELECT count(*) FROM tbl_users')
    x = [dict(((cur2.description[i][0]), value)
                    for i, value in enumerate(row)) for row in cur2.fetchall()]
    data2 = x[0]['count']
    conn = psycopg2.connect(database='mydb', user='myuser', host='myhost.host', password='mypassword')

    cur = conn.cursor()
    cur.execute('SELECT tbl_users.tbluserid, CONCAT(firstname,\' \', middle_initial, \' \', lastname) AS \"FULL NAME\", tbl_single_role.userrole, image_path, tbl_single_role.tblsingleroleid FROM tbl_users INNER JOIN tbl_single_role ON tbl_users.tblsingleroleid = tbl_single_role.tblsingleroleid  ORDER BY lastname ASC LIMIT {limit} offset {offset}'.format(limit = 5, offset = 0))
    data = cur.fetchall()
    page = request.args.get(get_page_parameter(), type=int, default=1)
    pagination = Pagination(page, total=data2, css_framework='bootstrap4', record_name='users')

    return render_template('tables.html', data = data, pagination=pagination)

here is my html

{{ pagination.info }}
    {{ pagination.links }}
        <div class="table-responsive">

            <table class="table">
                <thead class=" text-primary">
                    <th>
                        Full Name
                    </th>
                        <th>
                          Photo
                        </th>

                    </thead>
                    <tbody>
                        {% for item in data %}
                    <tr>
                        <td>{{item[1]}}</td>
                                {% if item[3] == None %}
                                <td> <img class="img-fluid img-thumbnail" src="{{url_for('static', filename='assets/img/img.jpg')}}" id="imgfilechosen" height="60" width="60"/></td>
                            {% else %}
                        <td> <img class="img-fluid img-thumbnail" src="/{{item[3]}}" id="imgfilechosen" height="60" width="60"/></td>
                    {% endif %}
                </tr>
            {% endfor %}
        </tbody>
     </table>
{{ pagination.links }}

I already solved it by myself.

itTech
– itTech

2018-12-29 15:57:30 +00:00
Commented Dec 29, 2018 at 15:57 — itTech
– itTech, Commented Dec 29, 2018 at 15:57

itTech · Accepted Answer · 2019-04-18 12:24:12Z

Here's how i solved my own problem sorry for the delay:

class:

@web_initials.route('/webpage/gmsi/list_of_users', defaults={'page': 1})
@web_initials.route('/webpage/gmsi/list_of_users/<page>')
@login_required
def list_of_users(page):

    conn = psycopg2.connect(database='yourdatabase', user='youruser', host='yourhost', password='yourpassword')

    page = request.args.get(get_page_parameter(), type=int, default=int(page))
    if(page == 1):
        cur = conn.cursor()
        cur.execute('SELECT tbl_users.tbluserid, CONCAT(firstname,\' \', middle_initial, \' \', lastname) AS \"FULL NAME\", tbl_single_role.userrole, image_path, tbl_single_role.tblsingleroleid FROM tbl_users INNER JOIN tbl_single_role ON tbl_users.tblsingleroleid = tbl_single_role.tblsingleroleid  WHERE tbl_users.tblsingleroleid < 4 ORDER BY tbluserid ASC LIMIT {limit} offset {offset}'.format(limit = 10, offset = 0))
        data = cur.fetchall()
    else:
        cur = conn.cursor()
        cur.execute('SELECT tbl_users.tbluserid, CONCAT(firstname,\' \', middle_initial, \' \', lastname) AS \"FULL NAME\", tbl_single_role.userrole, image_path, tbl_single_role.tblsingleroleid FROM tbl_users INNER JOIN tbl_single_role ON tbl_users.tblsingleroleid = tbl_single_role.tblsingleroleid  WHERE tbl_users.tblsingleroleid < 4 ORDER BY tbluserid ASC LIMIT {limit} offset {offset}'.format(limit = 10, offset = (5 * int(page))))
        data = cur.fetchall()


    pagination = Pagination(page=page, total=data2, css_framework='bootstrap4', record_name='users')

    return render_template('tables.html', data = data, pagination=pagination)

html side:

  <div class="content">
    <div class="container-fluid">
      <div class="row">
        <div class="col-md-12">
          <div class="card">
            <div class="card-header card-header-success text-white bg-dark">
              <h4 class="card-title ">List of Users</h4>
              <p class="card-category"></p>
            </div>
            <div class="card-body">
            {{ pagination.info }}
              {{ pagination.links }}
              <div class="table-responsive">

                <table class="table">
                  <thead class="bg-primary">
                    <th>
                      Full Name
                    </th>
                    <th>
                    <center>
                      Photo
                      </center>
                    </th>
                    <th>
                    <center>
                      Last Location
                      </center>
                    </th>
                     <th>
                     <center>

                      </center>
                    </th>
                  </thead>
                  <tbody>
                       {% for item in data %}
                        <tr>
                            <td>{{item[1]}}</td>
                            {% if item[3] == None %}
                            <td><center> <img class="img-fluid img-thumbnail" src="{{url_for('static', filename='assets/img/img.jpg')}}" id="imgfilechosen" height="60" width="60"/></center></td>
                              {% else %}
                              <td> <center><img class="img-fluid img-thumbnail" src="/{{item[3]}}" id="imgfilechosen" height="60" width="60"/></center></td>
                            {% endif %}
                            <td><center><a href="{{ url_for('web_initials.merchandiser_location',  id = item[0]) }}" target="_blank">View</a></center></td>

                            <td><center><button type="button" class="btn btn-primary" data-toggle="modal" data-target="#exampleModal">EDIT</button></center></td>
                          </tr>
                        {% endfor %}
                  </tbody>
                </table>
                {{ pagination.links }}
              </div>
            </div>
          </div>
        </div>

itTech · Accepted Answer · 2018-12-29 16:02:20Z

-1

I'd just got solved it, i figured it out how to make it with just those lines, no need to implements those long methods in github.

answered Dec 29, 2018 at 16:02

itTech

3462 gold badges7 silver badges23 bronze badges

5 Comments

Noah Martin Over a year ago

I am happy for you but it's good to share your solution so that others that might have your problem can be helped.

itTech Over a year ago

I did not posted my working code here because, thousands where able to make the long methods. Im just a very noob, newbie in python language that's why it's very hard for me to implement it and I think it was just a waste of time and most of my questions here were being answered by myself. I don't know why they can't or they just don't want to answer it. that's why i stopped posting a question here. Thanks for the vote down anyway.

Noah Martin Over a year ago

You asked for help and solved the issue within 4 hours by yourself. This is very good. Help might have taken longer than this. It's not granted that you will get a solution from someone immediately. No one is being paid to solve others problems here, everyone just tries to help. You should do the same and not complain for the lack of interest from the others or for my downvote. Downvote means that the answer doesn't provide any useful information to the question.

itTech Over a year ago

Ok ill post my solution. Thanks a lot for your good advise sir.

itTech Over a year ago

@NoahMartin good evening sir, i already posted my very simple fixed, sorry for being such a low-tempered person. Thanks a lot sir and may God bless you always. :)

Collectives™ on Stack Overflow

Pagination of postgres query using cursor in python flask

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related