Exemple #1
0
    def delete(self):
        """delete one record from the table

        sql: delete from users where id=2
        """
        sql = 'delete from %s %s' %(self.model.table_name, self.where_expr)
        logging.info("delete sql: ".format(sql))
        db.execute(sql, self.params)
        db.commit()
        db.cleanup()
Exemple #2
0
    def insert(self):
        """insert one record to the table

        sql: insert into users (name, password, email) values ()
        """
        sql = 'insert into %s(%s) values (%s);' % (
            self.model.table_name, ', '.join(self.model.keys()), ', '.join(['%s'] * len(self.model.values())))
        logging.info("insert sql: ".format(sql))
        db.execute(sql, self.model.values())
        db.commit()
        db.cleanup()
Exemple #3
0
    def update(self, **kwargs):
        params = []
        equations = []
        for field in self.fields:
            try:
                value = self[field]
                equations.append("{}=%s".format(field))
                params.append(value)
            except:
                pass
        if equations:
            where_expr = "where {}".format(",".join(equations))
        else:
            raise ValueError("where statement no exist")

        update_fields = []
        update_params = []
        for field, value in kwargs.iteritems():
            if field not in self.fields:
                raise AttributeError("'Model' object has no field '{}'".format(field))
            update_fields.append(field)
            update_params.append(value)

        # Set sql execute params
        update_params.extend(params)

        sql = 'update {} set {} {}'.format(self.table_name,
            ','.join(['{}=%s'.format(field) for field in update_fields]), where_expr)
        logging.info("update sql: {}".format(sql))
        logging.info("params: {}".format(update_params))
        cursor = None
        with db:
            cursor = db.execute(sql, update_params)
        return cursor
Exemple #4
0
    def delete(self):
        """delete one record from the table

        sql: delete from users where id=2
        """
        sql = 'delete from {}'.format(self.table_name)
        equations = []
        params = []
        logging.info("fields:{}".format(self.fields))
        for field in self.fields:
            try:
                value = self[field]
                equations.append("{}=%s".format(field))
                params.append(value)
            except:
                pass
        if equations:
            sql = "{} where {}".format(sql, ",".join(equations))
        else:
            raise ValueError("where statement no exist")

        logging.info("delete sql: {}".format(sql))
        logging.info("params: {}".format(params))
        cursor = None
        with db:
            cursor = db.execute(sql, params)
        return cursor
Exemple #5
0
    def insert(self):
        """insert one record to the table

        sql: insert into users (name, password, email) values ()
        """
        insert_field = self.fields.copy()
        if "id" in insert_field:
            del insert_field["id"]

        sql = "insert into {} ({}) values ({})".format(
            self.table_name, ", ".join(insert_field), ",".join(["%s"]*len(insert_field)))
        logging.info("insert sql: {}".format(sql))
        params = []
        for field in insert_field:
            try:
                value = self[field]
            except KeyError:
                # Set field default value is ""
                value = ""
            params.append(value)
        logging.info("params: {}".format(params))
        cursor = None
        with db:
            cursor = db.execute(sql, params)
        return cursor
Exemple #6
0
 def update(self, **kwargs):
     # update students set tel=default where id=5
     _keys = []
     _params = []
     for key, val in kwargs.iteritems():
         if val == None or key not in self.model.fields:
             continue
         _keys.append(key)
         _params.append(val)
     _params.extend(self.params)
     sql = 'update %s set %s %s' %(
         self.model.table_name, ','.join([key + '=%s' for key in _keys]), self.where_expr
         )
     logging.info(sql)
     db.execute(sql, _params)
     db.commit()
     db.cleanup()
Exemple #7
0
 def _select(self, need_one=False):
     sql = 'select * from %s %s' % (self.model.table_name, self.where_expr)
     logging.info("select sql:{}".format(sql))
     logging.info("params: {}".format(self.params))
     result = {}
     with db:
         cursor = db.execute(sql, self.params)
         if cursor.description:
             row_names = [x[0] for x in cursor.description]
         if need_one:
             values = cursor.fetchone()
             if values is not None:
                 result = Dict(row_names, values)
         else:
             result = [Dict(row_names, x) for x in cursor.fetchall()]
     return result
Exemple #8
0
    def select(cls, need_one=False, where=None, args=None, **kwargs):
        sql = 'select * from {}'.format(cls.table_name)
        params = []
        if where is not None:
            equations = []
            for field in where.keys():
                if field not in cls.fields:
                    raise AttributeError("'Model' object has no field '{}'".format(field))
                equations.append("{}=%s".format(field))
            if equations:
                sql = "{} where {}".format(sql, " and ".join(equations))
            params = where.values()

        orderbys = kwargs.get("orderbys", None)
        if orderbys is not None:
            orderby_expr = "order by"
            for orderby in orderbys:
                if len(orderby) != 2:
                    raise ValueError("orderby params error")
                if orderby[0] not in cls.fields:
                    raise AttributeError("'Model' object has no field '{}'".format(field))
                orderby_expr = "{} {},".format(orderby_expr, " ".join(list(orderby)))
            sql = "{} {}".format(sql, orderby_expr[:-1])

        limit = kwargs.get("limit", None)
        if limit is not None:
            sql = "{} limit {}".format(sql, ",".join(str(x) for x in limit))
        logging.info("select sql:{}".format(sql))

        result = None
        with db:
            cursor = db.execute(sql, params)
            if cursor.description:
                row_names = [x[0] for x in cursor.description]
            if need_one:
                values = cursor.fetchone()
                if values is not None:
                    result = Dict(row_names, values)
            else:
                result = [Dict(row_names, x) for x in cursor.fetchall()]
        return result
Exemple #9
0
def test_init_db():
    sql = "insert into user (name, password, email) values (%s, %s, %s)"
    values = ("suwen", "123456", "*****@*****.**")
    with db:
        cursor = db.execute(sql, values)
        assert cursor.rowcount == 1
Exemple #10
0
 def count(self):
     sql = 'select count(*) from %s %s;' % (self.model.table_name, self.where_expr)
     print sql
     (row_cnt, ) = db.execute(sql, self.params)
     return row_cnt
Exemple #11
0
def clear_table():
    with db:
        sql = "delete from {}".format(TABLE_NAME)
        db.execute(sql)