def clear(self, table_name): # real_sql = 'truncate table' + table_name + ';' real_sql = "delete from" + table_name + ";" with self.conn.cursor() as cursors: cursors.execute("SET FOREIGN_KEY_CHECKS=0;") cursors.execute(real_sql) self.conn.commit()
def do_insert(self, cursors, item): insert_sql = """ insert into 汽车投诉信息 (投诉品牌,投诉车系,投诉车型,投诉简述,投诉部位,投诉故障,投诉服务,服务问题,投诉时间) value(%s,%s,%s,%s,%s,%s,%s,%s,%s) """ cursors.execute(insert_sql, (item['tspp'], item['tscx'], item['tscxing'], item['tsjs'], item['tsbw'], item['tswt'], item['tsfw'], item['fwwt'], item['tssj']))
def insert(self,table_name,table_data): for key in table_data: table_data[key] = "'"+str(table_data[key])+"'" key = ','.join(table_data.keys()) value = ','.join(table_data.keys()) real_sql = 'Insert Into'+table_name +"("+key+")Values("+value+")" with self.conn.cursor() as cursors: cursors.execute(real_sql) self.conn.commit()
def insert(self, table_name, table_data): for key in table_data: table_data[key] = "'" + str(table_data[key]) + "'" key = ','.join(table_data.keys()) value = ','.join(table_data.values()) real_sql = 'insert into' + table_name + '(' + key + ')values(' + value + ')' print(real_sql) with self.conn.cursor() as cursors: cursors.execute(real_sql) self.conn.commit()
def insert(self, table_name, table_data): for key in table_data: table_data[key] = "'" + str(table_data[key]) + "'" key = ','.join(table_data.keys()) value = ','.join(table_data.values()) real_sql = "INSERT INTO " + table_name + " (" + key + ") VALUES (" + value + ");" print(real_sql) with self.conn.cursor() as cursors: cursors.execute(real_sql) self.conn.commit()
def select(self, table, key, value): with self.conn.cursor() as cursors: sql = f'select {key} from {table} where {key} = \'{value}\';' cursors.execute(sql) self.conn.commit() result1 = cursors.fetchall() # result1 = json.loads(result1,encoding="utf-8") return result1
def clear(self, table_name): # 编写sql语句,删除指定数据库表 real_sql = "delete from " + table_name + ";" with self.conn.cursor() as cursors: # 在mysql中取消外键约束(设置外键约束: SET FOREIGN_KEY_CHECKS=1; ) cursors.execute("SET FOREIGN_KEY_CHECKS=0;") # 执行sql,删除数据库表 cursors.execute(real_sql) # 提交执行语句 self.conn.commit()
def record(serverIP, dbUser, dbPasswd, port=3306, db='systeminformations', logpath='./monitor.log'): """ { "cpu": {"user": 1, "system": 0, "idle": 99}, "memory": {"total": 972, "free": 177, "percent": 80.0}, "disk": {"percent": "13%"} } """ client = pymysql.connect(host=serverIP, user=dbUser, password=dbPasswd, db=db, port=3306) try: with client.cursor() as cursors: with open(file='monitor.log', mode='r') as file: lines = file.readlines() log = lines[0].split('-')[-1] logs = json.loads(log) hostname = lines[0].split('-')[-2] times = lines[0].split('-')[0] for items in logs.items(): if items[0] == 'cpu': sql = "insert into cpu values ('{}', '{}', {}, {}, {});" cursors.execute( sql.format(times, hostname, items[1]['user'], items[1]['system'], items[1]['idle'])) elif items[0] == 'memory': sql = "insert into mem values ({}, '{}',{}, {}, {});" cursors.execute( sql.format(times, hostname, items[1]['total'], items[1]['free'], items[1]['percent'])) elif items[0] == 'disk': sql = "insert into disk values ({}, '{}', '{}');" cursors.execute( sql.format(times, hostname, items[1]['percent'])) finally: client.commit() client.close()
from pymysql import cursors, connect # 连接数据库 conn = connect(host='127.0.0.1', user='******', password='******', db='guest', chaerset='utf8mb4', cursorsclass=cursors.DictCursor) try: with conn.cursor() as cursors: # 创建嘉宾数据 sql = 'INSERT INTO sign_guest (realname,phone,email,sign,event_id,create_time) VALUES ' \ '("tom",18800110002,"*****@*****.**",0,1,NOW());' cursors.execute(sql) # 提交事物 conn.commit() with conn.cursor() as cursors: # 查询添加的嘉宾 sql = 'SELECT realname,phone,email,sign FROM sign_guest WHERE phone=%s' cursors.execute(sql, ('18800110002', )) result = cursors.fetchone() print(result) finally: conn.close()
def getActivationCode(): numlist = range(10000000, 99999999) num_dic = random.sample(numlist, 200) code_dic = [] for num in num_dic: code_dic.append(b64encode(bytes(str(num), 'utf-8'))[:-1]) return code_dic if __name__ == '__main__': connection = pymysql.Connect(host='localhost', port=3306, user='******', passwd='12345yy') cursors = connection.cursor() cursors.execute('create database if not exists python') cursors.execute('use Python;') cursors.execute('CREATE TABLE IF NOT EXISTS `activation_code`(' '`id` INT UNSIGNED AUTO_INCREMENT,' '`code` VARCHAR(100) NOT NULL,' 'PRIMARY KEY ( `id` )' ')ENGINE=InnoDB DEFAULT CHARSET=utf8;') insert = 'insert into activation_code(code)values' code_dic = getActivationCode() i = 0 for code in code_dic: if i == 0: i += 1 else: insert += ',' insert += '("'
def clear(self,table_name): real_sql = "delete from"+table_name+";" with self.conn.cursor() as cursors: cursors.execute('SET FOREIGN_KEY_CHECK=0;') cursors.execute(real_sql) self.conn.commit()
def clear(self, table_name): real_sql = 'delete from ' + table_name + ';' with self.conn.cursor() as cursors: cursors.execute('set foreign_key_checks=0;') cursors.execute(real_sql) self.conn.commit()
def get_product_info(big_list, mid_list, small_list): ''' category_list : list(String). 해당 상품의 중/소/카테고리 --> 어디서 필요한지..? /name : String. 상품 이름 /number : String. 상품 고유번호 /brand : String. 브랜드 이름 /img : String (src). 대표 이미지 -> 복수 개일 수 있으므로 변경 필요 /product_img_list : list(String). 대표 이미지들의 리스트 /item_img_list : list(String). 상품 설명 본문 이미지들의 리스트 /rate : String. 평점 /review_count : String (**건) 상품의 리뷰 개수 /is_discount : boolean. 할인 여부 /origin_price : String (**원). 정상가 /discount_price : String (**원). 할인 가격 옵션이 없는 단일 상품의 경우, 옵션 개수를 0개로 할 것인가 1개로 할 것인가 그리고 옵션 이름 목록과 가격에 그냥 name과 price를 넣어야 하나? 품절의 경우 옵션 가격은 얼마? /option_lists : 옵션 품절 여부 (Y : 품절, N : 품절 아님) /option_count : String. 옵션 개수 /option_name_list : list(String). 옵션별 이름 /option_price_list : list(String). 옵션별 가격 ''' search_category = big_list + ' ' + mid_list + ' ' + small_list print(search_category) for category_id, cat_name in lv_list.items( ): #lv_list 아이템을 하나씩 접근해서, key, value를 각각 category_id, cat_name에 저장 if category_id == search_category: print(cat_name) sleep(2.0) print(big_list, mid_list, small_list) product_img_list = [] option_name_list = [] option_price_list = [] sold_out = 0 option_count = 0 ## name, number, brand 가져오기 name = (driver.find_element_by_class_name('prd-name')).text number = driver.find_element_by_xpath('/html/head/meta[16]').get_attribute( 'content') brand = (driver.find_element_by_class_name('category')).text img = (driver.find_element_by_css_selector('#prdImgSwiper > div > img') ).get_attribute('src') ## product_img_list product_imgs = driver.find_elements_by_css_selector( '#prdImgSwiper > div > img') product_img_list = [img.get_attribute('src') for img in product_imgs] ## item_img_list item_imgs = (driver.find_elements_by_css_selector('#prdDtlTabImg img')) item_img_list = [itm_img.get_attribute('src') for itm_img in item_imgs] # rate & review_count width = (driver.find_element_by_class_name('tit-area .inner') ).get_attribute('style') re_width = re.split(' |%', width) rate = width_to_rate(re_width[1]) org_review_count = driver.find_element_by_class_name('tit-area .num').text review_re = org_review_count.replace('(', ')') review_count = review_re.replace(')', '') ## is_discount ~ discount_price discount_price = ((driver.find_element_by_class_name('price-last') ).text.split('원')[0]).replace(',', '') origin_price = ((driver.find_element_by_class_name('price-dis') ).text.split('원')[0]).replace(',', '') # 할인 X -> price-dis 값 : '' if origin_price == '': origin_price = discount_price is_discount = False # 할인 O else: is_discount = True # option_count ~ 끝까지 try: # .top-option을 클릭해야 .optSelectMode 인지 바로 optEditMode인지 확인가능 --> 옵션 선택시 optEditMode로 넘어감 driver.find_element_by_class_name('top-option').click() try: # 옵션이 많으면 optSelectMode // 단일 옵션인 경우 opt-only-one 라는 클래스가 있음, optEditMode (except로 넘어감) driver.find_element_by_class_name('optSelectMode') options_name = driver.find_elements_by_class_name( 'prd-item > .txt') options_price = driver.find_elements_by_class_name( 'prd-item > .right > .font-num-bold') option_name_list = [ name.get_attribute('textContent') for name in options_name ] option_price_list = [ price.get_attribute('textContent') for price in options_price ] option_count = len(option_name_list) print(option_name_list) # 옵션 품절 여부 확인 option_list = driver.find_elements_by_css_selector( '.option-list li') option_lists = is_product_sold_out(option_list) except: # 단일 옵션인 경우 , option_count = 1 option_name = (driver.find_element_by_class_name( 'option-view > .name')).get_attribute('textContent') option_price = ((driver.find_element_by_class_name( 'option-view > .price-area > .font-num') ).get_attribute('textContent')).replace(',', '') option_name_list.append(option_name) option_price_list.append(option_price) option_count = 1 except: # 제품 자체 품절 -> top-option.click 불가능 --> option관련 정보 제외하고는 다 가져가야함 sold_out = 1 ## name, number, brand 가져오기 name = (driver.find_element_by_class_name('prd-name')).text number = driver.find_element_by_xpath( '/html/head/meta[16]').get_attribute('content') brand = (driver.find_element_by_class_name('category')).text img = (driver.find_element_by_css_selector('#prdImgSwiper > div > img') ).get_attribute('src') cursors.execute("select brand_id from Brand where brand_name = '" + brand + "';") brand_id = cursors.fetchone() print(type(brand_id[0])) print(brand_id[0]) driver.back() # 뒤로가기 sleep(0.5)
print("Bilgiler Eklenemedi") cursor.close() conn.close() elif giris == 2: sorgu = "SELECT * FROM araba" cursor.execute(sorgu) hepsi = cursor.fetchall() for i in hepsi: print(i[0], "<-->", i[1], i[2], i[3]) cursor.close() conn.close() elif giris == 3: baglanti = pymysql.connect(host='', unix_socket='/var/run/mysqld/mysqld.sock', user='', passwd='', db='', charset='utf8') cursors = baglanti.cursor() sorgu = "SELECT * FROM araba" cursors.execute(sorgu) hepsi = cursors.fetchall() for i in hepsi: print(i[0], "<-->", i[1], i[2], i[3]) sil = int(input("Silmek istediğiniz Km'nin İD sini giriniz: "))
from pymysql import cursors, connect #连接数据库 conn = connect(host='127.0.0.1', user='******', password='******', db='guest', charset='utf8mb4', cursorsclass=cursors.DictCursor) try: with conn.cursor() as cursors: #创建嘉宾数据 sql = 'insert into sign_guest (realname,pgone,email,sign,event_id,create_time) values ("tom",18800110002,"*****@*****.**",0,1,NOW());' cursors.execute(sql) #提交事务 conn.commit() with conn.cursor() as cursor: #查询添加的嘉宾 sql = "select realname,phone,email,aign from sign_gues where phone=%s" cursor.execute(sql, ('18800110002', )) result = cursor.fetchone() print(result) finally: conn.close()
execute():执行SQL语句 commit(): 提交数据库执行 close():关闭数据库连接 """ # 连接数据库 conn = connect(host="127.0.0.1", user="******", password="******", db="guest", charset="utf8", cursorclass=cursors.DictCursor) try: with conn.cursor() as cursors: # sign_guest添加数据 mysqls ="INSERT INTO sign_event (name_1, limit_1, status_1, address, start_time, create_time) VALUES('奔驰发布会', 4, True,'孝感', '2019-5-20 20:00:00',NOW());" cursors.execute(mysqls) conn.commit() # with conn.cursor() as cursors: # # 创建嘉宾数据库 # sql ="INSERT INTO sign_guest (realname, phone, email, sign, event_id, create_time) VALUES('德玛', 18888888880,'*****@*****.**', 0,1,NOW());" # cursors.execute(sql) # conn.commit() # with conn.cursor() as cursors: # # 查询嘉宾数据 # sql = "SELECT realname, phone, email, sign FROM sign_guest WHERE phone=%s" # cursors.execute(sql, ('18888888888',)) # result = cursors.fetchone() # print(result)
def execute_sql(sqls): for sql in sqls: print(sql) cursors.execute(sql) conn.commit()
queriesTerm = ( " SELECT CommonName,Sequence FROM GNPNDB.Terminator where TerminatorID =" + str(row[3])) queriesProm = ( " SELECT CommonName,Sequence FROM GNPNDB.Promoter where PromoterID =" + str(row[1])) queriesGene = (" SELECT CDS,ONS FROM GNPNDB.Gene where GeneID =" + str(row[2])) queriesBackbone = ( "SELECT name,Sequence from GNPNDB.backbone where backboneID in (SELECT BackboneID from GNPNDB.NewPlasmidID Where PlasmidID =(SELECT DISTINCT(PlasmidID) From GNPNDB.Cassette WHERE PlasmidID=" + str(row[4]) + "))") queriesResist = ( "SELECT Name,Sequence from GNPNDB.Resist where ResistID in (SELECT ResistanceID from GNPNDB.NewPlasmidID Where PlasmidID =(SELECT DISTINCT(PlasmidID) From GNPNDB.Cassette WHERE PlasmidID=" + str(row[4]) + "))") cursors.execute(queriesProm) cursorsFour = db.cursor() cursorsFour.execute(queriesBackbone) cursorsFive = db.cursor() cursorsFive.execute(queriesResist) rowsFive = cursorsFive.fetchone() rows = cursors.fetchone() while rows: rowsLen = len(str(rows[1])) feature = SeqFeature(FeatureLocation(start=spot, end=spot + rowsLen), type='Promoter', qualifiers={'locus_tag': rows[0]}) print rowsLen record.features.append(feature)
from pymysql import cursors, connect #连接数据库 con = connect(host='127.0.0.1', user='******', passwordd='qwe123', db='guest', charset='utf8mb4', cursorclass=cursors.DictCursor) try: with con.cursor() as cursors: sql = 'INSERT INTO sign_guest (realname,phone,email,sign,event_id,"creat_time") VALUES ("TOM",178978978978,"*****@*****.**",0,1,NOW());' cursors.execute(sql) #提交事物 con.commit() with con.cursor() as cursors: sql = "SELECT realname,phone FROM sign_guest WHERE phone=%s" cursors.execute(sql,('178978978978',)) result = cursors.fetchone() print(result) finally: con.close()
def insert_item(self, cursors, item): cursors.execute( self.sql, (item['title'], item['avatar'], item['author'], item['pub_time'], item['origin_url'], item['article_id'], item['content']))
from pymysql import cursors, connect #连接数据库 conn = connect(host='127.0.0.1', user='******', password='******', db='guest', charset='utf8mb4', cursorclass=cursors.DictCursor) try: with conn.cursor() as cursors: sql = 'INSERT INTO sign_guest (realname,phone,email,sign,event_id,creat_time) VALUES ("tom",18800110022,"*****@*****.**",0,1,NOW());' cursors.execute(sql) #提交事务 conn.commit() with conn.cursor() as cursors: sql = "SELECT realname,phone,email,sign FROM sign_guest WHERE phone=%s" cursors.execute(sql, ('18800110022')) result = cursors.fetchone() print(result) finally: conn.close()
def get_product_info(big_list, mid_list, small_list): ''' category_list : list(String). 해당 상품의 중/소/카테고리 --> 어디서 필요한지..? /name : String. 상품 이름 /number : String. 상품 고유번호 /brand : String. 브랜드 이름 /img : String (src). 대표 이미지 -> 복수 개일 수 있으므로 변경 필요 /product_img_list : list(String). 대표 이미지들의 리스트 /item_img_list : list(String). 상품 설명 본문 이미지들의 리스트 /rate : String. 평점 /review_count : String (**건) 상품의 리뷰 개수 /is_discount : boolean. 할인 여부 /origin_price : String (**원). 정상가 /discount_price : String (**원). 할인 가격 옵션이 없는 단일 상품의 경우, 옵션 개수를 0개로 할 것인가 1개로 할 것인가 그리고 옵션 이름 목록과 가격에 그냥 name과 price를 넣어야 하나? 품절의 경우 옵션 가격은 얼마? /option_lists : 옵션 품절 여부 (Y : 품절, N : 품절 아님) /option_count : String. 옵션 개수 /option_name_list : list(String). 옵션별 이름 /option_price_list : list(String). 옵션별 가격 ''' category_list = big_list+ ' '+ mid_list + ' '+ small_list sleep(1.0) print(category_list) product_img_list = [] option_name_list = [] option_price_list = [] sold_out = 0 option_count = 0 ## name, number, brand 가져오기 name = (driver.find_element_by_class_name('prd-name')).text number = driver.find_element_by_xpath('/html/head/meta[16]').get_attribute('content') brand = (driver.find_element_by_class_name('category')).text img = (driver.find_element_by_css_selector('#prdImgSwiper > div > img')).get_attribute('src') ## product_img_list product_imgs = driver.find_elements_by_css_selector('#prdImgSwiper > div > img') product_img_list = [img.get_attribute('src') for img in product_imgs] ## item_img_list item_imgs = (driver.find_elements_by_css_selector('#prdDtlTabImg img')) item_img_list = [itm_img.get_attribute('src') for itm_img in item_imgs] # rate & review_count # Review 부분은 다음에 구현 (어떻게 얻어오는지만 이해하기) width = (driver.find_element_by_class_name('tit-area .inner')).get_attribute('style') re_width = re.split(' |%',width) rate = width_to_rate(re_width[1]) org_review_count = driver.find_element_by_class_name('tit-area .num').text review_re = org_review_count.replace('(',')') review_count = review_re.replace(')','') ## is_discount ~ discount_price discount_price = ((driver.find_element_by_class_name('price-last')).text.split('원')[0]).replace(',','') origin_price = ((driver.find_element_by_class_name('price-dis')).text.split('원')[0]).replace(',','') # 할인 X -> price-dis 값 : '' if origin_price == '': origin_price = discount_price is_discount = False # 할인 O else : is_discount = True # option_count ~ 끝까지 try: # .top-option을 클릭해야 .optSelectMode 인지 바로 optEditMode인지 확인가능 --> 옵션 선택시 optEditMode로 넘어감 driver.find_element_by_class_name('top-option').click() try: # 옵션이 많으면 optSelectMode // 단일 옵션인 경우 opt-only-one 라는 클래스가 있음, optEditMode (except로 넘어감) driver.find_element_by_class_name('optSelectMode') options_name = driver.find_elements_by_class_name('prd-item > .txt') options_price = driver.find_elements_by_class_name('prd-item > .right > .font-num-bold') option_name_list = [name.get_attribute('textContent') for name in options_name] option_price_list = [price.get_attribute('textContent') for price in options_price] option_count = len(option_name_list) print(option_name_list) # 옵션 품절 여부 확인 option_list = driver.find_elements_by_css_selector('.option-list li') option_lists = is_product_sold_out(option_list) except: # 단일 옵션인 경우 , option_count = 1 option_name = (driver.find_element_by_class_name('option-view > .name')).get_attribute('textContent') option_price = ((driver.find_element_by_class_name('option-view > .price-area > .font-num')).get_attribute('textContent')).replace(',','') option_name_list.append(option_name) option_price_list.append(option_price) option_count = 1 except: # 제품 자체 품절 -> top-option.click 불가능 --> option관련 정보 제외하고는 다 가져가야함 sold_out = 1 # json data data["category_list"] = category_list data["name"] = name data["number"] = number data["brand"] = brand data["img"] = img data["item_img_list"] = item_img_list data["rate"] = rate data["review_count"] = review_count data["is_discount"] = is_discount data["origin_price"] = origin_price data["discount_price"] = discount_price data["option_count"] = option_count data["option_name_list"] = option_name_list data["option_price_list"] = option_price_list try: with open( './data/{0}/{1}/{2}/{3}.json'.format(big_list, mid_list, small_list, number), 'w', encoding='utf-8') as f: json.dump(data, f, ensure_ascii=False, indent="\t") except: # 디렉터리가 없을 때만 디렉터리를 만듦 os.makedirs('./data/{0}/{1}/{2}'.format(big_list, mid_list, small_list)) search_category = big_list + ' ' + mid_list + ' ' + small_list sql_cat_name = '' for category_id, cat_name in lv_list.items(): #lv_list 아이템을 하나씩 접근해서, key, value를 각각 category_id, cat_name에 저장 if category_id == search_category: sql_cat_name = cat_name # print (cat_name) # sql문 생성 + insert sqls = [] sqls.append("insert into Discount(is_discount, discount_price) \ values ('%s', %d);" % (is_discount_product(int(discount_price), int(origin_price)), int(discount_price))) # select문 사용해서 brand.name을 찾아서 brand_id 값 받아와서 item_brand_id 에 넣는 로직 # cursors.fetchone()은 결과 값을 tuple 형태로 반환 -> brand_id_tuple[0] 이 우리가 필요한 실제 값 --> int 타입이기 떄문에 형변환 필요 X cursors.execute("select brand_id from Brand where brand_name = '"+brand+"';") brand_id_tuple = cursors.fetchone() brand_id = brand_id_tuple[0] # item.category_name for i in range(len(item_img_list)): sqls.append("insert into Item_img(item_img, item_order) \ values('%s', %d);" % ( item_img_list[i], i + 1)) # 제품 자체가 품절인지 확인하는 로직 필요 if sold_out == 0 : sqls.append("insert into Item(item_name, item_brand_id, item_img, item_price, category_name, is_optional, barcode, buy, item_no, is_soldout) \ values ('%s', %d, '%s', %d, '%s', '%s', %d, %d, '%s','%s');" % (name, brand_id, img, int(discount_price), sql_cat_name, is_optional_product(option_count), 1, 1, number, 'N')) else: sqls.append("insert into Item(item_name, item_brand_id, item_img, item_price, category_name, is_optional, barcode, buy, item_no, is_soldout) \ values ('%s', %d, '%s', %d, '%s', '%s', %d, %d, '%s','%s');" % (name, brand_id, img, int(discount_price), sql_cat_name, is_optional_product(option_count), 1, 1, number, 'N')) # 품절인지 알아내는 로직이 필요 m = 0 if is_optional_product(option_count) == 'Y': # 단일 옵션인지 확인 if option_count != 1: for m in range(option_count): if option_lists[m] == 'Y': sqls.append("insert into Item_option(item_no, item_option_name, is_soldout) \ values('%s', '%s', '%s');" %(number, option_name_list[m],'Y')) else: sqls.append("insert into Item_option(item_no, item_option_name, is_soldout) \ values('%s', '%s', '%s');" %(number, option_name_list[m],'N')) else: # 단일 옵션인 경우 sqls.append("insert into Item_option(item_no, item_option_name, is_soldout) \ values('%s', '%s', '%s');" %(number, option_name_list[m],'N')) for i in range(len(item_img_list)): sqls.append("insert into Item_img(item_no, item_img, item_order) \ values('%s', '%s', %d);" % (number, item_img_list[i], i + 1)) # 본문 이미지 for l in range(len(product_img_list)): sqls.append("insert into Product_img(item_no, product_img) \ values('%s', '%s');" % (number, product_img_list[l])) # 카테고리 대, 중, 소 넘기기 # sqls.append("insert into Category_detail(category_one, category_two, category_three) \ # valus('%s', '%s', '%s');" %(big_list, mid_list, small_list)) f = open("sql.txt",'a') for i in sqls: f.write(i) f.write('\n') execute_sql(sqls) driver.back() # 뒤로가기 sleep(1.0)