def main(): table_name = sys.argv[1] corpus = ReviewCorpus.load("model/" + table_name) read_con = MySQLdb.connect( host=DB_SERVER, port=DB_SERVER_PORT, user=DB_USER, passwd=DB_PASSWORD, db=DB_NAME, charset="utf8mb4" ) write_con = MySQLdb.connect( host=DB_SERVER, port=DB_SERVER_PORT, user=DB_USER, passwd=DB_PASSWORD, db=DB_NAME, charset="utf8mb4" ) read_cursor = read_con.cursor() read_cursor.execute(SQL_QUERY % (table_name)) write_cursor = write_con.cursor() for _ in range(read_cursor.rowcount): record = read_cursor.fetchone() hotel_review = format_review(record[1]) tfidf_score = 0 if hotel_review: tfidf_score = corpus.tfidf_score(hotel_review) write_cursor.execute(SQL_UPDATE % (table_name, tfidf_score, record[0])) write_con.commit() print "[%f]%s" % (tfidf_score, record[1]) write_cursor.close() write_con.close() read_cursor.close() read_con.close()
def run(request): PATH_VAR=request.path path=PATH_VAR.split('/') path_var=path[1] output= None status=database_status(path_var) if status == "disable": p= subprocess.Popen(["/bin/bash","/home/tcs/DEVOPS_MIGRATION/myproject/vm_launch.sh",path_var,' &'], stdout=subprocess.PIPE) output, err = p.communicate() print output cnx = MySQLdb.connect(user='******', passwd="test",db="Devops") cur = cnx.cursor() cur.execute("update devops_images set status='running' where image_name="+`str(path_var)`) cnx.commit() elif status == "running": p= subprocess.Popen(["/bin/bash","/home/tcs/DEVOPS_MIGRATION/myproject/vm_terminate.sh",path_var], stdout=subprocess.PIPE) output, err = p.communicate() cnx = MySQLdb.connect(user='******', passwd="test",db="Devops") cur = cnx.cursor() cur.execute("update devops_images set status='disable' where image_name="+`str(path_var)`) cnx.commit() t = loader.get_template('click.html') files_path=database_path() # bashCommand = "sh /home/tcs/test-launch.sh "+path_var # os.system(bashCommand) if status=="running": c = RequestContext(request,{'path':path_var,'button_name':'Terminate','test':files_path}) return HttpResponse(t.render(c)) else: #print "OUTPUT HI" c = RequestContext(request,{'path':path_var,'button_name':'Deploy','test':files_path}) return HttpResponse(t.render(c))
def open_master_connection(): """ Open a connection on the master """ if options.defaults_file: conn = MySQLdb.connect(read_default_file = options.defaults_file) config = ConfigParser.ConfigParser() try: config.read(options.defaults_file) except: pass username = config.get('client','user') password = config.get('client','password') port_number = int(config.get('client','port')) else: username = options.user port_number = options.port if options.prompt_password: password=getpass.getpass() else: password=options.password conn = MySQLdb.connect( host = options.host, user = username, passwd = password, port = options.port, unix_socket = options.socket) return conn, username, password, port_number
def SaveToken_old(token,typeToken,useruid):#typeToken is 0 for facebook token , 1 for twitter token #check if the token is present in the database db=MySQLdb.connect(host=HOST,user=DBUSER,passwd=DBPASS,db=DBNAME, use_unicode=True,charset="utf8") #db=MySQLdb.connect(HOST,DBUSER,DBPASS,DBNAME) cursor=db.cursor(MySQLdb.cursors.DictCursor) sql="SELECT count(*) as total FROM accesstoken where accesstoken='"+token+"';" rowcount=0 try: queryEsit=cursor.execute(sql) results=cursor.fetchall() dicti=results[0] rowcount=dicti['total'] except: rowcount=0 cursor.close() db.close() if rowcount>0:return db=MySQLdb.connect(host=HOST,user=DBUSER,passwd=DBPASS,db=DBNAME, use_unicode=True,charset="utf8") #db=MySQLdb.connect(HOST,DBUSER,DBPASS,DBNAME) cursor=db.cursor(MySQLdb.cursors.DictCursor) sql="INSERT INTO accesstoken (accesstoken,type,user_uid) VALUES ('%s',%d,%d);"%(token,typeToken,useruid) try: cursor.execute(sql) db.commit() except: db.rollback() cursor.close() db.close()
def _open(self, conv='', host='', user='', passwd='', db='', port=0): """Open database connection. In case of errors exceptions are thrown.""" if conv=='': self._db = MySQLdb.connect(host, user, passwd, db, port, connect_timeout = DB_TIMEOUT) else: self._db = MySQLdb.connect(conv=conv, host=host, user=user, passwd=passwd, db=db, port=port, connect_timeout = DB_TIMEOUT) self._db.set_character_set('utf8')
def another_page(): print('anotherpage') scoop = {'postername': MySQLdb.escape_string(request.form['postername']), 'activity': MySQLdb.escape_string(request.form['activity']), 'rank': request.form['rank'] } if request.method == 'POST': db = utils.db_connect() cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor) query = "INSERT INTO club_name (postername) VALUES ('" + MySQLdb.escape_string(request.form['postername']) + "')" # Print query to console (useful for debugging) print query cur.execute(query) id=cur.lastrowid #db.commit() query2 = "INSERT INTO activity (club_id, activity, rank) VALUES (" + str(id) + ", '" + MySQLdb.escape_string(request.form['activity']) + "', '" + request.form['rank'] + "')" # Print query to console (useful for debugging) print query2 cur.execute(query2) db.commit() cur.execute('SELECT DISTINCT cn.postername, a.activity, a.rank FROM club_name cn NATURAL JOIN activity a') rows = cur.fetchall() return render_template('another_page.html', club_name=rows, activity = rows, scoop = scoop)
def upload_meta(articleExists, url, title, author, domain, time_pub, text): gTime = strftime("%Y-%m-%d %H:%M:%S", gmtime()) conn = MySQLdb.connect(host="newshub.c5dehgoxr0wn.us-west-2.rds.amazonaws.com", user="******", passwd="columbiacuj", db="Newshub", charset="utf8") cursor = conn.cursor() # since we cannot get the author attribute, so we use the word "author" instead value = ['', title, author, domain, url, time_pub, gTime, text] if(articleExists == True): cursor.execute("insert into articles values (%s, %s, %s, %s, %s, %s, %s, %s)", value); else: cursor.execute("insert into deletions values (%s, %s, %s, %s, %s, %s, %s, %s)", value); conn.commit() cursor.close() conn.close() if(articleExists == True): print "upload an updated article" else: conn = MySQLdb.connect(host="newshub.c5dehgoxr0wn.us-west-2.rds.amazonaws.com", user="******", passwd="columbiacuj", db="Newshub", charset="utf8") cursor = conn.cursor() cursor.execute("delete from articles where URL=%s", url) conn.commit() cursor.close() conn.close() print "move a deleted article"
def index(req): send = [] post_data = str(req.form.list)[8:-7] if(post_data[0] == 'R'): generateStandard() db = MySQLdb.connect("localhost", "erik", "erik", db_name) cursor = db.cursor() cursor.execute("SELECT DISTINCT(user_id) FROM {}".format(table_name)) send = cursor.fetchall() db.close() elif post_data[0] == 'S': tmp = post_data.split(',') getSubtract(tmp[1], tmp[2]) else: user_id = int(post_data) db = MySQLdb.connect("localhost", "erik", "erik", db_name) cursor = db.cursor() cursor.execute("SELECT image_id, browser FROM {} WHERE user_id='{}'".format(table_name, user_id)) data = cursor.fetchall() db.close() generatePictures(data, user_id) send = gen_hash_codes(data) send_string = json.dumps(send) return send_string
def read_volt(config): data = [] chandata = [] #Read from DB db = MySQLdb.connect("localhost","monitor","1234","smartRectifier") curs = db.cursor() cmd = "SELECT srVBat FROM sensorDataCurrent ORDER BY srVBat ASC LIMIT 1" curs.execute(cmd) value = curs.fetchone() db.close() data.append(value[0]) #Update data to NULL db = MySQLdb.connect("localhost","monitor","1234","smartRectifier") curs = db.cursor() with db: cmd = 'UPDATE sensorDataCurrent SET srVBat = NULL WHERE name = "currentData"' curs.execute(cmd) db.close() for i in range(len(data)): chandata.append({"name": "Battery Voltage", "mode": "float", "unit": "Custom", "customunit": "Volt", "LimitMode": 1, "LimitMaxError": 70.0, "LimitMaxWarning": 50.0, "LimitMinWarning": 40.0, "LimitMinError": 30.0, "value": float(data[i])}) return chandata
def get_qsyk_and_insert(self, docid): cover_img = MySQLdb.escape_string(docid['cover_img']) docid = docid['docid'] if self.db_has_exist(docid): return url = "http://c.3g.163.com/nc/article/%s/full.html" % str(docid) data = utils.download_page(url, True) if data: data = data[docid] if data: ptime = data['ptime'] today = ptime.split(' ')[0] imgs = data['img'] body = data['body'].encode('utf-8') title = data['title'].replace(' ', '').replace('(', '-').replace('(', '-').replace(')', '').replace(')', '') for img in imgs: body = body.replace(img['ref'], "<img src=\"" + img['src'] + "\"/><hr>") body = body.replace('%', '%%') body = MySQLdb.escape_string(body) sql = "insert into wangyi(item_type, title, url, docid, cover_img, ptime, today, body) values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (self._item_type, title, url, docid, cover_img, ptime, today, body) utils.insert_mysql(sql)
def __init__(self, dbname,uname,passwd): self.__con= MySQLdb.connect('localhost','root', 'root') self.__cursor=self.__con.cursor() query="show databases" self.__cursor.execute(query) rows=self.__cursor.fetchall() found=False for row in rows: if (str(row[0])==dbname): found=True break #if the database does not exist create it if(found==False): query='select user from mysql.user' self.__cursor.execute(query) rows=self.__cursor.fetchall() found=False for row in rows: if(str(row[0])==uname): found=True break if(found==False): query="create user "+uname+"@localhost identified by '"+passwd+"'" self.__cursor.execute(query) query='CREATE DATABASE '+str(dbname) self.__cursor.execute(query) query="GRANT ALL PRIVILEGES ON "+dbname+".* To "+uname+"@localhost" self.__cursor.execute(query) #close the root connection and connect to the database self.__con.close() self.__con=MySQLdb.connect('localhost',uname,passwd,dbname) self.__cursor=self.__con.cursor() self.__con.commit() return
def main(): conn = MySQLdb.connect (host=settings.MYSQL_HOST, user=settings.MYSQL_USER, passwd=settings.MYSQL_PASSWORD, db=settings.MYSQL_DATABASE) cursor = conn.cursor() TABLES = ['agency', 'calendar', 'calendar_dates', 'fare_attributes','fare_rules','routes','shapes', 'stops', 'stop_times', 'trips'] for table in TABLES: print 'processing %s' % table f = open('gtfs/%s.txt' % table, 'r') reader = csv.reader(f) columns = reader.next() for row in reader: insert_row = [] for value in row: if not is_numeric(value) or value.find('E') != -1: insert_row.append('"' + MySQLdb.escape_string(value) + '"') else: insert_row.append(value) insert_sql = "INSERT INTO %s (%s) VALUES (%s);" % (table, ','.join(columns), ','.join(insert_row)) cursor.execute(insert_sql) conn.commit() cursor.close () conn.close ()
def setUp(self): _skip_if_no_MySQLdb() import MySQLdb try: # Try Travis defaults. # No real user should allow root access with a blank password. self.db = MySQLdb.connect(host='localhost', user='******', passwd='', db='pandas_nosetest') except: pass else: return try: self.db = MySQLdb.connect(read_default_group='pandas') except MySQLdb.ProgrammingError as e: raise nose.SkipTest( "Create a group of connection parameters under the heading " "[pandas] in your system's mysql default file, " "typically located at ~/.my.cnf or /etc/.my.cnf. ") except MySQLdb.Error as e: raise nose.SkipTest( "Cannot connect to database. " "Create a group of connection parameters under the heading " "[pandas] in your system's mysql default file, " "typically located at ~/.my.cnf or /etc/.my.cnf. ")
def insertSimToDB(pulseseq, params, dec): """ create an entry for a Simulation """ if not mysql: return entry_ps = repr(pulseseq.seq) entry_params = MySQLdb.escape_string(repr(params.__dict__)) entry_hspace = MySQLdb.escape_string(repr(params.hspace.__dict__)) entry_dec = MySQLdb.escape_string(repr(dec.__dict__)) dbx = MySQLdb.connect(user="******", passwd="tiqc_cluster1", db="tiqcspice", host="marvin") db = dbx.cursor() sql = "insert into Simulation (name, pulseseq, params, hspace, decoherence) values ('%s', '%s','%s','%s','%s')" % ( dec.doSQLname, entry_ps, entry_params, entry_hspace, entry_dec, ) try: db.execute(sql) except Exception, e: print "ERROR in sql insertSimToDB:", e
def redirect_short(short): val = ["shorts.html/" + short] write_log(["V"],request,val) if short[len(short)-1] == '_': db = MySQLdb.connect(host="localhost", user="******", passwd="ivB2hnBX",db="ashwin_chandak") # database info cur = db.cursor() cur.execute("SELECT * FROM clicks WHERE short_id = '%s'" % short[0:len(short)-1]) short_stats = cur.fetchall() return flask.render_template('stats.html',short_url = short[0:len(short)-1], number_clicks = len(short_stats),stats = short_stats) # retrieve short string from url db = MySQLdb.connect(host="localhost", user="******", passwd="ivB2hnBX",db="ashwin_chandak") # database info db.autocommit(True) cur = db.cursor() cur.execute("SELECT * FROM links WHERE short = '%s'" % short) # redirect if it's exist if cur.rowcount > 0: results = cur.fetchone() long_url= results[2] app.logger.debug("redirect to " + long_url) #Get IP address and json of geolocationdata, so that latitude and longitude entries can be stored in db ip_address = get_ip(request) geodata = get_geolocation(ip_address) cur.execute("INSERT INTO clicks (`short_id`,`ip_address`,`lat`,`long`) VALUES ('%s','%s',%s,%s)" % ( short,ip_address,geodata.get('latitude','NONE'),geodata.get('longitude','NONE'))) return redirect(long_url) # return 404 if not found abort(404)
def connect(self, charSet=None, dbase=None): try: if charSet == None and dbase == None: return self.__connections.getConnection() if dbase == None: dbase = self.dbName # bit ugly but mysqldb docs dont say what the default value for charset is, # then i could just set the default val for charSet if charSet != None: con = mdb.connect( self.host, self.user, self.passwd, db=dbase, port=self.port, charset=charSet) else: con = mdb.connect( self.host, self.user, self.passwd, db=dbase, port=self.port) return con except mdb.Error as dbError: self.onDBError(dbError)
def fun(): user = MySQLdb.escape_string(smart_str(request.form['user'])) unf = MySQLdb.escape_string(smart_str(request.form['unf'])) if unfollow(user, unf): posts = fget_post_all(user) return render_template("home.html", posts=posts, Username=user) return render_template("main-page.html")
def fpost(): post = MySQLdb.escape_string(smart_str(request.form['post_text'])) user = MySQLdb.escape_string(smart_str(request.form['user'])) lv = MySQLdb.escape_string(smart_str(request.form['lvalue'])) la = MySQLdb.escape_string(smart_str(request.form['ladr'])) pic = request.files['file'] if pic: ur = secure_filename(pic.filename) if '.' not in ur: ur = "." + ur if len(get_post_all(user)) > 0: ur = str(get_post_all(user)[-1][0] + 1) + ur else: ur = "1" + ur pic.save(os.path.join(app.config['UPLOAD_FOLDER'], ur)) ur = "pics/" + ur else: ur = "__empty__" if posting(user, MySQLdb.escape_string(post), ur): if la: if la[:7] != "http://": la = "http://" + la pi = int(get_post_all(user)[-1][0]) if lv: put_link(pi, la, lv) else: put_link(pi, la) session['user'] = user return redirect(url_for("hom"))
def connect_to_sql(self, sql_connect, db_name="", force_reconnect=False, create_db=True): """ Connect to SQL database or create the database and connect :param sql_connect: the variable to set :param db_name: the name of the database :param force_reconnect: force the database connection :param create_db: create the database :return the created SQL connection """ print self if sql_connect is None or force_reconnect: try: sql_connect = MySQLdb.connect(host=config.SQL_HOST, user=config.SQL_USERNAME, passwd=config.SQL_PASSWORD, db=db_name) return sql_connect except Exception, e: # Create the database if e[0] and create_db and db_name != "": if sql_connect is None: sql_connect = MySQLdb.connect(host=config.SQL_HOST, user=config.SQL_USERNAME, passwd=config.SQL_PASSWORD) utils.log("Creating database " + db_name) cur = sql_connect.cursor() cur.execute("CREATE DATABASE " + db_name) sql_connect.commit() sql_connect.select_db(db_name) return sql_connect else: utils.log("Could not connect to MySQL: %s" % e) return None
def signin(): user = MySQLdb.escape_string(smart_str(request.form['Username'])) password = MySQLdb.escape_string(smart_str(request.form['Password'])) if sign_in(user, password): posts = fget_post_all(user) return render_template("home.html", posts=posts, Username=user) return render_template("main-page.html")
def upload(upload_rows,table): try: if "adconnect" in table: db = mdb.connect(host="localhost", user="******", db='nami_adconnect_backup') cur = db.cursor() query = "REPLACE INTO `nami_adconnect_backup`.`" + table + """`(`DATE`,`PUB_NAME`,`SOURCE_NAME`,`BUDGET DEPLETED / MISCONFIGURATION`,`DAYPART BLOCK`,`CPIP FILTER`,`GEO FILTER`,`IP BLOCK`,`KEYWORD BLOCK`,`REFERER BLOCK`,`MISSING UA/REF`,`IE USER AGENT FILTER`,`CHROME USER AGENT FILTER`,`FIREFOX USER AGENT FILTER`,`SAFARI USER AGENT FILTER`,`OTHER USER AGENT FILTER`,`MAXQUERY THROTTLE`,`FEED FILTERS/TOLERANCES`,`FEED CALLS`,`TIMEOUTS`,`VALID FEED CALLS`,`CALLS PER QUERY`,`FEED COVERAGE`,`VALID FEED COVERAGE`,`PUBLISHER REQUEST RATIO`,`AD RETURN RATIO`,`AD AVAILABLE RATIO`,`CPC CLIPPED`,`EXCESS ADS`,`PUBLISHER USED LISTINGS`,`PUBLISHER COVERAGE`,`AVG PUBLISHER LISTINGS`,`AVG USED LISTINGS`,`GROSS CLICKS`, `INCOMPLETE URL`,`CLICK TIME FILTER`,`INTERASP FILTER`,`IP MISMATCH`,`UA MISMATCH`,`REFERER MISMATCH`,`CPL FILTER`,`CPQ FILTER`,`CPIP FILTER 2`,`CLICK CAP FILTER`,`COOKIE FILTERED`,`DOMAIN FILTER`,`BACKBUTTON FILTER`,`NO FLASH FILTER`,`OFFSCREEN FILTERED`,`ADVANCED JS FILTERED`,`IFRAME FILTERED`,`TOTAL FILTERED CLICKS`,`ROLLOVER REDIRECT DROPS`,`COOKIE REDIRECT DROPS`,`REFPAGE REDIRECT DROPS`,`JS REDIRECT DROPS`,`TOTAL DROPPED`,`SYSTEM ERRORS`,`TOTAL ERRORS AND DROPS`,`OFFSCREEN`,`ONSCREEN`,`NOSCREEN`,`ROLLOVER CLICKS`,`NON-ROLLOVER CLICKS`,`COOKIE PASS`,`COOKIE FAIL`,`IFRAME PASS`,`IFRAME FAIL`,`ADVANCED JS PASS`,`NATURALPLUS COMPLETE`,`REF ASSIGN JS`, `NATURAL JS`,`COMPLETED REFPAGE`,`FINAL REDIRECT`,`MAX JS NEG PLUS`,`ESTIMATED NET CLICKS`,`GOOD CLICK RATIO`,`ESTIMATED GROSS REVENUE`,`AVG ESTIMATED GROSS CPC`,`ASP ESTIMATED REVENUE`,`ESTIMATED PUBLISHER REVENUE`,`AVG ESTIMATED PUBLISHER CPC`,`ESTIMATED PUBLISHER ECPM`,`VALID NET CLICKS`,`VALID CLICKS PER PUBLISHER USED LISTINGS`,`VALID CLICK RATIO`,`VALID GROSS REVENUE`,`VALID ASP NET REVENUE`,`VALID PUBLISHER NET REVENUE`,`VALID REVENUE RATIO`,`VALID AVG GROSS CPC`,`VALID AVG PUBLISHER CPC`,`VALID PUBLISHER ECPM`) VALUES (%s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""" elif "advertiser" in table: db = mdb.connect(host="localhost", user="******", db='nami_admanager_backup', port=2000) cur = db.cursor() query = "REPLACE INTO `nami_admanager_backup`.`" + table + "`(`Date`,`Advertiser`,`Campaign`,`Adgroup`,`Ad`,`Matches`,`Impressions`,`Clicks`,`Conversions`,`Spend`,`Cost`,`CPC`,`Matches_CTR`,`Impressions_CTR`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" if len(upload_rows)>1000: chunked = chunk(upload_rows,1000) for id,row in enumerate(chunked): cur.executemany(query,row) db.commit() print("%s - SUCCESSFULLY INSERTED %s OUT OF %s ROWS INTO DATABASE"%(datetime.now(),len(row),len(upload_rows))) else: cur.executemany(query,upload_rows) db.commit() print("%s - SUCCESSFULLY INSERTED %s ROWS INTO DATABASE"%(datetime.now(),len(upload_rows))) except: print(traceback.print_exc())
def main(): localfile = 'local.txt' with open(localfile) as f: g = f.read() if g == 'True': local = True else: local = False if local == False: fldr = 'mlb-dfs/' serverinfo = security('mysql', fldr) con = MySQLdb.connect(host=serverinfo[2], user=serverinfo[0], passwd=serverinfo[1], db='MurrDogg4$dfs-mlb') else: fldr = '' con = MySQLdb.connect('localhost', 'root', '', 'dfs-mlb') #### Localhost connection gameday = datetime.date.today() datestr = datestring(gameday)[1] gameList = getSchedule(datestr) addtoDb(con, gameList, datestr) return
def putFBgenerefs(df,auth=authent_local,blocsz=10000): con = mdb.connect(**auth) # add as gene references table to local db vals= [ tuple([ None if pd.isnull(v) else v for v in rw]) for rw in df.values ] sql="""INSERT INTO generefs(pubid,FBid,symb) VALUES( %s,%s,%s) """ nadds=len(vals)*1.0 nblocs=int(np.ceil(nadds/blocsz)) print('Adding gene references to local db') with con: cur=con.cursor() cur.execute("DROP TABLE IF EXISTS generefs") cur.execute("""CREATE TABLE generefs( generef_id INT AUTO_INCREMENT PRIMARY KEY, pubid INT, FBid VARCHAR(50), symb VARCHAR(100)) """) #chunk up to keep from bombing c=0 for i in range(nblocs): con = mdb.connect(**auth) with con: cur=con.cursor() if (c+blocsz)>nadds: cur.executemany(sql,vals[c:]) else: cur.executemany(sql,vals[c:(c+blocsz)]) c+=blocsz print('Done')
def create_databases(installer_json, config_json): db_conf = installer_json['database'] db = MySQLdb.connect(host="localhost", user=db_conf['user'], passwd=db_conf['pass']) cur = db.cursor() prefix = installer_json['prefix_name'] platforms = config_json['platforms'] sql_path = '../sql/' for platform in platforms: path = sql_path + platform + '.sql' try: with open(path): db_name = prefix + '_' + platform + '_main' print '* Creating ' + platform + ' Database.' #Step 1 cur.execute('CREATE DATABASE IF NOT EXISTS ' + db_name + ' DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci') db_platform = MySQLdb.connect(host="localhost", user=db_conf['user'], passwd=db_conf['pass'], db= db_name) cur_platform = db_platform.cursor() fo = open(path, "r") db_script = fo.read() fo.close() if(db_script != ''): cur_platform.execute(db_script) except IOError: print '*** Warning: Problem creating ' + platform + ' Database.'
def save_character(json_data): json_dict = simplejson.loads(json_data) username = MySQLdb.escape_string(json_dict['username']) world_id = MySQLdb.escape_string(json_dict['world_id']) # TODO save the character return simplejson.dumps({"success": "true"})
def criartabela_troca(self): if not os.path.exists('agenciaBeta1.db'): print 'Banco de Dados existente troca' conexao = MySQLdb.connect(self.host,self.user,self.passwd,self.database ) print 'Banco de Dados ja instalado troca' #trocar aqui na linha de baixo o db conexao = MySQLdb.connect(host="localhost", user="******",passwd="carros", db="agenciaBeta1") cursor = conexao.cursor() sqlt = """create table trocae( codigotroca int(10) PRIMARY kEY Auto_increment NOT NULL UNIQUE, codigocliente varchar(60) NOT NULL , codigoveiculo varchar(10) NOT NULL , codigovendedor varchar(60) NOT NULL , placa varchar(10) NOT NULL , modelo varchar(60) NOT NULL , marca varchar(60) NOT NULL , ano varchar(60) NOT NULL , proprietario varchar(60) NOT NULL , valor float(10) , data varchar(20))""" try: #verifica se o database nomeado acima ja foi criado print ' uuuu troca' cursor.execute(sqlt) conexao.commit() print 'trocado' except MySQLdb.Error, e: if e.args[0] == 1150: print 'Tratando o erro' self.addTreeViewCliente()
def criartabela_venda(self): if not os.path.exists('agenciaBeta1.db'): print 'Banco de Dados existente' conexao = MySQLdb.connect(self.host,self.user,self.passwd,self.database ) print 'Banco de Dados ja instalado' conexao = MySQLdb.connect(host="localhost", user="******",passwd="carros", db="agenciaBeta1") cursor = conexao.cursor() sql = """create table venven( codigovenda int(10) PRIMARY kEY Auto_increment NOT NULL UNIQUE, codigo varchar(60) NOT NULL , codigoveiculo varchar(10) NOT NULL , codigovendedor varchar(60) NOT NULL , formadepagamento varchar(50) not null, financeira varchar(50)not null, data varchar(20))""" print'l' try: print ' testando 1' cursor.execute(sql) conexao.commit() print 'testando 2' except MySQLdb.Error, e: if e.args[0] == 1050: print 'Tratando o erro' self.addTreeViewCliente()
def getQiubaiDb(password='******'): setDelimiter('try database connect') userName='******' hostName='114.215.108.67' dbName='test' portName=3306 try: conn=mydb.connect(host=hostName,user=userName,passwd=password,db=dbName,port=portName) except mydb.OperationalError: while True: password=raw_input('password for %s:'%userName) try: conn=mydb.connect(host=hostName,user=userName,passwd=password,db=dbName,port=portName) break except mydb.OperationalError: continue curs=conn.cursor() searchDbSql='SELECT * FROM qiubai' try: curs.execute(searchDbSql) except: setDelimiter('no DB qiubai found') setQiubaiDb(curs) setDelimiter('database well connect') return (conn,curs)
def web_db_insert(self,item): # try: # db.insert('t_hh_dianping_tuangou_deal_info',**data) # except: # pass key_str = ','.join('`%s`' % k for k in item.keys()) value_str = ','.join( 'NULL' if v is None or v == 'NULL' else "'%s'" % MySQLdb.escape_string('%s' % v) for v in item.values()) kv_str = ','.join( "`%s`=%s" % (k, 'NULL' if v is None or v == 'NULL' else "'%s'" % MySQLdb.escape_string('%s' % v)) for (k, v) in item.items()) # print kv_str # print key_str sql = "INSERT INTO t_hh_dianping_shop_info_pet_hospital(%s) VALUES(%s)" % (key_str, value_str) sql = "%s ON DUPLICATE KEY UPDATE %s" % (sql, kv_str) print sql # with open('eeeeddddd','a') as f: # f.write(sql+'\n') # time.sleep(100) try: db.query(sql.replace('NULL','0')) except: pass
def putFBrefs(df,auth=authent_local,blocsz=2000): con = mdb.connect(**auth) # add as refs table to local db vals= [ tuple([ None if pd.isnull(v) else v for v in rw]) for rw in df.values ] sql="""INSERT INTO pub(pubid,title,ref,abstract) VALUES( %s,%s,%s,%s) """ nadds=len(vals)*1.0 nblocs=int(np.ceil(nadds/blocsz)) print('Adding to references to local db') with con: cur=con.cursor() cur.execute("DROP TABLE IF EXISTS pub") cur.execute("""CREATE TABLE pub( pubid INT PRIMARY KEY, title TEXT, ref TEXT, abstract TEXT) """) #chunk up to keep from bombing c=0 for i in range(nblocs): con = mdb.connect(**auth) with con: cur=con.cursor() if (c+blocsz)>nadds: cur.executemany(sql,vals[c:]) else: cur.executemany(sql,vals[c:(c+blocsz)]) c+=blocsz print('Done')
#/usr/bin/python #coding:utf-8 import sys reload(sys) sys.setdefaultencoding("utf-8") from flask import Flask, request, render_template, redirect import MySQLdb as mysql db = mysql.connect(host="127.0.0.1 ", user="******", passwd="ilikeit", db="reboot15", port=3306, charset='utf8') db.autocommit(True) cur = db.cursor() app = Flask(__name__) @app.route('/') def welcome(): return render_template('welcome.html') @app.route('/userlist/', methods=['GET', 'POST']) def userlist(): sql = 'select * from user' if cur.execute(sql): user_data = cur.fetchall() return render_template('userlist.html', data=user_data)
highlightbackground="#111", width=12, command=login_command) b1.grid(row=6, column=0) window.bind('<Return>',login_command) window.resizable(True, False) window.grid_columnconfigure(0, weight=1) window.mainloop() #Q3 import MySQLdb from numpy.random import randint try: con=MySQLdb.connect('localhost','root','pw','db') c=con.cursor() sql = "CREATE TABLE IF NOT EXISTS FIFA (scores int)" c.execute(sql) for i in range(1,100000): r=randint(low=10,high=30) c.execute('INSERT INTO FIFA VALUES(%s)',(r)) con.close() except: print("Error connecting db") #--- 6.4126 seconds --- to save 100000 records in Database
#+ "values ('" + strDate + "','" + imeeting_id + "','" + checkinId + "','0',now())") icursor.execute("insert into person_checkin (meeting_id,person_id,status,checkin_time) " \ + "values ('" + imeeting_id + "','" + checkinId + "','0',now())") idb.commit() except: idb.rollback() idb.close flag = False index = 0 while True: index = index + 1 id = 0 if index % 100 == 0: db = MySQLdb.connect("192.168.0.126", "root", "root", "app") cursor = db.cursor() cursor.execute( "select meeting_id from meeting_mng where status = '1' or status = '2'" ) results = cursor.fetchall() if len(results) == 1: meeting_id = results[0][0] flag = True print('checkinStart') elif len(results) > 1: flag = False print('tasklist数据异常') else: flag = False db.close()
#!C:\Python27\python.exe print "Content-Type:text/html\n\n" #print "My Appointment" import MySQLdb con=MySQLdb.connect("127.0.0.1","root","","sgpgi",3306) cur=con.cursor() query="select * from tbl_appointment" cur.execute(query) res=cur.fetchall() print """ <html> <head> <title></title> <link rel="stylesheet" type="text/css" href="css/bookcode.css"> <body> <div id="main"> <nav> <img src="images/pgilogo.png" style="width:120px;height:80px;"> <ul> <li><a href="index.html">Home</a></li> <li><a href="doctorviewprofile.py">View Appointment</a></li> <li><a href="cancelappointment.py">Cancel Appointment</a></li> <li><a href="doctorupdatelogin.py">Update Profile</a></li> <li><a href="doctorchangepass.py">Change Password</a></li> <li><a href="logout.py">Log Out</a></li> </ul> </nav> <center> <table border="1" style="font-size:25px; color:#fff;width:1000px;"> <h1>Cancel Appointment</h1><br/> <tr>
__author__="Harshit.Kashiv" import MySQLdb if __name__ == '__main__': print 'Hi' host="localhost" user="******" password="******" database="cja_test_anand" unix_socket="/tmp/mysql.sock" db = MySQLdb.connect(host = host, user = user, passwd=password, db = database, unix_socket = unix_socket) cur = cursor = db.cursor(MySQLdb.cursors.DictCursor) #cmd = '''select * from check2 limit 1''' #cmd = '''insert into check2 values (1,2)''' cmd = '''delete from jobrec''' cur.execute(cmd) #for i in xrange(24, 40): # cmd = '''insert into jobrec values (''' + str(i) + ''', '1,2,3')''' params = [] for i in [2,2,2]: #cmd = '''INSERT INTO jobrec (resid, jobs2bsent) VALUES (%s, %s) on duplicate key update resid = %s, jobs2bsent = %s, (resid, jobs2bsent)'''
''' List all cities from the db Username, password, and database name given as user args Can only use execute() once Sort ascending order by cities.id ''' import sys import MySQLdb if __name__ == "__main__": db = MySQLdb.connect(user=sys.argv[1], passwd=sys.argv[2], db=sys.argv[3], host='localhost', port=3306) cur = db.cursor() cmd = """SELECT cities.id, cities.name, states.name FROM states INNER JOIN cities ON states.id = cities.state_id ORDER BY cities.id ASC""" cur.execute(cmd) allCities = cur.fetchall() for city in allCities: print(city) cur.close() db.close()
def __init__(self): self.conn = MySQLdb.connect(host="10.6.2.121",user="******",passwd="root",port=3306,charset="utf8") self.cur = self.conn.cursor()
import nltk nltk.data.path.append('/home/srijoni/nltk_data') from nltk import pos_tag, sent_tokenize, word_tokenize fname_ = sys.argv[1] table_dm = "_" + fname_.replace(".", "_") def words_tok(text) : print "text is ", text return re.findall("[\O\Θ\Ωa-zA-Z0-9'!#$%&*+-_/=?`{|}~.\"\(\):;.,]+", text, re.UNICODE) print "Error not found here" # Open database connection dbComment = MySQLdb.connect("localhost","root","srijoni321","Comments" ,charset='utf8',use_unicode=True ) # prepare a cursor object using cursor() method cursorComment = dbComment.cursor() dbAST = MySQLdb.connect("localhost","root","srijoni321","test" ,charset='utf8', use_unicode=True ) # prepare a cursor object using cursor() method cursorAST = dbAST.cursor() # Create table as per requirement sql = """ Drop table if exists CommentAssociation""" cursorComment.execute(sql)
import MySQLdb import datetime db = MySQLdb.connect('localhost','root', '12345678', 'RESTAURANT') cursor = db.cursor() cursor.execute("""SELECT MAX(EMPLOYEE_ID) FROM EMPLOYEES;""") resp = cursor.fetchone() employeeID = resp[0] + 1 cursor.execute("""SELECT MAX(FOOD_ID) FROM MENU;""") resp = cursor.fetchone() foodID = resp[0] + 1 cursor.execute("""SELECT MAX(CUSTOMER_ID) FROM CUSTOMER;""") resp = cursor.fetchone() if resp[0] is None: customerID = 1; else: customerID = resp[0] + 1 cursor.execute("""SELECT MAX(ORDER_ID) FROM `ORDER`;""") resp = cursor.fetchone() if resp[0] is None: orderID = 1 else: orderID = resp[0] + 1 cursor.execute("""SELECT MAX(PAYMENT_ID) FROM PAYMENT;""") resp = cursor.fetchone() if resp[0] is None:
#coding:utf-8 import MySQLdb try: from localsettings import * except: dbname = '' rootusername = '' root_passwd = '' dbusername = '' password = '' try: conn = MySQLdb.Connect(host='localhost', user=rootusername, passwd=root_passwd) cursor = conn.cursor() cursor.execute('create database %s;' % (dbname)) cursor.execute( "GRANT ALL PRIVILEGES ON `%s`.* TO '%s'@'localhost' IDENTIFIED BY '%s' WITH GRANT OPTION;" % (dbname, dbusername, password)) #CREATE USER user01@'localhost' IDENTIFIED BY 'password1'; cursor.execute('FLUSH PRIVILEGES;') except: print 'fail'
#!/usr/bin/env python import MySQLdb # connect to the database db = MySQLdb.connect("localhost","root","itr0cks!","bluehalo") # setup a cursor object using cursor() method cursor = db.cursor() # run an sql question cursor.execute("SELECT VERSION()") # grab one result data = cursor.fetchone() # close the mysql database connection db.close()
# -*- coding: utf-8 -*- """ 功能:将Excel数据导入到MySQL数据库 """ import xlrd import MySQLdb # Open the workbook and define the worksheet book = xlrd.open_workbook("/Users/tq/Documents/serverStartTime.xlsx") sheet = book.sheet_by_name("Sheet1") #建立一个MySQL连接 database = MySQLdb.connect(host="54.223.192.252", user="******", passwd="QmPhaQ8hYsxx", db="test") # 获得游标对象, 用于逐行遍历数据库数据 cursor = database.cursor() # 创建插入SQL语句 query = """INSERT INTO server_startTime (sid, start_time) VALUES (%s, %s)""" # 创建一个for循环迭代读取xls文件每行数据的, 从第二行开始是要跳过标题 for r in range(1, sheet.nrows): product = sheet.cell(r, 0).value customer = sheet.cell(r, 1).value values = (product, customer) # 执行sql语句
django.setup() from app.acc.models import AccManage from app.car.models import CarInfoManage, CarFixManage, CarFixAccManage, EmployeeBonusManage from app.staff.models import EmployeeManage # 分页获取原始数据 import MySQLdb # connect() 方法用于创建数据库的连接,里面可以指定参数:用户名,密码,主机等信息。 # 这只是连接到了数据库,要想操作数据库需要创建游标。 conn = MySQLdb.connect( host='localhost', port=3306, user='******', passwd='23', db='db', charset='utf8' ) def acc(): time = 0 table = 'form_accmanage' cur.execute(count_sql % table) count = cur.fetchone()[0] while time <= count // n: cur.execute(sql_offset % (table, n, n * time)) values = cur.fetchall() acc_list = [ AccManage(old_id=item[0], common_name=item[1], name=item[2], sn=item[3], type=item[4], location=item[5],
#!/usr/bin/env python3 import MySQLdb user_input = raw_input("Please enter search and press Enter button: ") db = MySQLdb.connect(passwd="Tulsi@991", db="searchdb") mycursor = db.cursor() mycursor.execute("""SELECT * FROM test WHERE STOVEPIPE = %10s""", (user_input, )) # calls fetchone until None is returned (no more rows) for row in iter(mycursor.fetchone, None): print row
import MySQLdb as mdb import sys import urllib2 f=open('output/Index_Score.txt','w+') try: con=mdb.connect('localhost','root','iiit123','popularity'); with con: cur=con.cursor() cur.execute("SELECT a.id, a.artifact, b.score FROM Artifact_Likes a, Index_Score b WHERE a.id = b.artifact_id order by b.score DESC;") rows = cur.fetchall() for r in rows: print>>f, str(r[1]), print>>f, str(r[2]), print>>f except mdb.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1) finally: if con: f.close() con.close
### プロフィールアイコンを切り替える ### 2020-06-23 たかお import sys,json, MySQLdb,config import os import urllib.request from model.user import User from requests_oauthlib import OAuth1Session con = MySQLdb.connect( host = config.DB_HOST, port = config.DB_PORT, db = config.DB_DATABASE, user = config.DB_USER, passwd = config.DB_PASSWORD, charset = config.DB_CHARSET ) con.autocommit(False) cursor = con.cursor(MySQLdb.cursors.DictCursor) try: print("ダウンロードするアイコン画像の情報を確認しています...") cursor.execute( " SELECT RI.user_id,RI.sequence,RI.url "\ " FROM profile_icons RI "\ " WHERE RI.completed = 0 "\ " AND RI.create_datetime = ( "\ " SELECT MAX(RI2.update_datetime) "\ " FROM profile_icons RI2 "\ " WHERE RI2.user_id = RI.user_id "\ " ) "\
def callback(): quote_page = 'https://en.wikipedia.org/wiki/List_of_programming_languages' page = urllib2.urlopen(quote_page) soup = BeautifulSoup(page, 'html.parser') listbox = Listbox(master, width=100, height=100) listbox.pack(side="left", fill="y") scrollbar = Scrollbar(master, orient="vertical") scrollbar.config(command=listbox.yview) scrollbar.pack(side="right", fill="y") listbox.config(yscrollcommand=scrollbar.set) List1 = soup.find('div', attrs={'class': 'div-col columns column-width'}) mydb = MySQLdb.connect(host = "127.0.0.1", user = "******", passwd = "", db ="data1") cur = mydb.cursor() def goto(linenum): global line line = linenum cur.execute("delete from list") line = 1 while True: if List1 == None: break else: List_box = List1.find_all('li') for b in List_box: List = b.text.strip() List = List.encode('utf-8') if isinstance(List, unicode) else List #print List cur.execute('''INSERT INTO list(languages) VALUES( "%s")''' %(List)) mydb.commit() listbox.insert(END, List) next_div = List1.find_next('div', attrs={'class': 'div-col columns column-width'}) List1 = next_div listbox.pack(fill=BOTH, expand=YES) goto(1) cur.execute("select count(*) from list") info = cur.fetchone() for row in info: if row != 0: tkMessageBox.showinfo("Information", "List Successfully Inserted to Database") cur.close()
#!/usr/bin/python3 """Lists all cities by state""" import MySQLdb from sys import argv if __name__ == "__main__": conn = MySQLdb.connect(host="localhost", port=3306, charset="utf8", user=argv[1], passwd=argv[2], db=argv[3]) cur = conn.cursor() cur.execute(""" SELECT cities.id, cities.name, states.name FROM cities LEFT JOIN states ON cities.state_id = states.id ORDER BY cities.id ASC; """) query_rows = cur.fetchall() for row in query_rows: print(row) cur.close() conn.close()
#!/usr/bin/python import MySQLdb db = MySQLdb.connect("localhost", "root", "root", "test") cursor = db.cursor() sql = """insert into employee(first_name, last_name, sex, age, income) values("rupak","rokade","Male",29,50000)""" try: cursor.execute(sql) db.commit() except: db.rollback() db.close()
# -*- coding: utf-8 -*- import ConfigParser, MySQLdb import sys config = ConfigParser.ConfigParser() config.read(sys.argv[1]) hostname = config.get('mysql', 'host') dbname = config.get('mysql', 'dbname') username = config.get('mysql', 'user') password = config.get('mysql', 'passwd') db = MySQLdb.connect(hostname, username, password, dbname, use_unicode=True) cursor = db.cursor() cursor.execute('SET NAMES utf8') cursor.execute("""DELETE FROM tag_errors WHERE error_type = 4""") cursor.execute("""DELETE FROM tag_errors WHERE error_type = 6""") cursor.execute("""SELECT book_id, tag_name FROM book_tags WHERE (book_id in (select book_id from books where parent_id = 8 or parent_id = 56)) and (tag_name LIKE 'url:%') and (tag_name NOT LIKE '%oldid=%')""" ) data = cursor.fetchall() for i in data: query = """INSERT INTO tag_errors VALUES(%d, '%s', %d)""" % (i[0], i[1], 4) cursor.execute(query) cursor.execute( """SELECT distinct book_id FROM books WHERE book_id NOT IN (SELECT book_id FROM book_tags WHERE tag_name LIKE 'url:%')""" ) data1 = cursor.fetchall() #for i in data: # query = """INSERT INTO tag_errors VALUES(%d, '%s', %d)""" % (i[0], i[1], 4)
def connect(self): self._conn = MySQLdb.connect(host=DBInfo.host, user=DBInfo.user, passwd=DBInfo.passwd, port=DBInfo.port, charset="utf8")
def saveResults(file, fits, var_names, step): f = open(file + ".asc", "w") which_solution = 0 import MySQLdb, sys, os, re db2 = MySQLdb.connect(db='subaru', user='******', passwd='darkmatter', host='ki-sr01') c = db2.cursor() for fit in fits: which_solution += 1 #if Numeric.sometrue(result[2]): import os, time user_name = os.environ['USER'] bonn_target = os.environ['BONN_TARGET'] bonn_filter = os.environ['BONN_FILTER'] time_now = time.asctime() user = user_name stringvars = { 'USER': user_name, 'BONN_TARGET': bonn_target, 'BONN_FILTER': bonn_filter, 'TIME': time_now, 'CHOICE': '', 'NUMBERVARS': 4 - which_solution, 'USER': user, 'step': step } for ele in var_names.keys(): stringvars[ele] = var_names[ele] #print stringvars from copy import copy floatvars = {} #copy array but exclude lists for ele in fit['class'].fitvars.keys(): if ele != 'condition' and ele != 'model_name' and ele != 'fixed_name': floatvars[ele] = fit['class'].fitvars[ele] elif ele == 'model_name' or ele == 'fixed_name': stringvars[ele] = fit['class'].fitvars[ele] # make database if it doesn't exist make_db = reduce(lambda x, y: x + ',' + y, [x + ' float(30)' for x in floatvars.keys()]) make_db += ',' + reduce( lambda x, y: x + ',' + y, [x + ' varchar(80)' for x in stringvars.keys()]) command = "CREATE TABLE IF NOT EXISTS photometry_db ( id MEDIUMINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id))" #print command #c.execute("DROP TABLE IF EXISTS photometry_db") #c.execute(command) for column in stringvars: try: command = 'ALTER TABLE photometry_db ADD ' + column + ' varchar(80)' c.execute(command) except: nope = 1 for column in floatvars: try: command = 'ALTER TABLE photometry_db ADD ' + column + ' float(30)' c.execute(command) except: nope = 1 # insert new observation names = reduce(lambda x, y: x + ',' + y, [x for x in floatvars.keys()]) values = reduce(lambda x, y: str(x) + ',' + str(y), [floatvars[x] for x in floatvars.keys()]) names += ',' + reduce(lambda x, y: x + ',' + y, [x for x in stringvars.keys()]) values += ',' + reduce( lambda x, y: x + ',' + y, ["'" + str(stringvars[x]) + "'" for x in stringvars.keys()]) command = "INSERT INTO photometry_db (" + names + ") VALUES (" + values + ")" printvals = {} for key in floatvars.keys(): printvals[key] = floatvars[key] for key in stringvars.keys(): printvals[key] = stringvars[key] print "##" + printvals['model_name'] + '##' keylist = printvals.keys() keylist.sort() for key in keylist: print key, ": ", printvals[key] print "######" #print command c.execute(command) #f.write("%s %s %s\n" % (result[0][0], result[0][1], result[0][2])) #f.write("%s %s %s\n" % (result[1][0], result[1][1], result[1][2])) #f.write("%s#ReducedChiSq\n" % (result[2])) #f.write("%s#MeanError\n" % (result[3])) #f.write("%s\n" % (id)) #else: # f.write("-1 -1 -1\n") # f.write("-1 -1 -1\n") # f.write("-1#ReducedChiSq\n") # f.write("-1#MeanError\n") # f.write("%s\n" % (id)) f.close return id
def mysql_conn(): try: conn = mysql.connect(host=db_host,user=db_user,passwd=db_pass,db=db_database,port=int(db_port)) return conn.cursor() except mysql.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
#!/usr/bin/env python3 # -*- coding: utf-8 -*- import MySQLdb import csv import os import sys import pandas as pd db = MySQLdb.connect(db='urp',host='192.168.5.213',user='******',passwd='eEtm9a3jHK9m6Umc',port=3306) cursor = db.cursor() print('Connection OK') if __name__ == "__main__": while True: try: input_csv_file = str(input("\033[0;34m%s\033[0m" % "Please input uid list csv file name: ")) print (input_csv_file) output_csv_file = str(input("\033[0;34m%s\033[0m" % "Please input uid list csv file name: ")) except ValueError: continue else: break output_csv_file = open(output_csv_file+".csv", 'w', newline='') writer = csv.writer(output_csv_file) writer.writerow(['customer_name', 'p2pserver_ip', 'vid', 'pid', 'gid', 'uid_count']) input_csv_data = pd.read_csv(input_csv_file+".csv") for index in range(len(input_csv_data['customer_name'])):
def connect(self): try: if self.engine == "sqlite": import sqlite3 self.db_types[DB.DATE] = 10 self.db_types[DB.TIMESTAMP] = -1 self.db_types[DB.INT] = -1 self.db_types[DB.LONG] = -1 self.db_types[DB.DECIMAL] = -1 self.db_types[DB.STRING] = -1 self.tag = "?" self.conn = sqlite3.connect(self.db) elif self.engine == "odbc": import pyodbc # $conn = odbc_connect("DRIVER={IBM i Access ODBC Driver 64-bit};System=my_iSeries", 'my_i_UID', 'my_i_PWD'); $a = 'Doe'; # pyodbc_driver_name = "{IBM i Access ODBC Driver 64-bit}" # pyodbc_driver_name = "{IBM DB2 ODBC DRIVER}" pyodbc_driver_name = "{iSeries Access ODBC Driver}" # self.db_types[DB.DATE] = 1082 # self.db_types[DB.TIMESTAMP] = -1114 if self.passwd is not None and len(self.passwd) > 2: self.conn = pyodbc.connect(driver=pyodbc_driver_name, system=self.host, database=self.db, uid=self.user, pwd=self.passwd) else: self.conn = pyodbc.connect(host=self.host, database=self.db, user=self.user) elif self.engine == "mysql": import MySQLdb self.db_types[DB.DATE] = MySQLdb.FIELD_TYPE.DATE """ self.db_types[DB.TIMESTAMP] = -1 self.db_types[DB.INT] = 3 self.db_types[DB.LONG] = 8 self.db_types[DB.DECIMAL] = 246 self.db_types[DB.STRING] = 253 """ self.db_types[DB.STRING] = MySQLdb.FIELD_TYPE.DATE # self.db_types[DB.DATE] = 10 self.db_types[DB.TIMESTAMP] = -1 self.db_types[DB.INT] = 3 self.db_types[DB.LONG] = 8 self.db_types[DB.DECIMAL] = 246 # self.db_types[DB.STRING] = 253 # self.db_types[DB.STRING] = 254 if self.passwd is not None and len(self.passwd) > 2: self.conn = MySQLdb.connect(host=self.host, db=self.db, user=self.user, passwd=self.passwd) else: self.conn = MySQLdb.connect(host=self.host, db=self.db, user=self.user) elif self.engine == "postgresql": import psycopg2 self.db_types[DB.DATE] = 1082 self.db_types[DB.TIMESTAMP] = 1114 self.db_types[DB.BOOL] = 16 self.db_types[DB.INT] = 23 self.db_types[DB.LONG] = -1 self.db_types[DB.DECIMAL] = 1700 self.db_types[DB.STRING] = 1043 if self.passwd is not None and len(self.passwd) > 2: self.conn = psycopg2.connect(host=self.host, database=self.db, user=self.user, password=self.passwd) else: self.conn = psycopg2.connect(host=self.host, database=self.db, user=self.user) elif self.engine == "oracle": import cx_Oracle port = 1521 self.db_types[DB.DATE] = 1082 self.db_types[DB.TIMESTAMP] = 1114 """ dsn_tns = cx_Oracle.makedsn(self.host, port, self.db) self.conn = cx_Oracle.connect(self.user, self.passwd, dsn_tns) """ url = "%s/%s@%s:%s/%s" % (self.user, self.passwd, self.host, port, self.db) self.conn = cx_Oracle.connect(url) elif self.engine == "mssql": import pymssql self.db_types[DB.DATE] = pymssql._mssql.SQLDATETIME self.db_types[DB.TIMESTAMP] = pymssql._mssql.SQLDATETIME self.db_types[DB.INT] = pymssql._mssql.SQLINT4 self.db_types[DB.LONG] = pymssql._mssql.SQLINT4 self.db_types[DB.DECIMAL] = pymssql._mssql.SQLDECIMAL self.db_types[DB.STRING] = pymssql._mssql.STRING if self.passwd is not None and len(self.passwd) > 2: self.conn = pymssql.connect(host=self.host, database=self.db, user=self.user, password=self.passwd) else: self.conn = pymssql.connect(host=self.host, database=self.db, user=self.user) if self.conn is None: raise Exception("connection is None") except Exception as err: logerr.log("connect error") logerr.log(err) sys.exit(0)
ncols=table.ncols print "cols number:",ncols #获取第一张工作表的第2行和第2列的值(数组) print "the value of the 2nd row:",table.row_values(2) print "the value of the 2nd col:",table.col_values(2) # 获取特定单元格的值 print "the value of the 1st row of the 1st col:",table.cell_value(2,0) print "the value of the 1st row of the 2st col:",table.cell_value(3,1) # 5 数据库读取 # MySQL数据库 import MySQLdb db=MySQLdb.connect("localhost","root","7125","company") #主机名 用户名 密码 数据库 #使用cursor()方法获取操作游标 cursor=db.cursor() # SQL查询语句 sql="select * from t_employee \ where empno=7369" try: #执行SQL语句 cursor.execute(sql) #获取所有记录的列表 results=cursor.fetchall() for row in results: empno=row[0] ename=row[1] job=row[2] mgr=row[3]
#-- automatically submit #-- nba adjustments #-- better formatting #-- backtesting with data from old games #-- what to do when player is two positions? cory harkey #-- add more projections (yahoo, nfl, fleaflicker?) #-- fix dst and K scoring #-- when do salaries update? do it earlier? auto adjust? #-- single factor for all? up and down scenario? #-- use player scoring stddev current_time = datetime.now() week = ceil((current_time - datetime.strptime('2013-09-03', '%Y-%m-%d')).days/7.0) gamedate = (current_time + timedelta((6 - current_time.weekday()) % 7)).date() mydb = MySQLdb.connect(host="localhost", user='******', db="dfs") mycursor = mydb.cursor() mycursor.execute('delete from optimization where week = %s and sport = "nfl"' % week) site_settings = { "draft-street": { "caps": [97159, 95569], "roster": { "qb": 2, "rb": 2, "wr": 2, "te": 1, "flex": 2, "dst": 0, "k": 0 },
# run sql from mysql to python using cursor #coding:utf-8 import MySQLdb conn = MySQLdb.Connect(host='127.0.0.1', port=3306, user='******', passwd='klxsxzsdf1', db='imooc', charset='utf8') cursor = conn.cursor() print conn print cursor sql = 'select * from user;' cursor.execute(sql) print cursor.rowcount rs = cursor.fetchone() print rs rs = cursor.fetchmany(3) print rs rs = cursor.fetchall() print rs cursor.close() conn.close()
#coding:utf-8 from flask import Flask, request, render_template, redirect import MySQLdb as mysql import json import time import traceback conn = mysql.connect(user='******', host='127.0.0.1', db='reboot10', charset='utf8') #conn=mysql.connect(host='192.168.9.10',port=3306,user='******',passwd='123.com',db='backstage',charset='utf8') conn.autocommit(True) cur = conn.cursor() app = Flask(__name__) @app.route('/register', methods=['GET', 'POST']) def register(): if request.method == 'POST': ''' data = {} data["name"] = request.form.get('name',None) data["name_cn"] = request.form.get('name_cn',None) data["mobile"] = request.form.get('mobile',None) data["email"] = request.form.get('email',None) data["role"] = request.form.get('role',None) data["status"] = request.form.get('status',None) data["password"] = request.form.get('password',None) data["repwd"] = request.form.get('repwd',None)
#!/usr/bin/env python # --*-- coding:utf-8 --*-- import MySQLdb #操作mysql,需要加载MySQLdb模块 #创建连接 conn = MySQLdb.connect(host = '127.0.0.1',user = '******',passwd = '123',db = 'mydb') #使用connect方法对数据库进行连接,相当于一个门 cur = conn.cursor() #使用conn.cursor方法,相当于操作的一双手 #操作数据库 reCount = cur.execute('select * from students') #可以看到主函数的操作是查看students表 table = cur.fetchall() #将操作所得到的数据全部拿出来 # #关闭连接 cur.close() #结束操作后,将手拿回来 conn.close() #将门关上 print reCount #cur.execute返回的是操作影响的行数 print data