900字范文 > python 自用的包随机user_agent 插入删除提交mysql语句

python 自用的包随机user_agent 插入删除提交mysql语句

时间：2019-10-29 03:12:22

user_agent.py，随机生成一个UA

from random import choiceclass My_UA():def __init__(self):self.USER_AGENT = ["Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; AcooBrowser; .NET CLR 1.1.4322; .NET CLR 2.0.50727)","Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; Acoo Browser; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506)","Mozilla/4.0 (compatible; MSIE 7.0; AOL 9.5; AOLBuild 4337.35; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)","Mozilla/5.0 (Windows; U; MSIE 9.0; Windows NT 9.0; en-US)","Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Win64; x64; Trident/5.0; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 2.0.50727; Media Center PC 6.0)","Mozilla/5.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 1.0.3705; .NET CLR 1.1.4322)","Mozilla/4.0 (compatible; MSIE 7.0b; Windows NT 5.2; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30)","Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN) AppleWebKit/523.15 (KHTML, like Gecko, Safari/419.3) Arora/0.3 (Change: 287 c9dfb30)","Mozilla/5.0 (X11; U; Linux; en-US) AppleWebKit/527+ (KHTML, like Gecko, Safari/419.3) Arora/0.6","Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.2pre) Gecko/0215 K-Ninja/2.1.1","Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.9) Gecko/0705 Firefox/3.0 Kapiko/3.0","Mozilla/5.0 (X11; Linux i686; U;) Gecko/0322 Kazehakase/0.4.5","Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.0.8) Gecko Fedora/1.9.0.8-1.fc10 Kazehakase/0.5.6","Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11","Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_3) AppleWebKit/535.20 (KHTML, like Gecko) Chrome/19.0.1036.7 Safari/535.20","Opera/9.80 (Macintosh; Intel Mac OS X 10.6.8; U; fr) Presto/2.9.168 Version/11.52","Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.11 (KHTML, like Gecko) Chrome/20.0.1132.11 TaoBrowser/2.0 Safari/536.11","Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/21.0.1180.71 Safari/537.1 LBBROWSER","Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; LBBROWSER)","Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; QQDownload 732; .NET4.0C; .NET4.0E; LBBROWSER)","Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.84 Safari/535.11 LBBROWSER","Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E)","Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; QQBrowser/7.0.3698.400)","Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; QQDownload 732; .NET4.0C; .NET4.0E)","Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; SV1; QQDownload 732; .NET4.0C; .NET4.0E; 360SE)","Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; QQDownload 732; .NET4.0C; .NET4.0E)","Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E)","Mozilla/5.0 (Windows NT 5.1) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/21.0.1180.89 Safari/537.1","Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/21.0.1180.89 Safari/537.1","Mozilla/5.0 (iPad; U; CPU OS 4_2_1 like Mac OS X; zh-cn) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8C148 Safari/6533.18.5","Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:2.0b13pre) Gecko/0307 Firefox/4.0b13pre","Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:16.0) Gecko/0101 Firefox/16.0","Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.64 Safari/537.11","Mozilla/5.0 (X11; U; Linux x86_64; zh-CN; rv:1.9.2.10) Gecko/0922 Ubuntu/10.10 (maverick) Firefox/3.6.10","Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36",]def __getattr__(self, attr):if attr=='random_ua':return self.get_ua()def get_ua(self):return choice(self.USER_AGENT)

用法：

one_ua=My_UA().get_ua

use_mysql.py 操作mysql语句：

import pymysql'''初始化数据库连接TEST_DB = {'host': '127.0.0.1','user': 'root','password': 'xxx','db': 'EhcoTestDb'}store = Use_Mysql(TEST_DB)插入数据方法：insert_one_data(表名，数据)data = {'id': 1, 'name': 'ehco', 'age': 20}state = store.insert_one_data('testtable'，data)删除数据(根据单个条件)方法：delete_by_field(表名, 字段, 字段值)state = store.delete_by_field('testtable','id','1')更新数据 (根据单个条件)方法：update_by_keywords(表名, 条件,数据)keywords={'id': 1}data = {'name': 'superehco', 'age': 20}state = store.update_by_id('testtable', keywords,data)返回多行数据方法：find_all(表名,行数)res = store.find_all('testtable', 5)查询数据方法：单个条件 find_by_field(表名, 字段, 字段值)多个条件 find_by_fields(表名, 条件)res=store.find_by_field('testtable', 'age', '20')res=store.find_by_fields('testtable', {'name': 'superehco', 'age': 20})直接执行sql语句：方法：query(sql)sql = "select * from testtable"res = store.query(sql)PS: 所有方法操作不成功，return的都是-1；成功的话，查询语句返回的是数据，插入/更新/删除返回的是1'''class Use_Mysql():def __init__(self, configs): #初始化账户密码之类self.configs = configsdef connect(self):#相当于选定用户、数据库self.con = pymysql.connect(host=self.configs['host'],user=self.configs['user'],password=self.configs['password'],db=self.configs['db'],charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)def close(self):'''关闭数据库链接'''self.con.close()def query(self, sql):#输入sql语句，连接、操作sql、提交、关闭'''执行sql语句，execute提交后，返回sql影响成功的行数，若失败，那就是0，此时返回-1成功后，返回获取的数据，插入、删除、更新返回的是()'''try:self.connect()with self.con.cursor() as cursor:res=cursor.execute(sql)mit()ret = cursor.fetchall() #只有查询时，才会返回数据,插入、删除、更新返回的是()if res==0:print('操作失败!')return -1else:return retexcept Exception as e:print(e)return -1finally:self.close()def insert_one_data(self, table,data):'''插入一条数据:param table: 表名:param data: 要插入的dict数据:return:插入成功返回1，失败-1'''if len(data) == 0 or isinstance(data,dict) is False:print('请确保data被正确传入了')return -1keys = ''values = ''for k,v in data.items():keys += "%s,"%str(k)values += "'%s',"%(pymysql.escape_string(str(v)))sql = "insert ignore into {} ({}) values ({})".format( #ignore不注入相同的数据table, keys[:-1], values[:-1])res = self.query(sql)if res != -1: res=1return resdef delete_by_field(self, table, field, field_value):'''从数据库里删除单个条件的记录:param table: 表名:param field:字段名:param field_value:字段值:return:成功返回1，失败-1'''sql = "delete from {} where {} = '{}'".format(table, field, field_value)res = self.query(sql)if res != -1: res=1return resdef update_by_keywords(self, table, keywords,data):'''根据keywords条件，更新数据:param table: 表名:param keywords: 更新条件，是dict:param data: 需要更新数据，dict:return:成功返回1，否则-1'''if len(data) == 0 or isinstance(data,dict) is False\or len(keywords) == 0 or isinstance(keywords,dict) is False :print('请确保data被正确传入了')return -1#更新条件 where后的语句keyword=''for k,v in keywords.items():keyword="{} = '{}'".format(k,v)#更新数据，set后的内容update_data = ''for k,v in data.items():update_data += "{}='{}',".format(str(k), pymysql.escape_string(str(v)))#执行sql语句sql = "update {} set {} where {}".format(table, update_data[:-1], keyword)#updates[:-1]因为循环添加{}时会多出一个逗号，res = self.query(sql)if res != -1: res=1return resdef find_all(self, table, limit=None):'''从数据库里查询返回对应数据，不设定数量，就全部内容返回:param table:表名字:param limit:提取的数据行数:return:'''if limit==None:sql="select * from {}".format(table)else:sql = "select * from {} limit 0,{}".format(table, limit)#limit 0 是指第一行res = self.query(sql)return resdef find_by_field(self, table, field, field_value):'''从数据库里获取符合单个条件的数据:param table:表名字:param field:字段名:param field_value:字段值:return:'''sql = "select * from {} where {} = '{}'".format(table, field, field_value)res = self.query(sql)return resdef find_by_fields(self, table, keywords={}):'''从数据库里查询同时符合多个条件的数据:param table: 表名字:param keywords: 多个条件 dict:return:'''querys = ""for k, v in keywords.items():querys += "{} = '{}' and ".format(k, v)sql = "select * from {} where {} ".format(table, querys[:-4])res = self.query(sql)return res