1
\$\begingroup\$

This class gets the products from a MySQL database. The code is not clean, so are there any tips on how to improve?

from django.db import connection, ProgrammingError

class Productos(MainClass):

    def get_products(self, data):
        param_select = data.get('select', 'p.*')
        param_category = data.get('category', '')
        param_q = data.get('q', '')
        param_order = data.get('order_by', '')
        param_limit_from = data.get('limit_from' '')
        param_limit = data.get('limit', '')
        param_show_count = data.get('show_count', False)
        param_show_filtros = data.get('show_filtros', False)
        param_custom_contiditon = data.get('custom_contiditon', '')    


        def build_sql(param_select, exclude_limit=False):
            # SELECT
            sql = "SELECT " + param_select + " FROM product p WHERE p.active = 1"


            # Categories
            if param_category != '' and param_category != ' ':
                category_list = param_category.split(',')
                sql += ' AND ('
                condicion = ''
                for c in category_list:
                    if c != '':
                        condicion = condicion + ' p.tags LIKE "%ca' + c + '%" OR '
                condicion = condicion[:-3]
                condicion += ") "
                sql = sql + condicion

            # QUERY TERM
            if param_q != '' and param_q != ' ':
                term_list = param_q.split(' ')
                sql += ' AND ('
                condicion = ''
                for t in term_list:
                    if t != '' and t != ' ':
                        if t.endswith('s'):
                            t = t[:-1]
                        condicion = condicion + 'p.title LIKE "%' + t + '%" OR '
                        condicion = condicion + 'p.summary LIKE "%' + t + '%" OR '
                        condicion = condicion + 'p.description LIKE "%' + t + '%" OR '
                condicion = condicion[:-3]
                condicion += ") "
                sql = sql + condicion

            # CUSTOM CONDITION
            if param_custom_contiditon != '':
                sql = sql + ' AND ' + param_custom_contiditon

            # ORDEN
            if param_order is not False:
                if param_order != '':
                    if param_order == '-price':
                        sql += ' ORDER BY price DESC '
                    elif param_order == 'price':
                        sql += ' ORDER BY price ASC '
                    elif param_order == '-rating':
                        sql += ' ORDER BY rating DESC '
                    elif param_order == 'rating':
                        sql += ' ORDER BY rating ASC '
                elif param_order == '' and param_q == '':
                    sql += " ORDER BY (CASE WHEN p.stock>0 THEN 1 ELSE 0 END) DESC, p.sales DESC"

                if param_q != '' and param_q != ' ' and param_order == '':
                    sql = sql + ' ORDER BY (CASE WHEN p.title LIKE "%' + param_q + '%" THEN 500 '
                    if len(term_list) == 3:
                        sql = sql + ' WHEN p.title LIKE "%' + term_list[0] + '%" AND p.title LIKE "%' + term_list[1] + '%" AND p.title LIKE "%' + term_list[2] + '%" THEN 300 '
                        sql = sql + ' WHEN p.title LIKE "%' + term_list[0] + '%" AND p.title LIKE "%' + term_list[1] + '%" THEN 200 '
                        sql = sql + ' WHEN p.title LIKE "%' + term_list[1] + '%" AND p.title LIKE "%' + term_list[2] + '%" THEN 200 '
                        sql = sql + ' WHEN p.title LIKE "%' + term_list[0] + '%" AND p.title LIKE "%' + term_list[2] + '%" THEN 200 '

                    if len(term_list) == 2:
                        sql = sql + ''' WHEN p.title LIKE "%''' + term_list[0] + '''%" AND p.title LIKE "%''' + term_list[1] + '''%" THEN 200 '''

                    for t in term_list:
                        if t != '':
                            sql = sql + ' WHEN p.title LIKE "%' + t + '%" THEN 100 '

                    sql = sql +'''
                    WHEN p.summary LIKE "%''' + param_q + '''%" THEN 50
                    WHEN p.description LIKE "%''' + param_q + '''%" THEN 30
                    WHEN p.ingredientes LIKE "%''' + param_q + '''%" THEN 25
                    ELSE 0 END) DESC, p.sales DESC '''

            # LIMIT
            if exclude_limit is not True:
                if param_limit_from != '' and param_limit != '':
                    sql = sql + ' LIMIT ' + str(param_limit_from) + ', ' + param_limit

            return sql


        def execute_sql(sql):
            cursor = connection.cursor()
            try:
                cursor.execute(sql, None)
            except ProgrammingError:
                create_log("Error", "Error get_products")

            resul = cursor.fetchall()
            connection.close()
            return [cursor, resul]


        if param_show_filtros:
            try:
                filtros = execute_sql(build_sql('p.tags, p.brand_id', True))[1]
                tags = ''
                brands = []
                for f in filtros:
                    tags = tags + f[0]
                    brands.append(str(f[1]))
            except:
                pass
        else:
            brands = ''
            tags = ''

        if param_show_count:
            try:
                size = execute_sql(build_sql('COUNT(p.id) as size', True))[1]
                size = size[0][0]
            except:
                pass
        else:
            size = ''
        productos = execute_sql(build_sql(param_select))

        if param_show_filtros or param_show_count:
            json_final = {
                'metadata': {'size': size, 'tags': tags, 'brands': brands},
                'list': self.sql_to_json(productos),
            }
        else:
            json_final = self.sql_to_json(productos)

        return simplejson.dumps(json_final, ensure_ascii=False, encoding='latin1', use_decimal=True)
\$\endgroup\$
  • 4
    \$\begingroup\$ Welcome to codereview! Does the code work as intended? Who is MainClass? What is connection, create_log and so on ? \$\endgroup\$ – Grajdeanu Alex. Dec 20 '16 at 15:19
  • \$\begingroup\$ Yes, It's working, But i think it's ugly. MainClass contains utility functions, create_log creates a log in our database. Connection is from Django. from django.db import connection, ProgrammingError \$\endgroup\$ – Marcos Aguayo Dec 20 '16 at 15:34
  • 2
    \$\begingroup\$ I suspect param_limit_from = data.get('limit_from' '') should read param_limit_from = data.get('limit_from', '') (missing comma). Your code may also be prone to SQL injections if there is no previous checking of the dictionary data's content. Also you mix up spanish (condicion) and english (condition). You maybe want to consider using an ORM framework such as peewee or SQLAlchemy. \$\endgroup\$ – Richard Neumann Dec 20 '16 at 16:09
  • \$\begingroup\$ Thanks. The problem with ORM is that for some queries is very slow. Do you know any alternatives? \$\endgroup\$ – Marcos Aguayo Dec 20 '16 at 16:19
  • \$\begingroup\$ In most cases an ORM will not significantly impact performance. Some queries might be slow, but that is usually less the fault of the ORM and often more about of how the database is designed. Now the ORM could be slow in processing the results (loading tons of objects)... but many ORMs allow exporting the SQL query or allowing you to override the collection handlers. \$\endgroup\$ – Phil M Dec 21 '16 at 23:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.