GUID SQLite Error - Server Side Processing in Flask

GUID SQLite Error - Server Side Processing in Flask

rene7vickrene7vick Posts: 1Questions: 1Answers: 0
edited March 17 in Free community support

Hi guys,

I am working on a Flask App (using SQLAlchemy) and implemented a Python script for server side processing. Now I encountered some problems using an UUID as ID. When I store it as String (VARCHAR) inside my table, searching works fine. But when I store it as GUID (so a CHAR) my server side python script doesnt recognize the UUID. Did anyone encouter the same problem? Ordering (asc. & desc) works fine, but when I search for the guid, it doesnt show any entries...

I Tried almost everything and been sitting for a few days. Any help would be appreciated

Server side processing code:

class ProjectsDataTable:
    def __init__(self, request, model_object):
        self.request = request
        self.model_object = model_object
        self.cardinality = 0
        self.cardinality_filtered = 0
        self.results = None
        self.run_query()

    def output_result(self):
        output = {}
        output["sEcho"] = int(self.request.args.get('sEcho'))
        output["iTotalRecords"] = self.cardinality
        output["iTotalDisplayRecords"] = self.cardinality_filtered
        output["aaData"] = self.results
        return output

    def run_query(self):
        self.cardinality = db.session.query(func.count(self.model_object.id)).first()
        #get columns name from request
        column_count = int(self.request.args.get('iColumns'))
        column_list = []
        for i in range(column_count):
            column_name = self.request.args.get('mDataProp_%d' % i)
            if column_name:
                column_list.append(column_name)

        #filtering
        search_value = self.request.args.get('sSearch')
        filter_list = []
        if search_value != "":
            for col in column_list:
                column_type = getattr(getattr(self.model_object, col), 'type')
                if not isinstance(column_type, db.DateTime):
                    filter_list.append(getattr(self.model_object, col).like("%" + search_value + "%"))

        #sorting
        order_column_index = int(self.request.args.get('iSortCol_0'))
        order_column = getattr(self.model_object, column_list[order_column_index])
        order_dir = self.request.args.get('sSortDir_0')
        order_object = getattr(order_column, order_dir)()

        #paging
        start = self.request.args.get('iDisplayStart', 0, type=int)
        length = self.request.args.get('iDisplayLength', 1, type=int)

        items = self.model_object.query.filter(or_(*filter_list)).order_by(order_object) \
                    .offset(start).limit(length).all()
        self.cardinality_filtered = db.session.query(func.count(self.model_object.id)) \
                    .filter(or_(*filter_list)).order_by(None).first()
        self.results = [i.projects_table_to_json for i in items]

Inside my JS file I have initliazied the table as follows:

  $('#table_projects').DataTable({
      language : {
          url: getCurrentSelectedLanguage()
        },
      "lengthChange": true,
      "bServerSide": true,
      "sPaginationType": "full_numbers",
      "iDisplayLength": 10,
      "stateSave": true,
      "sAjaxSource": "/load-projects",
      columns: [{data: 'id'},
          {data: 'title'},
          {data: 'description'},
          {data: 'created_date'},
.....

My Sqlite Table model looks as following:

class Projects(db.Model):
    __tablename__ = 'projects'
    id = db.Column(GUID, default=GUID.gen_value, primary_key=True)
    title = Column(String(100), unique=False, nullable=False)

    def __init__(self, title, description, created_date):
        self.title = title
    
    @property
    def projects_table_to_json(self):
        return {
            'id': self.id,
            'title': self.title,
        }

and the GUID Class is the following:

class GUID(TypeDecorator):
    """GUID column."""

    impl = CHAR

    def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(UUID())
        else:
            return dialect.type_descriptor(CHAR(32))

    def process_bind_param(self, value, dialect):
        if not isinstance(value, uuid.UUID):
            try:
                return '%.32x' % int(uuid.UUID(value))
            except ValueError:
                return None
        else:
            return '%.32x' % int(value)

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        else:
            return uuid.UUID(value)

    @staticmethod
    def gen_value():
        return uuid.uuid4()

and the route for it:

@queries.route('/projects')
def projects():
    return render_template('interface/projects.html')

@queries.route('/load-projects')
def load_projects():
    if request.method == 'GET':
        returnTable = ProjectsDataTable(request, Projects).output_result()
        return jsonify(returnTable)

Answers

  • allanallan Posts: 54,909Questions: 1Answers: 8,606 Site admin

    I suspect you'd be better asking Python programming questions on StackOverflow. I'm afriad I've not really used Python much, nor has the rest of the team here.

    Allan

Sign In or Register to comment.