def save_capturesong(songlist): counter = 0 try: conn = MySQLdb.connect(host=dbhost, port=dbport, user=dbuser, passwd=dbpasswd, db=songdb) cur = conn.cursor() cur.execute("set names utf8") conn.commit() for i in range(0, len(songlist)): song = songlist[i] songid = song['songid'] name = song['name'] src = song['src'] singer = song['singer'] singertype = song['singtype'] language = song['language'] pubtime = song['pubtime'] ishd = song['ishd'] videotype = song['videotype'] album = song['album'] filesize = song['filesize'] albumid = compute_md5(album) isexist = isexist_download(conn, songid) if isexist > 0: logger.warning("The song(" + str(songid) + ") was exist in download list, skip") continue sql = ( "insert into t_runtime_download(lxsongid, src, name, pubtime, singer, singertype, language, videotype, ishd, albumname, albumid, filesize, copyright, phase, status) value(%s,%s,%s,%s,%s, %s,%s,%s,%s,%s,'" + str(albumid) + "', '" + str(filesize) + "', '乐心歌库', 1, 0)") logger.info("capture(" + songid + ") name:<<" + name + ">> singer:" + singer + ",ishd:" + ishd + ",video:" + videotype + ",src:" + src) cur.execute(sql, (MySQLdb.Binary(songid), MySQLdb.Binary(src), MySQLdb.Binary(name), MySQLdb.Binary(pubtime), MySQLdb.Binary(singer), MySQLdb.Binary(singertype), MySQLdb.Binary(language), MySQLdb.Binary(videotype), MySQLdb.Binary(ishd), MySQLdb.Binary(album))) conn.commit() counter = counter + 1 cur.close() conn.close() logger.info("[captor] step4 update " + str(len(songlist)) + " songs is finish") except Exception, e: logger.error(e) print(e)
def _pickle(obj, dump=cPickle.dump): import io import MySQLdb with io.BytesIO() as f: dump(obj, f, protocol=2) f.seek(0) return MySQLdb.Binary(f.read())
def pump_driven_end_diagnosis(data, fs, R, bearing_ratio: dict, th: dict): data = np.fromstring(data, dtype=np.float32) x = VibrationSignal(data=data, fs=fs, type=2) mp_instance = PumpDriven( x=x, y=x, r=R, bearing_ratio=bearing_ratio, ib_threshold=th["Unbalance"], ma_threshold=th["Misalignment"], bw_threshold=th["RollBearing"], al_threshold=th["ALoose"], bl_threshold=th["BLoose"], sg_threshold=th["Surge"], rb_threshold=th["Rubbing"], thd_threshold=th["thd"], pd_threshold=0, kurtosis_threshold=th["kurtosis"], harmonic_threshold=th["harmonic_threshold"], subharmonic_threshold=th["subharmonic_threshold"], ) mp_instance.diagnosis() return ( mp_instance.fault_diag_result, { "ib_mark": int(mp_instance.x_vel.harmonics_index[1]), "ma_mark": int(mp_instance.x_vel.harmonics_index[2]), "al_mark": int(mp_instance.x_vel.harmonics_index[1]), "bl_mark": [int(item) for item in mp_instance.x_vel.harmonics_index], "rb_mark": [int(item) for item in mp_instance.x_vel.harmonics_index[:5]] + [int(item) for item in mp_instance.x_vel.sub_harmonics_index], "bw_mark": [int(item) for item in mp_instance.x_env.bearing_index], "sg_mark": [int(item) for item in mp_instance.sg_index], }, { "ib_indicator": float(mp_instance.ib_indicator), "ma_indicator": float(mp_instance.ma_indicator), "bw_indicator": MySQLdb.Binary(mp_instance.x_env.bearing_amp.astype(np.float32)), "al_indicator": float(mp_instance.al_indicator), "bl_indicator": float(mp_instance.bl_indicator), "sg_indicator": float(mp_instance.sg_indicator), "rb_indicator": float(mp_instance.rb_indicator), "env_kurtosis": float(mp_instance.x_env.kurtosis), "vel_thd": float(mp_instance.x_vel.thd), }, )
def mysqlDbCamera(): #path = "f:\\img\\" #path = "/home/pi/sensor/images170/" imglist = readImg() print "1" + imglist path = "/home/pi/sensor/images170/" fp = open(path + imglist, "rb") img = fp.read() fp.close() db = MySQLdb.connect(host='localhost', user="******", passwd="123456", db="sensor", charset='utf8') # 获取操作游标 cursor = db.cursor() # createtablesql="" imgdata = MySQLdb.Binary(img) #n = cursor.execute("select * from img")#获取多少条数据 #print "照片:",n # n = cursor.execute("select id from img") #print "照片:",n #camerasql = "INSERT INTO img (id,imgname,imgs) VALUES(%s,%s,%s)" #args=(n+1,imglist[0],imgdata) camerasql = "INSERT INTO img (imgname,imgs) VALUES(%s,%s)" print "2" + imglist args = (imglist, imgdata) cursor.execute(camerasql, args) cursor.close() db.commit() # 关闭数据库连接 db.close()
def insert_cache(province,sex,time,kind,data,is_select_dict=False): cursor = connection.cursor() binary = MySQLdb.Binary(pickle.dumps(data)) cache_table_name = "cache" if is_select_dict : cache_table_name = "cache_dict" cache_sql = "insert into {cache_table_name}(province,sex,time,kind,content) values(%s,%s,%s,%s,%s)".format(cache_table_name=cache_table_name) cursor.execute(cache_sql,(province,sex,time,kind,binary))
def query(self, q, data_param, typequery): self.typequery = typequery cursor = self.cursor self.data_param = data_param if self.typequery == 'S': #S: Select SQL #print q, self.typequery,self.data_param if self.data_param == '': cursor.execute(q) else: cursor.execute(q, data_param) rows = cursor.fetchall() return rows elif self.typequery == 'S1': cursor.execute(q) rows = cursor.fetchone() return rows elif self.typequery == 'SL': #SL: SQL Select for like dato = (data_param, ) print q, dato cursor.execute(q, dato) rows = cursor.fetchall() return rows elif self.typequery == 'U': #U: update operation SQL if self.data_param == '': cursor.execute(q) else: cursor.execute(q, data_param) self.connection.commit() elif self.typequery == 'I': #I: insert operation SQL if self.data_param == '': cursor.execute(q) else: dato = data_param cursor.execute(q, data_param) self.connection.commit() elif self.typequery == 'D': #D: Delete operation SQL if self.data_param == '': cursor.execute(q) else: dato = data_param print q, dato cursor.execute(q, str(data_param)) self.connection.commit() elif self.typequery == 'II': #I: insert image operation SQL idd = data_param[0] img = data_param[1] binario = pymysql.Binary(img) data_paramx = (idd, binario) #print data_paramx cursor.execute(q, data_paramx) self.connection.commit()
def _pickle(obj, use_dill=False): import MySQLdb with io.BytesIO() as f: if use_dill: dill.dump(obj, f, protocol=3) else: pickle.dump(obj, f, protocol=3) f.seek(0) return MySQLdb.Binary(f.read())
def insert(self, sql, packetbytes=None): params = None if packetbytes != None: sql = sql + ", packetbytes=%s" params = (MySQLdb.Binary(packetbytes), ) #print "INSERTING SQL: ", sql self.conn.execute(sql, params) if self.conn.rowcount != 1: raise Exception("DBLogger: Insert did not succeed.") self.db.commit() return self.conn.lastrowid
def insert(self, name, remark, img): try: if self.cursor: sql = "insert into wx_img(name,remark,img) VALUES(%s,%s,%s)" args = (name, remark, (MySQLdb.Binary(img))) self.cursor.execute(sql, args) self.connect.commit() except: print 'error occur when insert data' self.connect.rollback()
def send_function(): json_object = {} json_object["success"] = True json_object["errors"] = [] tmp_filename = os.popen("mktemp").read().rstrip('\n') open(tmp_filename, 'wb').write(attachment.file.read()) output = os.popen("clamscan --stdout --quiet " + tmp_filename + " 2>&1").read() if len(output) > 0: os.system( 'echo ' + tmp_filename + ' | mail -s \"upload.py warning 1\" [email protected]' ) json_object["success"] = False json_object["errors"].append( "Uploaded file does not pass malware scanner") print json.dumps(json_object) return json.dumps(json_object) dbname = "estrewn" secrets_file = open("/home/ec2-user/secrets.txt") passwords = secrets_file.read().rstrip('\n') db_password = passwords.split('\n')[0] conn = MySQLdb.connect( host= 'estrewn-production-instance-1.cphov5mfizlt.us-west-2.rds.amazonaws.com', user='******', passwd=db_password, port=3306) curs = conn.cursor() curs.execute("use " + str(dbname) + ";") curs.execute( "insert into videos values(NULL,%s,%s,%s,now(6),now(6),%s)", (username, title, description, MySQLdb.Binary(open(tmp_filename, "rb").read()))) conn.commit() curs.execute("SELECT LAST_INSERT_ID()") conn.commit() print "unique_id = " + str(curs.fetchall()[0][0]) conn.close() print json.dumps(json_object) return json.dumps(json_object)
def insert_vib_2_one_table(simu_meth, table_id, station_id): engine = meta_engine initial_datetime = datetime.datetime(2016, 1, 1, 0, 0, 0, 0) for i in range(11): with engine.begin() as connection: s = text( "INSERT INTO b_vib_{0} (time,ima) " "values ('{1}',:data);".format(table_id, str(initial_datetime)) ) data, _ = simu_meth(3, 50, FS=10000, T=0.5) result = connection.execute(s, data=MySQLdb.Binary(data.astype(np.float32))) print(str(i) + " processed!") initial_datetime += datetime.timedelta(days=1)
def test_binary_prefix(self): # verify prefix behaviour when enabled, disabled and for default (disabled) for binary_prefix in (True, False, None): kwargs = self.connect_kwargs.copy() # needs to be set to can guarantee CHARSET response for normal strings kwargs['charset'] = 'utf8' if binary_prefix != None: kwargs['binary_prefix'] = binary_prefix with closing(connection_factory(**kwargs)) as conn: with closing(conn.cursor()) as c: c.execute('SELECT CHARSET(%s)', (MySQLdb.Binary(b'raw bytes'),)) self.assertEqual(c.fetchall()[0][0], 'binary' if binary_prefix else 'utf8') # normal strings should not get prefix c.execute('SELECT CHARSET(%s)', ('str',)) self.assertEqual(c.fetchall()[0][0], 'utf8')
def test_binary_prefix(self): # verify prefix behaviour when enabled, disabled and for default (disabled) for binary_prefix in (True, False, None): kwargs = self.connect_kwargs.copy() # needs to be set to can guarantee CHARSET response for normal strings kwargs["charset"] = "utf8" if binary_prefix is not None: kwargs["binary_prefix"] = binary_prefix with closing(connection_factory(**kwargs)) as conn: with closing(conn.cursor()) as c: c.execute("SELECT CHARSET(%s)", (MySQLdb.Binary(b"raw bytes"), )) self.assertEqual(c.fetchall()[0][0], "binary" if binary_prefix else "utf8") # normal strings should not get prefix c.execute("SELECT CHARSET(%s)", ("str", )) self.assertEqual(c.fetchall()[0][0], "utf8")
def setup(request): user = request.POST.get('user') test_name = request.POST.get('testName') description = request.POST.get('description', '') url = request.POST.get('apiUrl') concurrent_num = int(request.POST.get('concurrentNum')) method = request.POST.get('apiMethod') header = request.POST.get('apiHeader', '') payload = request.POST.get('apiPayload', '') timeout = int(request.POST.get('apiTimeout')) proxy = request.POST.get('apiProxy', '') parameters = request.FILES.get('parameters') repeat = int(request.POST.get('repeat', '1')) db = MySQLdb.connect("model-mysql.internal.gridx.com", "demo", "RE3u6pc8ZYx1c", "test") cursor = db.cursor() if parameters is None: insert_sql = "insert load_test (user,testName,description,apiUrl,concurrentNum,apiMethod," \ " apiHeader, apiPayload, apiTimeout, apiProxy, `repeat`)" \ " values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" else: insert_sql = "insert load_test (user,testName,description,apiUrl,concurrentNum,apiMethod," \ " apiHeader, apiPayload, apiTimeout,apiProxy, parameters, `repeat`)" \ " values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" try: if parameters is None: cursor.execute(insert_sql, [user, test_name, description, url, concurrent_num, method, header, payload, timeout, proxy, repeat]) else: cursor.execute(insert_sql, [user, test_name, description, url, concurrent_num, method, header, payload, timeout, proxy, MySQLdb.Binary(parameters.read()), repeat]) test_id = int(db.insert_id()) print test_id db.commit() except Exception, e: error = repr(e) print error db.rollback()
def update_test_case(request): test_id = request.POST.get('testId') user = request.POST.get('user') test_name = request.POST.get('testName') description = request.POST.get('description', '') url = request.POST.get('apiUrl') concurrent_num = int(request.POST.get('concurrentNum')) method = request.POST.get('apiMethod') header = request.POST.get('apiHeader', '') payload = request.POST.get('apiPayload', '') timeout = int(request.POST.get('apiTimeout')) proxy = request.POST.get('apiProxy', '') parameters = request.FILES.get('parameters') repeat = int(request.POST.get('repeat', '1')) db = MySQLdb.connect("model-mysql.internal.gridx.com", "demo", "RE3u6pc8ZYx1c", "test") cursor = db.cursor() # If no parameters file uploaded, keep the original file if parameters is None: update_sql = "update load_test set user=%s, testName=%s, description=%s, apiUrl=%s,concurrentNum=%s," \ "apiMethod=%s, apiHeader=%s,apiPayload=%s,apiTimeout=%s,apiProxy=%s, `repeat`=%s where id=%s" else: update_sql = "update load_test set user=%s, testName=%s, description=%s, apiUrl=%s,concurrentNum=%s," \ "apiMethod=%s,apiHeader=%s,apiPayload=%s,apiTimeout=%s,apiProxy=%s,parameters=%s,`repeat`=%s" \ " where id=%s" try: if parameters is None: cursor.execute(update_sql, [user, test_name, description, url, concurrent_num, method, header, payload, timeout, proxy, repeat, test_id]) else: cursor.execute(update_sql, [user, test_name, description, url, concurrent_num, method, header, payload, timeout, proxy, MySQLdb.Binary(parameters.read()), repeat, test_id]) db.commit() except Exception, e: error = repr(e) print error db.rollback() response_data = {'error': error} return produce_fail_response(response_data)
def testRWBlobData(self): # 读取源图片数据 f = open("./img/test.jpg", "rb") b = f.read() f.close() # 将图片数据写入表 cursor = self.conn.cursor() cursor.execute("INSERT INTO Dem_Picture (PicData) VALUES (%s)", (MySQLdb.Binary(b))) # self.conn.commit() # 读取表内图片数据,并写入硬盘文件 cursor.execute( "SELECT PicData FROM Dem_Picture ORDER BY ID DESC limit 1") d = cursor.fetchone()[0] cursor.close() f = open("C:\\22.jpg", "wb") f.write(d) f.close()
def insert_imgs(img, timenow): cursor = conn.cursor() cursor.execute("Insert into img(picture,time) values(%s,%s)", (MySQLdb.Binary(img), timenow)) conn.commit() cursor.close()
#!/usr/bin/env python # coding=utf-8 import MySQLdb import os import urllib conn = MySQLdb.connect(host='localhost',port=0,user='******',passwd='qweasd',db='py_plug_in',charset='utf8') cursor = conn.cursor() # with open("./ad3-2.1-cp37-cp37m-win32.whl","rb") as f: with open("./ad3-2.1-cp37-cp37m-win32.whl","rb") as f: data = f.read() # data = data.replace('\', '\\') data = data.replace("\\","\\\\") sql = 'INSERT INTO bin values("%s")'%(MySQLdb.Binary(data)) try: cursor.execute(sql) conn.commit() except Exception,e: conn.rollback() print e cursor.close() conn.close()
for i in range(10): # Starting a transaction. with engine.begin() as connection: s = text( 'INSERT INTO currentspack_%s (time,motor_id,sampling_rate,rpm) ' 'values (NOW(),:motor_id,20480,3000);' % tableHash) result = connection.execute(s, motor_id=motor_id) pack_id = result.lastrowid s = text('INSERT INTO uphase_%s (wave,pack_id) ' 'values (:wave,:pack_id);' % tableHash) connection.execute(s, wave=MySQLdb.Binary( data[1000 + i * 10000 - shift:1000 + i * 10000 + 8192 - shift].astype( np.float32)), pack_id=pack_id) s = text('INSERT INTO vphase_%s (wave,pack_id) ' 'values (:wave,:pack_id);' % tableHash) connection.execute(s, wave=MySQLdb.Binary( data[1000 + i * 10000:1000 + i * 10000 + 8192].astype(np.float32)), pack_id=pack_id) s = text('INSERT INTO wphase_%s (wave,pack_id) ' 'values (:wave,:pack_id);' % tableHash)
def CreatePlayer(self, id): # Prevents sql injection if not self.IsSafeStr(id): return False # Serialize playerData = PlayerData.PlayerData() try: byte_data = pickle.dumps(playerData) except MySQLdb.Error, e: print "[DataMgr] CreatePlayer failed." + e.args[1] return False # Writes into sql cmd = "insert into player values (%s, %s);" try: self.cursor.execute(cmd, (id, MySQLdb.Binary(byte_data))) self.sqlConn.commit() return True except MySQLdb.Error, e: self.sqlConn.rollback() print "[DataMgr] CreatePlayer: Can not write into sql. " + e.args[1] return False def CheckPassWord(self, id, pw): # Prevents sql injection if (not self.IsSafeStr(id)) or (not self.IsSafeStr(pw)): return False cmd = "select * from user where id='{0}' and pw='{1}';".format(id, pw) try: self.cursor.execute(cmd)
import MySQLdb import os conn = MySQLdb.connect(host='localhost', port=0, user='******', passwd='qweasd', db='py_plug_in') cursor = conn.cursor() with open('./aggdraw-1.3.4-cp27-cp27m-win32.whl', 'rb') as f: data = f.read() table_name = 'A' name = 'aggdraw' v = 'aggdraw-1.3.4-cp27-cp27m-win32.whl' sql = "INSERT INTO %s values('%s','%s','%s')" % (table_name, name, v, (MySQLdb.Binary(data))) # print sql # '%s','%s',%b)"%(table_name,name,v,data) # try: cursor.execute(sql) conn.commit() # except Exception,e: # db.rollback() # print str(e) cursor.close() conn.close()
def putin_songdb(conn, row): taskid = row[0] lxsongid = row[1] songid = "web_lx_" + str(lxsongid) workspace = base64.b64decode(row[2]) duration = row[3] localmp4 = row[4] encoded_mp4 = encodemp4(localmp4) wp = demjson.decode(workspace) name = wp['name'].encode('utf8') cover = wp['banner_src'] (cover_src, cover_md5) = upload_song_cover(lxsongid, cover) (video_src, video_md5) = upload_song_video(lxsongid, encoded_mp4) pinyin_first = wp['first_letter'] pinyin = wp['chinese_pinyin'] descript = wp['song_description'] lyricist = wp['lyricists'].encode('utf8') album = wp['belong_album'].encode('utf8') sql = ( "insert into t_data_song(songid, name, cover_url, cover_md5, src_url, src_md5, pinyin_first, pinyin, descript, lyricist, album) values(" "%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)") #%(songid, name, cover_src, cover_md5, video_src, video_md5, pinyin_first, pinyin, descript, lyricist, album) #); cur = conn.cursor() cur.execute("set names utf8") conn.commit() cur.execute(sql, (MySQLdb.Binary(songid), MySQLdb.Binary(name), MySQLdb.Binary(cover_src), MySQLdb.Binary(cover_md5), MySQLdb.Binary(video_src), MySQLdb.Binary(video_md5), MySQLdb.Binary(pinyin_first), MySQLdb.Binary(pinyin), MySQLdb.Binary(descript), MySQLdb.Binary(lyricist), MySQLdb.Binary(album))) conn.commit() sql = "update t_runtime_editing set status = 5 where taskid = " + str( taskid) cur.execute(sql) conn.commit() sql = "update t_runtime_download set status = 4 where taskid = " + str( taskid) cur.execute(sql) conn.commit() print(workspace) return 0
def insert_cache(province, sex, time, kind, data): cursor = connection.cursor() binary = MySQLdb.Binary(pickle.dumps(data)) cache_sql = "insert into cache(province,sex,time,kind,content) values(%s,%s,%s,%s,%s)" cursor.execute(cache_sql, (province, sex, time, kind, binary))