Beispiel #1
0
class MyTest(unittest.TestCase):
    def tearDown(self):
        print('当前用例执行后清理...')
              
        sql = 'delete from vir_host'
        params = ()
        self.dbutil.update(sql,params)
              
    def setUp(self):
        print('当前用例执行前初始化...')
        self.dbutil = DBUtil('localhost','root','','ops_db')
    @classmethod
    def tearDownClass(self):
        print('所有用例执行后的清理...')

    @classmethod
    def setUpClass(self):
        print('所有用例执行前的初始...')

    def test_update(self):
        sql = 'insert into vir_host(ip,purpose,type,state,root_pass,depart,owner,email,phone,memo)  values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        params = ('127.0.0.1',u'测试','test','active','123kissme',u'工业互联网','song','*****@*****.**','18638217761','haha')
        self.dbutil.update(sql,params)
        
        sql = 'select ip,purpose,type,state,root_pass,depart,owner,email,phone,memo from vir_host where ip = %s'
        params = ('127.0.0.1')
        result = self.dbutil.query(sql,params)
        #print(result)
        
        self.assertEqual('test',result[0][2])

    def test_batchUpdate(self):
        
        sql1 = 'insert into vir_host(ip,purpose,type,state,root_pass,depart,owner,email,phone,memo)  values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        params1 = ('127.0.0.1',u'测试1','test1','active','123kissme',u'工业互联网','song','*****@*****.**','18638217761','haha')
        sql2 = 'insert into vir_host(ip,purpose,type,state,root_pass,depart,owner,email,phone,memo)  values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        params2 = ('127.0.0.2',u'测试2','test2','active','123kissme',u'工业互联网','song','*****@*****.**','18638217761','haha')     
        
        sql_params = []
        sql_param = {}
        sql_param['sql'] = sql1
        sql_param['params'] = (params1)
        
        sql_params.append(sql_param)
        
        sql_param2 ={}
        
        sql_param2['sql'] = sql2
        sql_param2['params'] = (params2)
        sql_params.append(sql_param2)
                       
        self.dbutil.batchUpdate(sql_params)
        
        sql = 'select count(1) from vir_host '
        params = ()
        result = self.dbutil.query(sql,params)
        
        
        
        self.assertEqual(2,result[0][0])
Beispiel #2
0
parser.add_argument('-whereValue', help = 'used in update')

parser.add_argument('-column', help = 'used in delete arg')
parser.add_argument('-value', help = 'used in delete arg')

parser.add_argument('-columnList', help = 'used in query arg (use wildcard in quotes)', nargs = '*');

parser.add_argument('-name', help = 'used in insert arg')
parser.add_argument('-age', type = int, help = 'used in insert arg')
parser.add_argument('-club', help = 'used in insert arg')

args = parser.parse_args()

if args.path:
	p = args.path
	db = DBUtil(p)

if args.insert:
	db.insert(args.name, args.age, args.club)

if args.delete:
	db.delete(args.column, args.value)

if args.query:
	print(args.columnList)
	for i in db.query(args.columnList):
		print(i)

if args.update:
	db.update(args.setColumn, args.setValue, args.whereColumn, args.whereValue)
Beispiel #3
0
class UserInfoService(object):
    '''
		提供对数据库操作的接口
		1.save(data)
		2.save_flag(data)
		3.save_read_record(data)
		4.update(data)
		5.update_flag(data)
		6.update_all_flag(data)
		7.get_all()
		8.get_all_user_read()
		9.get_all_user_flag(read_flag, share_flag)
		12.get_register()
	'''
    def __init__(self):
        '''初始化数据库信息'''
        path = 'data/userinfo.db'
        table = 'userinfo'
        self.db = DBUtil(path, table)

    def create_token_table(self):
        ''' create token table'''
        userflag_sql = '''CREATE TABLE IF NOT EXISTS `tokens` (
				  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
				  `member_id` int(11) NOT NULL,
				  `token` varchar(64) NOT NULL,
				   FOREIGN KEY(`member_id`) REFERENCES `userinfo`(`member_id`)
				)'''
        conn = self.db.get_conn()
        self.db.create_table(conn, userflag_sql)

    def save(self, data):
        '''
			保存一条用户信息
			
			data = [(member_id (int), telephone (string),  
					balance(string), invite_code(string), coin(int), 
					teacher_id(int), device_code(string))]
		'''
        save_sql = '''INSERT INTO userinfo 
			(member_id, telephone, balance, coin, invite_code, teacher_id, device_code
			) 
			VALUES 
			(?, ?, ?, ?, ?, ?, ?)
		'''
        conn = self.db.get_conn()
        self.db.save(conn, save_sql, data)

    def save_token(self, data):
        '''
			保存一条用户标志
			
			args: data = [(member_id(int), token(str))]
		'''
        save_sql = '''INSERT INTO tokens
				(member_id, token) VALUES (?, ?)
			'''
        conn = self.db.get_conn()
        self.db.save(conn, save_sql, data)

    def init_all_user_token(self):
        self.create_token_table()
        users = self.get_all()
        for user in users:
            data = [(user[0], "token")]
            self.save_token(data)
            print("insert one")

    def save_flag(self, data):
        '''
			保存一条用户标志
			
			args: data = [(member_id(int),)]
		'''
        save_sql = '''INSERT INTO userflag
				(member_id) VALUES (?)
			'''
        conn = self.db.get_conn()
        self.db.save(conn, save_sql, data)

    def save_read_record(self, data):
        '''
			保存一条用户阅读信息
			
			arg: data = [(member_id(int), read_time(string), read_count(int))]
		'''
        save_sql = '''INSERT INTO userread
				(member_id, read_time, read_count)
				VALUES
				(?, ?, ?)
			'''
        conn = self.db.get_conn()
        self.db.save(conn, save_sql, data)

    def update(self, data):
        '''
			更新一条用户信息
			
			data = [balance(string), coin(int), (member_id(int)]
		'''
        update_sql = '''UPDATE userinfo 
					SET balance=?, coin=?
					WHERE member_id=?
				'''
        conn = self.db.get_conn()
        self.db.update(conn, update_sql, data)

    def update_user_info(self, member_id, device_code, token):
        '''
			update one user info
			args: member_id, device_code, token
		'''
        update_sql = '''UPDATE userinfo
				set device_code=? WHERE member_id=?'''
        data = [(device_code, member_id)]
        conn = self.db.get_conn()
        self.db.update(conn, update_sql, data)

        self.update_token([(token, member_id)])

    def update_token(self, data):
        '''
			保存一条用户标志
			
			args: data = [(token(str), member_id(int))]
		'''
        update_sql = '''UPDATE tokens
				set token=? WHERE member_id=?'''
        conn = self.db.get_conn()
        self.db.update(conn, update_sql, data)

    def update_flag(self, data):
        '''
			更新一条flag信息
			
			data = [read_flag(int{0,1,2}), share_flag(int {0, 1})), (member_id(int)]
		'''
        update_sql = '''UPDATE userflag
					SET read_flag=?, share_flag=?
					WHERE member_id=?
				'''
        conn = self.db.get_conn()
        self.db.update(conn, update_sql, data)

    def update_all_flag(self):
        '''
			更新所有read_flag信息
		'''
        update_sql = ''' UPDATE userflag
					SET read_flag=?
				'''
        conn = self.db.get_conn()
        self.db.update(conn, update_sql, [(0, )])

    def delete(self, tel):
        users = self.get_user_mobile(tel)
        for user in users:
            # update_sql = ''' DELETE FROM tokens WHERE member_id=?'''
            # conn = self.db.get_conn()
            # self.db.update(conn, update_sql, [(user[0],)])
            update_sql = ''' DELETE FROM userflag WHERE member_id=?'''
            conn = self.db.get_conn()
            self.db.update(conn, update_sql, [(user[0], )])
            update_sql = ''' DELETE FROM userread WHERE member_id=?'''
            conn = self.db.get_conn()
            self.db.update(conn, update_sql, [(user[0], )])
            update_sql = ''' DELETE FROM userinfo WHERE member_id=?'''
            conn = self.db.get_conn()
            self.db.update(conn, update_sql, [(user[0], )])

    def get_all(self):
        '''
			查询所有用户信息
		'''
        sql = '''SELECT * FROM userinfo'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql)
        return res

    def get_all_tokens(self):
        '''
			查询所有用户信息
		'''
        update_sql = ''' DELETE FROM tokens WHERE id<?'''
        conn = self.db.get_conn()
        self.db.update(conn, update_sql, [(67, )])
        sql = '''SELECT * FROM tokens'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql)
        return res

    def get_all_user_read(self):
        '''
			查询所有read_record
		'''
        sql = '''SELECT * FROM userread'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql)
        return res

    def get_all_user_flag(self, read_flag, share_flag=1):
        '''
			查询所有user_flag
			
			args: read_flag(0, 1, 2)
					share_flag(0, 1)
		'''
        sql = '''SELECT * FROM userflag WHERE read_flag=? AND share_flag=?'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql, (read_flag, share_flag))
        return res

    def get_one(self, member_id):
        '''
			查询一条用户信息
			
			arg: member_id (int)
		'''
        sql = '''SELECT * FROM userinfo WHERE member_id=?'''
        conn = self.db.get_conn()
        res = self.db.fetchone(conn, sql, uid)

        if len(res) > 0:
            return res[0]
        else:
            return None

    def get_user_mobile(self, telephone):
        '''
			查询一条用户信息
			
			arg: telephone (string)
		'''
        sql = '''SELECT * FROM userinfo WHERE telephone=?'''
        conn = self.db.get_conn()
        res = self.db.fetchone(conn, sql, telephone)

        if len(res) > 0:
            return res
        else:
            return []

    def get_token(self, member_id):
        sql = '''SELECT * FROM tokens WHERE member_id=?'''
        conn = self.db.get_conn()
        res = self.db.fetchone(conn, sql, member_id)

        if len(res) > 0:
            return res
        else:
            return []

    def get_register(self):
        '''
			查询注册用户
		'''
        sql = ''' SELECT * FROM userinfo ui, userflag uf
				WHERE ui.member_id = uf.member_id AND uf.share_flag=0
			'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql)
        if len(res) > 0:
            return res
        else:
            return []

    def get_one_user(self):
        '''
			查询一条flag=0的用户信息
		'''

        sql = '''SELECT * 
			FROM userinfo ui, userflag uf
			WHERE ui.member_id = uf.member_id AND uf.read_flag = 0 AND uf.share_flag=1
		'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql)
        if len(res) > 0:
            return res[random.randint(0, len(res) - 1)]
        else:
            return None

    def get_all_already_read_user(self):
        '''
			查询已完成的用户
		'''
        sql = '''SELECT * 
			FROM userinfo ui, userflag uf
			WHERE ui.member_id = uf.member_id AND uf.read_flag = 2 AND uf.share_flag=1
		'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql)
        return res

    def get_all_not_read_user(self):
        '''
			查询未完成的用户
		'''
        sql = '''SELECT * 
			FROM userinfo ui, userflag uf
			WHERE ui.member_id = uf.member_id AND uf.read_flag = 0 AND uf.share_flag=1
		'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql)
        return res

    def get_all_reading_user(self):
        '''
			查询正在完成的用户
		'''
        sql = '''SELECT * 
			FROM userinfo ui, userflag uf
			WHERE ui.member_id = uf.member_id AND uf.read_flag = 1 AND uf.share_flag=1
		'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql)
        return res

    def get_user_read_count(self, member_id):
        '''
			查询用户的阅读量
		'''
        time_ms = int(time.time())
        time_local = time.localtime(time_ms)
        time_format = "%Y-%m-%d"
        time_str = time.strftime(time_format, time_local)
        time_local = time.strptime(time_str, time_format)
        time_ms = int(time.mktime(time_local))
        #print("今日00:00对应毫秒:{}".format(time_ms))
        sql = '''SELECT sum(read_count) FROM userread
				WHERE member_id = ? AND read_time > ?
			'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql, (member_id, time_ms))
        if res[0][0]:
            return res[0][0]
        else:
            return 0

    def get_user_coin(self, coin):
        '''
			查询一定coin的用户
		'''
        sql = '''SELECT * FROM userinfo
				WHERE coin >= ?'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql, (score, ))
        return res

    def get_user_balance(self, balance):
        '''
			查询一定balance的用户
		'''
        sql = '''SELECT * FROM userinfo
				WHERE balance == ?'''
        conn = self.db.get_conn()
        res = self.db.fetchall(conn, sql, (balance, ))
        return res

    def get_time_str(self, time_ms, time_format="%Y-%m-%d"):
        '''
			毫秒-->指定格式
		'''
        time_local = time.localtime(time_ms)
        time_str = time.strftime(time_format, time_local)
        return time_str
Beispiel #4
0
class UserInfoService(object):
    """
    UserInfo Service
    """
    def __init__(self, init_flag=False):
        """
        init 
        """
        path = 'data/userinfo.db'
        self.query_result = ""
        self.db = DBUtil(path, init_flag=init_flag)

    def save(self, data):
        """
            save one userinfo record
            
            data = [( (uid (int), name (string), mobile (string),  
                    father (string), balance (string), coin (int), 
                    device_code (string), token (string), os (string)
                    brand (string), mac (string), android_id (string) )]
        """
        save_sql = """INSERT INTO userinfo 
            (uid , username, mobile, father, balance, coin, device_code, token, os, brand, mac, android_id) 
            VALUES 
            (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        conn = self.db.get_conn()
        self.db.save(conn, save_sql, data)

    def save_flag(self, data):
        """
            save one userflag record
            args: data = [(uid(int),)]
        """
        save_sql = """INSERT INTO userflag
                (uid) VALUES (?)
            """
        conn = self.db.get_conn()
        self.db.save(conn, save_sql, data)

    def save_read_record(self, data):
        """
            save one read record
            
            arg: data = [(uid(int), read_time(string), read_count(int))]
        """
        save_sql = """INSERT INTO userread
                (uid, read_time, read_count)
                VALUES
                (?, ?, ?)
            """
        conn = self.db.get_conn()
        self.db.save(conn, save_sql, data)

    def update(self, data):
        """
            update one userinfo record
            
            data = [
                (balance (string), coin (int), token (string), device_code(string), uid (int))
            ]
        """
        update_sql = """UPDATE userinfo 
                    SET balance=?, coin=?, token=?, device_code=?
                    WHERE uid=?
                """
        conn = self.db.get_conn()
        self.db.update(conn, update_sql, data)

    def update_read_flag(self, data):
        """
            update one userflag record
            
            data = [(read_flag(int{0,1,2}), (uid(int))]
        """
        update_sql = """UPDATE userflag
                    SET read_flag=?
                    WHERE uid=?
                """
        conn = self.db.get_conn()
        self.db.update(conn, update_sql, data)

    def clear_all_flag(self):
        """
            clear all userflag record
        """
        update_sql = """ UPDATE userflag
                    SET read_flag=?
                """
        conn = self.db.get_conn()
        self.db.update(conn, update_sql, [(0, )])

    def get_all(self):
        """
            query all userinfo records
        """
        sql = """SELECT * FROM userinfo"""
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql)
        return self.query_result

    def get_all_user_read(self):
        """
            query all userread
        """
        sql = """SELECT * FROM userread"""
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql)
        return self.query_result

    def get_all_user_flag(self, read_flag):
        """
            query all userinfo by read_flag
            
            args: read_flag(0, 1, 2)
            return: userinfo list
        """
        sql = """SELECT * FROM userflag WHERE read_flag=?"""
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql, (read_flag, ))
        return self.query_result

    def get_one(self, uid):
        """
            query one userinfo record by uid
            arg: uid (int)
            return one userinfo or None
        """
        sql = """SELECT * FROM userinfo WHERE uid=?"""
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql, (uid, ))

        if len(self.query_result) > 0:
            return self.query_result[0]
        else:
            return None

    def get_user_mobile(self, mobile):
        """
            query one userinfo record by mobile
            
            arg: mobile (string)
            :return one userinfo or None
        """
        sql = """SELECT * FROM userinfo WHERE mobile=?"""
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql, (mobile, ))

        if len(self.query_result) > 0:
            return self.query_result[0]
        else:
            return None

    def get_one_user(self):
        """
            query one userinfo record where read_flag=0
            :return one userinfo or None
        """

        sql = """SELECT * FROM userinfo ui, userflag uf
            WHERE ui.uid = uf.uid AND uf.read_flag=0"""
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql)
        if len(self.query_result) > 0:
            return self.query_result[random.randint(0,
                                                    len(self.query_result) -
                                                    1)]
        else:
            return None

    def get_all_already_read_user(self):
        """
            query userinfo records where read_flag=1
            :return  userinfo list
        """
        sql = """SELECT * 
            FROM userinfo ui, userflag uf
            WHERE ui.uid = uf.uid AND uf.read_flag=2"""
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql)
        return self.query_result

    def get_all_not_read_user(self):
        """
            query userinfo records where read_flag=0
            :return userinfo list
        """
        sql = """SELECT * FROM userinfo ui, userflag uf
            WHERE ui.uid = uf.uid AND uf.read_flag=0"""
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql)
        return self.query_result

    def get_all_reading_user(self):
        """
            query userinfo records where read_flag=1
            :return userinfo list 
        """
        sql = """SELECT * FROM userinfo ui, userflag uf
            WHERE ui.uid = uf.uid AND uf.read_flag = 1"""
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql)
        return self.query_result

    def get_user_read_count(self, uid):
        """
            query total read count by uid
            :return read_count (int)
        """
        time_ms = int(time.time())
        time_local = time.localtime(time_ms)
        time_format = "%Y-%m-%d"
        time_str = time.strftime(time_format, time_local)
        time_local = time.strptime(time_str, time_format)
        time_ms = int(time.mktime(time_local))
        sql = """SELECT sum(read_count) FROM userread
                WHERE uid = ? AND read_time > ?
            """
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql, (uid, time_ms))
        if self.query_result[0][0]:
            return self.query_result[0][0]
        else:
            return 0

    def get_user_coin(self, coin):
        """
            query userinfo records where coin >= coin
        """
        sql = """SELECT * FROM userinfo
                WHERE coin >= ?"""
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql, (coin, ))
        return self.query_result

    def get_user_balance(self, balance):
        """
            query userinfo records where balance >= balance
            :return userinfo list 
        """
        sql = """SELECT * FROM userinfo
                WHERE balance >= ?"""
        conn = self.db.get_conn()
        self.query_result = self.db.fetchall(conn, sql, (balance, ))
        return self.query_result

    def get_time_str(self, time_ms, time_format="%Y-%m-%d"):
        """
            transfer the time_ms to some format
            :return format_time string
        """
        time_local = time.localtime(time_ms)
        time_str = time.strftime(time_format, time_local)
        return time_str