#!/usr/bin/python # coding: utf-8 from flask import request, Flask, send_from_directory import os from werkzeug.utils import secure_filename import pyzbar.pyzbar as pyzbar from PIL import Image, ImageEnhance import MySQLdb app = Flask(__name__) UPLOAD_DIR = '/root/upload' cxn = MySQLdb.Connect(host='127.0.0.1', user='******', passwd='123456') cur = cxn.cursor() cur.execute("USE ginsheng") @app.route('/') def hello_world(): return 'Hello World!' @app.route("/upload", methods=['POST']) def upload(): if request.method == 'POST': #qs=QssClient() #full_filr_lis=[] #print("files", request.files) #print("args:", request.args) f = request.files['file'] upload_path = os.path.join(UPLOAD_DIR, secure_filename(f.filename))
#coding=utf-8 import MySQLdb import logging logger = logging.getLogger(__name__) try: conn = MySQLdb.Connect( host="localhost", user="******", passwd="asd123", db="li", port=3306, charset="utf8", ) cur = conn.cursor() except Exception as e: logger.info(e) def insert_ip(ip, port, anonymous_degrees, country, location, connection_time, validation_time): try: # 插入正式库 sql = 'insert into tianyan_xian(url,company_name,mobile_number,company_address,email,province,city,county) values ( %s, %s,%s, %s,%s,%s,%s,%s)' a = (ip, port, anonymous_degrees, country, location, connection_time, validation_time) cur.execute(sql, a) conn.commit() except MySQLdb.Error as e: # ignore mysql error: Duplicate entry。 logger.debug(e)
import time #import json import MySQLdb while True: SQL="select * from user" Con = MySQLdb.Connect(host="127.0.0.1", port=3306, user="******", passwd="hussain", db="user") cursor=Con.cursor() cursor.execute(SQL) data=cursor.fetchall() Con.commit() #json_output=json.dumps(data) #print data ID=int(input("Enter Your ID:")) if(ID==101 or ID==102 or ID==103 or ID==104 or ID==105 or ID==106 or ID==107 or ID==108 or ID==109 or ID==110 or ID==123 or ID==330 or ID==160 or ID==157 or ID==190 or ID==160): if ID in data[0]: print "Please Wait Your data is Searching from database" time.sleep(5) #print data[0] #if you want to part by part result and design you have to write code like below... print "Name:",data[0][1] print "Email:",data[0][2] print "Salary:",data[0][3] print "Phone NO:",data[0][4] print "State:",data[0][5] print "Company:",data[0][6] elif ID in data[1]: print "Please Wait Your data is Searching from database" time.sleep(5) #print data[1]
#!/usr/bin/env python # -*- coding: utf-8 -*- # @Date : 2016-08-06 18:41:53 # @Author : yang ke ([email protected]) import MySQLdb as mysql conn = mysql.Connect(host="localhost", user="******", passwd="123456", db="reboot10", charset="utf8") curs = conn.cursor() def get_userlist(fieldlist): sql = "select %s from users" % ",".join(fieldlist) users = [] curs.execute(sql) result = curs.fetchall() for row in result: user = {} for i, k in enumerate(fieldlist): user[k] = row[i] users.append(user) return users def getone(uid): field_lsit = ["name", "name_cn", "email", "mobile", "role", "status"] sql = "select %s from users where id = %s" % (",".join(field_lsit), uid)
import glob import os import time import MySQLdb import pytz import time THRESHOLD_TIME = 10 connection = MySQLdb.Connect(user='******', passwd='password', db='water', local_infile=1) cursor = connection.cursor() DATA_BASE_PATH = '/home/muc/Desktop/water_data/' try: list_of_files = glob.glob(str(DATA_BASE_PATH) + str("/*.csv")) print list_of_files for f in list_of_files: if int(time.time()) - int(os.stat(f).st_mtime) > THRESHOLD_TIME: time.sleep(2) query = "LOAD DATA LOCAL INFILE " + "'" + f + "' INTO TABLE water_data FIELDS TERMINATED BY ',' IGNORE 1 LINES" cursor.execute(query) connection.commit() os.remove(f) except Exception, e: print e pass
import MySQLdb conn = MySQLdb.Connect(host="127.0.0.1", port=3306, user="******", passwd="", db="imooc_python", charset="utf8") cursor = conn.cursor() print conn print cursor cursor.close() conn.close()
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = 'qli25' import MySQLdb #插入多条数据 db = MySQLdb.Connect("localhost", "root", "lq910816", "PythonDB", charset='utf8') cursor = db.cursor() sql = 'SELECT COUNT(*) FROM EMPLOYEE' cursor.execute(sql) print cursor.fetchone() db.close()
#sys.stderr.write(p.stdout.read(1000)) #return False return p.stdout if __name__ == '__main__': db_name = sys.argv[1] db_user = sys.argv[2] db_pass = sys.argv[3] lang = sys.argv[4] f = sys.argv[5] log_file = sys.argv[6] db = MySQLdb.Connect(host='localhost', port=5306, user=db_user, passwd=db_pass, db=db_name, charset="utf8", use_unicode=True) #conn.autocommit(True) cursor = db.cursor() # Arguments: DB connection, wiki lang and name of log file # Currently supported: 'enwiki', 'dewiki' parser = Parser(db, cursor, lang, log_file) print "Parsing file " + f start = time.clock() pages, revisions = parser.parse(f) end = time.clock() print "Successfully parsed %s revisions " % revisions +\
def transfer(self, source_acctid, target_acctid, money): try: self.check_acct_available(source_acctid) self.check_acct_available(target_acctid) self.has_enough_money(source_acctid, money) self.reduce_money(source_acctid, money) self.add_money(target_acctid, money) self.conn.commit() except Exception as e: self.conn.rollback() raise e if __name__ == '__main__': source_acctid = sys.argv[1] target_acctid = sys.argv[2] money = sys.argv[3] conn = MySQLdb.Connect(host='127.0.0.1', user='******', passwd='a', db='mysqlforpython') tr_money = TransferMoney(conn) try: tr_money.transfer(source_acctid, target_acctid, money) except Exception as e: print "异常" + str(e) finally: conn.close()
# -*- coding: utf-8 -*- __author__ = 'Administrator' import MySQLdb conn = MySQLdb.Connect(host='localhost', user='******', passwd='123456', db='auto_testing', port=3306, charset='utf8') cur = conn.cursor() cur.execute('SELECT * FROM auto_testing ') a = cur.fetchall() cur.close() def update_output(output, id): update = conn.cursor() update.execute( 'update auto_testing SET actualoutput="{output}" WHERE id={id}'.format( output=output, id=id)) conn.commit() print(update) update.close() def result(id2): content = conn.cursor() content.execute( 'SELECT expectoutput,actualoutput FROM auto_testing WHERE id="{id2}"'.
def connect(self, dbname='', user='******'): conn = MySQLdb.Connect( **self.mysql_connection_parameters(dbname, user)) return conn, conn.cursor()
# -- coding: utf-8 -- import MySQLdb; conn = MySQLdb.Connect( host = "127.0.0.1", port = 3306, user = "******", passwd = "111111", db = "learn", charset = "utf8", ); cursor = conn.cursor(); print conn; print cursor; cursor.close(); conn.close();
Create Date: 2016/9/13 Create Time: 11:29 pip install MySQL-python """ import MySQLdb import datetime import sys try: sql = 'select now()' conn = MySQLdb.Connect(host='localhost', user='******', passwd='', db='test', port=3306, charset='utf8') cur = conn.cursor() start_time = datetime.datetime.now() rows = cur.execute(sql) result = cur.fetchall() end_time = datetime.datetime.now() time = (end_time - start_time).microseconds / 1000000.000 print "%d rows in set (%f sec)" % (rows, time) for record in result: print record cur.close() conn.close() except MySQLdb.Error, e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
# CounterNum tinyint UNSIGNED NOT NULL, # CounterValue mediumint UNSIGNED NOT NULL, # PRIMARY KEY(SerialNumber), # KEY(CounterKey) # ); # import string, os, sys, time import MySQLdb timefmt = '%Y-%m-%d %H:%M:%S' # Connect to the database try: mydb = MySQLdb.Connect(host='localhost', user='******', passwd='password', db='weather') except DatabaseError: print "Problem connecting to database" sys.exit(-1) cursor = mydb.cursor() # 1. Read the output of DigiTemp cmd = '/home/brian/temperature/digitemp -c/home/brian/temperature/digitemp.cfg -a -q' for outline in os.popen(cmd).readlines(): outline = outline[:-1] # print outline if outline[0:1] == 'T':
#coding = "utf-8" import MySQLdb conn = MySQLdb.Connect(host='127.0.0.1', port=3306, user='******', passwd='123456', db='test', charset='utf8') cursor = conn.cursor() sql_insert = "insert into user(username) values('haha')" sql_update = "update user set username='******' where userid = 4" sql_delete = "delete from user where userid<3" try: cursor.execute(sql_insert) cursor.execute(sql_insert) print cursor.rowcount cursor.execute(sql_update) print cursor.rowcount cursor.execute(sql_delete) print cursor.rowcount conn.commit() #事务提交 except Exception as e: print e conn.rollback() #事务回滚 cursor.close() conn.close()
#!/usr/bin/python3 """ matches the argument. But this time, write one that is safe from MySQL injections! """ import sys import MySQLdb if __name__ == '__main__': username = sys.argv[1] password = sys.argv[2] dbname = sys.argv[3] stateNameSearched = sys.argv[4] connection = MySQLdb.Connect(host="localhost", port=3306, user=username, passwd=password, db=dbname, charset="utf8") cursor_exe = connection.cursor() cursor_exe.execute(''' SELECT * FROM states WHERE name = %s ORDER BY id ASC ''', (stateNameSearched,)) qRows = cursor_exe.fetchall() for rows in qRows: print(rows) cursor_exe.close() connection.close()
#!/usr/bin/env python # -*- coding: utf-8 -*- import MySQLdb import sys connection = MySQLdb.Connect(host="127.0.0.1", user="******", passwd="FX_USER", db="FX", charset="utf8", local_infile=1) cursor = connection.cursor() tableName = [ "CNN_MLP_PARAS", "CNN_MLP_OUTPUT_NUMS", "CNN_MLP_W", "CNN_MLP_BN_PARAS" ] tableDef = [ "("\ "SEQ_NO bigint AUTO_INCREMENT,"\ "CURRENCY varchar(6) NOT NULL,"\ "PLUS_COUNT int(6) NOT NULL,"\ "WAIT_COUNT int(6) NOT NULL,"\ "MINUS_COUNT int(6) NOT NULL,"\ "EPISODE_NUMS int(10) NOT NULL,"\ "STEP_NUMS int(10) NOT NULL,"\ "S_DATE varchar(12) NOT NULL,"\ "E_DATE varchar(12) NOT NULL,"\ "MINIBATCH_NUMS int(5) NOT NULL,"\ "SV_DATA_NUMS int(10) NOT NULL,"\ "SV_CHANNEL_NUMS int(5) NOT NULL,"\
def main(argv=None): """script main. parses command line options in sys.argv, unless *argv* is given. """ if argv is None: argv = sys.argv parser = E.OptionParser( version= "%prog version: $Id: gpipe/gbrowser_delete_features.py 2781 2009-09-10 11:33:14Z andreas $", usage=globals()["__doc__"]) parser.add_option("-d", "--dry-run", dest="dry_run", action="store_true", help="dry run, do not execute any commands.") parser.add_option("-f", "--source-type", dest="source_type", type="string", help="source type to remove.") parser.set_defaults( dry_run=False, source_type=None, ) (options, args) = E.Start(parser, add_mysql_options=True) dbhandle = MySQLdb.Connect(host=options.host, user=options.user, passwd=options.password, port=options.port) if not options.database: raise "please supply a database." dbhandle.cursor().execute("USE %s" % options.database) cc = dbhandle.cursor() cc.execute("SHOW TABLES") tables = map(lambda x: x[0], cc.fetchall()) cc.close() if "ftype" not in tables: raise "table ftype not found in %s - is it a gbrowser database?" % ( self.database) if options.source_type: source_types = options.source_type.split(",") if len(source_types) == 1 and "%" in source_types[0]: statement = "SELECT ftypeid FROM ftype WHERE fsource LIKE '%s'" % source_types[ 0] else: statement = "SELECT ftypeid FROM ftype WHERE fsource IN ('%s')" % "','".join( source_types) if options.dry_run: print statement cc = dbhandle.cursor() cc.execute(statement) typeids = map(lambda x: str(x[0]), cc.fetchall()) cc.close() if not typeids: raise "no features of type %s found" % (options.source_type) # find annotations statement = "SELECT DISTINCTROW a.fid FROM fattribute_to_feature AS a, fdata AS b WHERE b.ftypeid IN (%s) AND a.fid = b.fid" % ",".join( typeids) if options.dry_run: print statement cc = dbhandle.cursor() cc.execute(statement) fids = map(lambda x: str(x[0]), cc.fetchall()) cc.close() # find gene identifiers (gid) in fdata for group statement = "SELECT DISTINCTROW b.gid FROM fdata AS b WHERE b.ftypeid IN (%s)" % ",".join( typeids) if options.dry_run: print statement cc = dbhandle.cursor() cc.execute(statement) gids = map(lambda x: str(x[0]), cc.fetchall()) cc.close() options.stdout.write("deleting %i genes from %s.\n" % (len(gids), options.database)) options.stdout.flush() statement = "DELETE FROM fgroup WHERE gid IN (%s)" % ",".join(gids) if options.dry_run: print statement else: dbhandle.cursor().execute(statement) options.stdout.write("deleting attributes for %i features from %s.\n" % (len(fids), options.database)) options.stdout.flush() statement = "DELETE FROM fattribute_to_feature WHERE fid IN (%s)" % ",".join( fids) if options.dry_run: print statement else: dbhandle.cursor().execute(statement) options.stdout.write("deleting features %s from %s.\n" % (",".join(typeids), options.database)) options.stdout.flush() statement = "DELETE FROM fdata WHERE ftypeid IN (%s)" % ",".join( typeids) if options.dry_run: print statement else: dbhandle.cursor().execute(statement) options.stdout.write("deleting types %s from %s.\n" % (",".join(typeids), options.database)) options.stdout.flush() statement = "DELETE FROM ftype WHERE ftypeid IN (%s)" % ",".join( typeids) if options.dry_run: print statement else: dbhandle.cursor().execute(statement) E.Stop()
import csv, time, MySQLdb connection = MySQLdb.Connect(host='localhost', user='******', passwd='', db='ufo') cursor = connection.cursor() sql_beg = 'SELECT COUNT(*) FROM sightings WHERE MBRContains(GeomFromText(\'LineString(' sql_end = ')\'), lat_lng)' lat_NW = 54.162434 lng_NW = -135.351563 lat_SE = 23.725012 lng_SE = -61.347656 # About 5 miles # lat_incr = -.0725 # lng_incr = .0725 # About 20 miles lat_incr = -.29 lng_incr = .29 lat_curr = lat_NW lng_curr = lng_NW cnt_matrix = [] num_rows = 0 while lat_curr > lat_SE:
import MySQLdb import json import random client = MySQLdb.Connect(host='172.17.0.136', port=3306, user='******', passwd='Toutiao123456', db='toutiao', charset='utf8') cursor = client.cursor() channels = {} user_ids = [1, 2, 3] with open('/Users/delron/Downloads/news14w.json', 'r') as f: while True: l = f.readline() if not l: break data = json.loads(l) channel_name = data.get('cate') if not channel_name: continue # 处理类别 channel_id = channels.get(channel_name) if not channel_id: sql = 'select channel_id from news_channel where channel_name=%s;' count = cursor.execute(sql, args=(channel_name, ))
#!/usr/bin/python import MySQLdb con = MySQLdb.Connect(user="******", passwd="abc123", db="das", host="localhost", port=3306) db = con.cursor() def drop_table(table_name): import warnings warnings.filterwarnings('ignore', "Unknown table.*") drop_query = """DROP TABLE IF EXISTS %s;""" % table_name print "[-] Dropping table %s" % table_name db.execute(drop_query) con.commit() return def create_table(table_name, params): print "[+] Creating table %s" % table_name db.execute(params) con.commit() return
import MySQLdb, itertools, requests, json from datetime import datetime, date from bs4 import BeautifulSoup import pandas as pd db = MySQLdb.Connect("127.0.0.1", "root", "", "NBA Data") cursor = db.cursor() db.set_character_set('utf8') cursor.execute('SET NAMES utf8;') cursor.execute('SET CHARACTER SET utf8;') cursor.execute('SET character_set_connection=utf8;') cursor.execute("DELETE FROM games") cursor.execute("SELECT * from teams") column_names = [col[0] for col in cursor.description] teams = [dict(itertools.izip(column_names, row)) for row in cursor.fetchall()] base_url = 'http://espn.go.com/nba/team/schedule/_/name/' year = '2016' x = 0 games = [] future_games = [] while True: team = teams[x]['team'] url = base_url + teams[x]['team'] + '/' + year + '/' + teams[x]['prefix'] r = requests.get(url) table = BeautifulSoup(r.text).table for row in table.find_all('tr')[1:]: columns = row.find_all('td') try: games_dic = {}
sql = "update account set money=money+%s where acctid=%s" % ( money, acctid) cursor.execute(sql) print "add_money:" + sql if cursor.rowcount != 1: raise Exception("账户%s加钱失败" % (acctid)) finally: cursor.close() if __name__ == "__main__": source_acctid = sys.argv[1] target_acctid = sys.argv[2] money = sys.argv[3] conn = MySQLdb.Connect(host="127.0.0.1", user="******", passwd="jiajin@123", port=3306, db="test_aaa", charset="utf8") tr_money = TransferMoney(conn) try: tr_money.transfer(source_acctid, target_acctid, money) except Exception as e: print "出现问题" + str(e) finally: conn.close()
# -*- coding: utf-8 -*- # from django.test import TestCase # from __future__ import unicode_literals # Create your tests here. import calendar import datetime import re import MySQLdb print (datetime.date.today().replace(day=1)) db = MySQLdb.Connect(host = '127.0.0.1', port = 3306, db = 'Deployment', user = '******', passwd = 'admin@123') cursor = db.cursor() tablename = 'report_datesofmonth' # cursor.execute("Truncate table "+tablename) db.commit() def insert(date): print ("Insert into "+tablename+" (weekday) values ('%s')" % (date)) cursor.execute("Insert into "+tablename+" (weekday) values ('%s')" % (date)) db.commit() # for day in calendar.monthcalendar(2018, 10): # print day def weekdays(yr,mnt):
def connect_dict(self, dbname='', user='******', **params): params.update(self.mysql_connection_parameters(dbname, user)) conn = MySQLdb.Connect(**params) return conn, MySQLdb.cursors.DictCursor(conn)
def __init__(self, fd): file = fd.formdata.getfirst("file", "") sort = fd.formdata.getfirst("sort", "") order = fd.formdata.getfirst("order", "up") cmd = fd.formdata.getfirst("cmd", "") tableID = fd.formdata.getfirst("tableID", "") addIndex = fd.formdata.getfirst("addIndex", "1") hiddenColumnsString = fd.formdata.getfirst("hiddenColumns", "") hiddenColumns = hiddenColumnsString.split(',') try: fp = open(os.path.join(webqtlConfig.TMPDIR, file + '.obj'), 'rb') tblobj = cPickle.load(fp) fp.close() if cmd == 'addCorr': dbId = int(fd.formdata.getfirst("db")) dbFullName = fd.formdata.getfirst("dbname") trait = fd.formdata.getfirst("trait") form = fd.formdata.getfirst("form") ids = fd.formdata.getfirst("ids") vals = fd.formdata.getfirst("vals") ids = eval(ids) nnCorr = len(ids) vals = eval(vals) workbook = xl.Writer('%s.xls' % (webqtlConfig.TMPDIR + file)) worksheet = workbook.add_worksheet() logger.warning( "Creating new MySQLdb cursor (this method is OBSOLETE!)") con = MySQLdb.Connect(db=webqtlConfig.DB_NAME, host=webqtlConfig.MYSQL_SERVER, user=webqtlConfig.DB_USER, passwd=webqtlConfig.DB_PASSWD) cursor = con.cursor() cursor.execute( "Select name, ShortName from ProbeSetFreeze where Id = %s", dbId) dbName, dbShortName = cursor.fetchone() tblobj['header'][0].append( THCell(HT.TD(dbShortName, Class="fs11 ffl b1 cw cbrb"), text="%s" % dbShortName, idx=tblobj['header'][0][-1].idx + 1), ) headingStyle = workbook.add_format(align='center', bold=1, border=1, size=13, fg_color=0x1E, color="white") for i, item in enumerate(tblobj['header'][0]): if (i > 0): worksheet.write([8, i - 1], item.text, headingStyle) worksheet.set_column([i - 1, i - 1], 2 * len(item.text)) for i, row in enumerate(tblobj['body']): ProbeSetId = row[1].text #XZ, 03/02/2009: Xiaodong changed Data to ProbeSetData cursor.execute(""" Select ProbeSetData.StrainId, ProbeSetData.Value From ProbeSetData, ProbeSetXRef, ProbeSet where ProbeSetXRef.ProbeSetFreezeId = %d AND ProbeSetXRef.DataId = ProbeSetData.Id AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSet.Name = '%s' """ % (dbId, ProbeSetId)) results = cursor.fetchall() vdict = {} for item in results: vdict[item[0]] = item[1] newvals = [] for id in ids: if vdict.has_key(id): newvals.append(vdict[id]) else: newvals.append(None) corr, nOverlap = webqtlUtil.calCorrelation( newvals, vals, nnCorr) repr = '%0.4f' % corr row.append( TDCell( HT.TD(HT.Href( text=repr, url= "javascript:showCorrPlotThird('%s', '%s', '%s')" % (form, dbName, ProbeSetId), Class="fs11 fwn ffl"), " / ", nOverlap, Class="fs11 fwn ffl b1 c222", align="middle"), repr, abs(corr))) last_row = 0 for j, item in enumerate(tblobj['body'][i]): if (j > 0): worksheet.write([9 + i, j - 1], item.text) last_row = 9 + i last_row += 1 titleStyle = workbook.add_format(align='left', bold=0, size=14, border=1, border_color="gray") ##Write title Info # Modified by Hongqiang Li worksheet.write([0, 0], "Citations: Please see %s/reference.html" % webqtlConfig.PORTADDR, titleStyle) worksheet.write([1, 0], "Trait : %s" % trait, titleStyle) worksheet.write([2, 0], "Database : %s" % dbFullName, titleStyle) worksheet.write([3, 0], "Date : %s" % time.strftime("%B %d, %Y", time.gmtime()), titleStyle) worksheet.write([4, 0], "Time : %s GMT" % time.strftime("%H:%M ", time.gmtime()), titleStyle) worksheet.write([ 5, 0 ], "Status of data ownership: Possibly unpublished data; please see %s/statusandContact.html for details on sources, ownership, and usage of these data." % webqtlConfig.PORTADDR, titleStyle) #Write footer info worksheet.write([ 1 + last_row, 0 ], "Funding for The GeneNetwork: NIAAA (U01AA13499, U24AA13513), NIDA, NIMH, and NIAAA (P20-DA21131), NCI MMHCC (U01CA105417), and NCRR (U01NR 105417)", titleStyle) worksheet.write( [2 + last_row, 0], "PLEASE RETAIN DATA SOURCE INFORMATION WHENEVER POSSIBLE", titleStyle) cursor.close() workbook.close() objfile = open( os.path.join(webqtlConfig.TMPDIR, file + '.obj'), 'wb') cPickle.dump(tblobj, objfile) objfile.close() else: pass self.value = str( webqtlUtil.genTableObj(tblobj=tblobj, file=file, sortby=(sort, order), tableID=tableID, addIndex=addIndex, hiddenColumns=hiddenColumns)) except: self.value = "<span class='fs16 fwb cr ffl'>The table is no longer available on this server</span>"
'All Phenotypes')] data = dict( species=species, groups=groups, types=types, datasets=datasets, ) #print("data:", data) output_file = """./wqflask/static/new/javascript/dataset_menu_structure.json""" with open(output_file, 'w') as fh: json.dump(data, fh, indent=" ", sort_keys=True) #print("\nWrote file to:", output_file) def _test_it(): """Used for internal testing only""" types = build_types("Mouse", "BXD") #print("build_types:", pf(types)) datasets = build_datasets("Mouse", "BXD", "Hippocampus") #print("build_datasets:", pf(datasets)) if __name__ == '__main__': Conn = MySQLdb.Connect(**parse_db_uri()) Cursor = Conn.cursor() main()
import MySQLdb import random import collections from nltk.stem.snowball import SnowballStemmer from nltk.corpus import stopwords from sklearn.svm import SVC from sklearn.neighbors import KNeighborsClassifier db = MySQLdb.Connect(host='localhost', port=3306, user='******', passwd='123456', db='commit_message', charset='utf8') cursor = db.cursor() def get_data(): sql = "select id, repo_id, n_label, new_message1, allennlp_tokens, allennlp_tags" \ " from message where new_message1 is not null and id > 3045 and id < 5678" cursor.execute(sql) rows = cursor.fetchall() repo_id = 101 data_set = [] repo = [] for row in rows: if row[1] != repo_id: data_set.append(repo) repo = [] repo_id = row[1]
def stock_I(num): filename = "/home/yutuo/downloads/teacherdata/股票贴吧数据/新浪股吧/" + str( num).zfill(6) if os.path.exists(filename): os.chdir("/home/yutuo/downloads/teacherdata/股票贴吧数据/新浪股吧/" + str(num).zfill(6)) print "file exits" conn[num] = MySQLdb.Connect(host='127.0.0.1', port=3306, user='******', passwd='root', db='stock', charset='utf8', use_unicode=True) curdor[num] = conn[num].cursor() conn[num].autocommit(False) post_name = str(num).zfill(6) + "_1_post" reply_name = str(num).zfill(6) + "_1_reply" print "doing:", filename #start = datetime.datetime.now() creat_reply = "create table if not exists %s" % reply_name + "(reply_id int unsigned auto_increment primary key,昵称 varchar(30),时间 varchar(30),内容 text,post_id int unsigned)" creat_post = "create table if not exists %s" % post_name + "(post_id int unsigned auto_increment primary key,访问 int(7),回复 int(5),昵称 varchar(30),时间 varchar(30),主题 varchar(50),内容 text)" #foreign_key = "alter table %s" % reply_name + " add foreign key(post_id) references %s" % post_name + "(post_id)" try: # 创建post和reply表格 curdor[num].execute(creat_post) curdor[num].execute(creat_reply) #curdor[num].execute(foreign_key) conn[num].commit() except Exception as e: #print e conn[num].rollback() os.system("cat *.txt >file_big.txt") filename_txt = "/home/yutuo/downloads/teacherdata/股票贴吧数据/新浪股吧/" + str( num).zfill(6) + "/file_big.txt" r = 0 with open(filename_txt, 'r') as fp: allLines = fp.readlines() while r < len(allLines): #处理 大文件 visit_index = re.compile(r'^访问') line_2 = (allLines[r + 0].decode('gbk')).encode('utf-8') while str(re.match(visit_index, line_2)) == 'None': r = r + 1 line_2 = (allLines[r + 0].decode('gbk')).encode('utf-8') #line_2 = allLines[num + 1].decode(encoding='gbk') line_2 = allLines[r + 1].decode(encoding='gbk') #回复 if 0 == (int(line_2[3:-1]) != 0): sql_insert = "insert into %s" % post_name + "(访问,回复,昵称,时间,主题,内容) VALUE (%s,%s,%s,%s,%s,%s)" try: line_1 = allLines[r + 0].decode(encoding='gbk') # 访问 line_3 = allLines[r + 2].decode(encoding='gbk') # 昵称 line_4 = allLines[r + 3].decode(encoding='gbk') # 时间 line_5 = allLines[r + 4].decode(encoding='gbk') # 主题 line_6 = allLines[r + 6].decode(encoding='gbk') # 内容 curdor[num].execute(sql_insert, [ line_1[3:-1], line_2[3:-1], line_3[3:-1], line_4[3:-1], line_5[3:-1], line_6[3:-1] ]) conn[num].commit() except Exception as e: # print e #print "wroing" conn[num].rollback() r = r + 10 else: sql_max_id = "select max(post_id) from %s" % post_name sql_insert_post = "insert into %s" % post_name + "(访问,回复,昵称,时间,主题,内容) VALUE (%s,%s,%s,%s,%s,%s)" sql_insert_reply = "insert into %s" % reply_name + "(昵称,时间,内容,post_id) VALUE (%s,%s,%s,%s)" try: line_1 = allLines[r + 0].decode(encoding='gbk') # 访问 line_6 = allLines[r + 6].decode(encoding='gbk') line_3 = allLines[r + 2].decode(encoding='gbk') # 昵称 line_4 = allLines[r + 3].decode(encoding='gbk') # 时间 line_5 = allLines[r + 4].decode(encoding='gbk') # 主题 curdor[num].execute(sql_insert_post, [ line_1[3:-1], line_2[3:-1], line_3[3:-1], line_4[3:-1], line_5[3:-1], line_6[3:-1] ]) conn[num].commit() except Exception as e: #print e #print "wroing2" conn[num].rollback() curdor[num].execute(sql_max_id) max_id = curdor[num].fetchone() try: for i in range(0, int(line_2[3:-1])): #处理回复 w = allLines[r + 12 + i * 5].decode( encoding='gbk') # 昵称 y = allLines[r + 13 + i * 5].decode( encoding='gbk') # 内容 t = (allLines[r + 14 + i * 5].decode('gbk')).encode( 'utf-8') # 时间 time_index = re.compile(r'^时间') while str(re.match(time_index, t)) == 'None': y = y + allLines[r + 14 + i * 5].decode(encoding='gbk') r = r + 1 t = (allLines[r + 14 + i * 5].decode('gbk')).encode('utf-8') t = allLines[r + 14 + i * 5].decode( encoding='gbk') # shijian curdor[num].execute( sql_insert_reply, [w[3:-1], t[3:-1], y[3:-1], max_id]) conn[num].commit() except Exception as e: #print e #print "wroing3" conn[num].rollback() r = r + 12 + 5 * int(line_2[3:-1]) end = datetime.datetime.now() print str(num).zfill(6), "using time:", (end - start) curdor[num].close() conn[num].close()
__author__ = 'shaleen' import MySQLdb from constants import db db_conn = MySQLdb.Connect("localhost", db.USERNAME, db.PASSWORD, "tpch", charset='utf8', use_unicode=True) db_conn.autocommit(True) class DBUtils: cursor = db_conn.cursor() sql = 'SET GLOBAL max_allowed_packet=107374182400;' cursor.execute(sql) @staticmethod def no_of_queries(): DBUtils.cursor.execute('select count(*) from ' + db.QUERY_TABLE) res = DBUtils.cursor.fetchall() no_of_queries = res[0][0] DBUtils.cursor.nextset() return no_of_queries @staticmethod def no_of_queries_lp(): DBUtils.cursor.execute('select count(*) from LPQueries') res = DBUtils.cursor.fetchall()