GUID SQLite Error - Server Side Processing in Flask

rene7vick

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)

allan

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