def login(): """登录验证用户名密码正确性,如正确返回token""" _db = db_connection.Connection(host="localhost", user="******", passwd="root123", db="apitest", charset="utf8") j_data = json.loads(request.get_data()) print(j_data) try: username = j_data['username'] password = j_data['password'] except: return jsonify(code=1001, msg="参数缺失") sql = "SELECT * FROM users WHERE name = '%s' AND password = '******'" % ( username, password) print(sql) cursor = _db.cursor() rows = cursor.execute(sql) if rows: token = create_token(username, password) return jsonify(code=4, msg="登录成功,欢迎您!", token=token.decode()) else: return jsonify(code=2, msg="非法用户") cursor.close()
def showaddemp(): db_conn = connections.Connection(host=customhost, port=3306, user=customuser, password=custompass, db=customdb) return render_template('AddEmp.html')
def load_to_table(self): song_df = self.get_spotify_songs() engine = sqlalchemy.create_engine( "mysql+pymysql://{}:{}@{}:3306/recent_played_songs".format( custom_user, custom_pass, custom_host)) conn = connections.Connection(host=custom_host, port=3306, user=custom_user, password=custom_pass, db=custom_db) cursor = conn.cursor() sql_query = "CREATE TABLE IF NOT EXISTS {} (song_name VARCHAR(200), artist_name VARCHAR(200), played_at VARCHAR(200), date VARCHAR(200))".format( self.table_name) cursor.execute(sql_query) print("Opened database sucessfully") try: song_df.to_sql(name=self.table_name, con=engine, index=False, if_exists="replace") except Exception as e: print(e) conn.close() print("Close database successfully")
def __init__(self, logger, dbname=None, user=None, password=None, host=None, port=None): """ Set up database connection """ dbname = config.DB_NAME if not dbname else dbname user = config.DB_USER if not user else user password = config.DB_PASSWORD if not password else password host = config.DB_HOST if not host else host port = config.DB_PORT if not port else port self.connection = mysqlconnections.Connection(database=dbname, user=user, password=password, host=host, port=port) self.cursor = self.connection.cursor(pymysql.cursors.DictCursor) self.log = logger if self.log is None: raise NotImplementedError
def AddEmp(): db_conn = connections.Connection(host=customhost, port=3306, user=customuser, password=custompass, db=customdb) fmno = request.form['fmno'] name = request.form['name'] team = request.form['team'] location = request.form['location'] certification = request.form['certification'] # emp_image_file = request.files['emp_image_file'] insert_sql = "INSERT INTO employee VALUES (%s, %s, %s, %s, %s)" cursor = db_conn.cursor() # if emp_image_file.filename == "": # return "Please select a file" try: cursor.execute(insert_sql, (fmno, name, team, location, certification)) db_conn.commit() # # Uplaod image file in S3 # # emp_image_file_name_in_s3 = "emp-id-" + str(emp_id) + "_image_file" # s3 = boto3.resource('s3') # try: # print("Data inserted in MySQL RDS... uploading image to S3...") # s3.Bucket(custombucket).put_object(Key=emp_image_file_name_in_s3, Body=emp_image_file) # bucket_location = boto3.client('s3').get_bucket_location(Bucket=custombucket) # s3_location = (bucket_location['LocationConstraint']) # # if s3_location is None: # s3_location = '' # else: # s3_location = '-' + s3_location # # object_url = "https://s3{0}.amazonaws.com/{1}/{2}".format( # s3_location, # custombucket, # emp_image_file_name_in_s3) # # except Exception as e: # return str(e) except Exception as e: print(e) logging.info(msg=e) finally: print("finally") cursor.execute("select * from employee") data = cursor.fetchall() # data from database return render_template('ViewSummary.html', value=data)
def showsummary(): db_conn = connections.Connection(host=customhost, port=3306, user=customuser, password=custompass, db=customdb) cursor = db_conn.cursor() cursor.execute("select * from employee") data = cursor.fetchall() # data from database return render_template('ViewSummary.html', value=data)
def news(): _db = db_connection.Connection(host="localhost", user="******", passwd="root123", db="apitest", charset="utf8") #j_data = json.loads(request.get_data()) newsid = request.args.get("newsid") sql = "SELECT * FROM news WHERE id = %s" % (newsid) cursor = _db.cursor() rows = cursor.execute(sql) data = cursor.fetchall() print(rows) if rows: return jsonify(code=5, msg="查询成功", sql=sql, count=rows, data=data) else: return jsonify(code=6, msg="未查询到数据", sql=sql) _cursor.close()
def listen_times(table_name): conn = connections.Connection(host=custom_host, port=3306, user=custom_user, password=custom_pass, db=custom_db) cursor = conn.cursor() sql_query = "SELECT song_name, played_at, COUNT(*) AS 'occurence' FROM {} GROUP BY song_name ORDER BY 'occurence' DESC".format( table_name) cursor.execute(sql_query) print("Getting listening times for {}".format(table_name)) results = pd.read_sql_query(sql_query, conn) times = results["played_at"] conn.close() print("Close database successfully") return (times)
def topartists(table_name): conn = connections.Connection(host=custom_host, port=3306, user=custom_user, password=custom_pass, db=custom_db) cursor = conn.cursor() sql_query = "SELECT artist_name, COUNT(*) AS 'occurence' FROM {} GROUP BY artist_name ORDER BY occurence DESC LIMIT 5;".format( table_name) cursor.execute(sql_query) print("Results fetched from table {}".format(table_name)) results = pd.read_sql_query(sql_query, conn) conn.close() print("Close database successfully") return (results)
def recentsongs(table_name): engine = sqlalchemy.create_engine("mysql+pymysql://{}:{}@{}:3306/recent_played_songs".format(custom_user, custom_pass, custom_host)) conn = connections.Connection( host = custom_host, port = 3306, user = custom_user, password = custom_pass, db = custom_db ) cursor = conn.cursor() sql_query = "SELECT * FROM {}".format(table_name) cursor.execute(sql_query) print("Results fetched from table {}".format(table_name)) results = pd.read_sql_query(sql_query, conn) conn.close() print("Close database successfully") return(results)
def __init__(self, pool, **kwargs): self._closed = False self._pool = pool self.result = None self.proto = connections.Connection(**kwargs) self.restore()
from flask import render_template, request from pymysql import connections import os app = Flask(__name__) DBHOST = os.environ.get("DBHOST") DBPORT = os.environ.get("DBPORT") DBPORT = int(DBPORT) DBUSER = os.environ.get("DBUSER") DBPWD = os.environ.get("DBPWD") DATABASE = os.environ.get("DATABASE") db_conn = connections.Connection(host=DBHOST, port=DBPORT, user=DBUSER, password=DBPWD, db=DATABASE) output = {} @app.route("/", methods=['GET', 'POST']) def home(): return render_template('AddEmp.html') @app.route("/AddEmp", methods=['POST']) def updatedatabase(): emp_id = request.form['emp_id'] first_name = request.form['first_name'] last_name = request.form['last_name']
from flask import Flask, render_template, request from pymysql import connections import os import boto3 from config import * app = Flask(__name__) bucket = custombucket region = customregion db_conn = connections.Connection( host="sm1ajhg6j0477i5.cmuq4uiatswr.ap-southeast-1.rds.amazonaws.com", port=3306, user="******", password="******", db="myapp") output = {} table = 'employee' @app.route("/", methods=['GET', 'POST']) def home(): return render_template('AddEmp.html') @app.route("/about", methods=['GET', 'POST']) def about(): return render_template('about.html')
# -*- coding: utf-8 -*- import sys from pymysql import connections from datetime import datetime time = datetime.now() try: conn = connections.Connection( host='139.129.95.148', port=3306, user='******', password='******', db='test', charset='utf8') except Exception as e: print(e) sys.exit() cursor = conn.cursor() # 插入 def insert(number): sql_insert = """insert into \ everyone1606(id, day_id, spend, mark, form, other) \ values(%s, %s, %s, '%s', '%s', '%s')""" % number # tuple(number) cursor.execute(sql_insert) conn.commit() # 保存 # 删除 def delete(number): sql_delete = "delete from everyone1606 where id=%s" % number
from flask import Flask, render_template, request from pymysql import connections import os import boto3 from config import * app = Flask(__name__) bucket = databashe-1 region = us-east-2 db_conn = connections.Connection( host=,database-1.cacsabkcjmvr.us-east-2.rds.amazonaws.com, user=admin, password=prasad123, db=database-1 ) output = {} table = 'employee' @app.route("/", methods=['GET', 'POST']) def home(): return render_template('AddEmp.html') @app.route("/about", methods=['POST']) def about(): return render_template('www.intellipaat.com')
import numpy as np from numpy import dot from numpy.linalg import norm # Aurora serverless database initialisation from pymysql import connections DBHOST = "" DBPORT = int(3306) DBUSER = "******" DBPASSWORD = "" DATABASE = "wordvectors" dbcon = connections.Connection(host = DBHOST, port=DBPORT,user=DBUSER,password=DBPASSWORD,db=DATABASE) cursor = dbcon.cursor() def get_index_and_vector(word): sql_getcluster_index_and_wordvector = 'SELECT cluster_id,vector FROM wordvectors WHERE word = (%s)' cursor.execute(sql_getcluster_index_and_wordvector, word) # Fetch all the rows in a list of lists. cluster_index, wordvector = cursor.fetchall()[0] wordvector = wordvector.replace("[","").replace("]","") wordvector = wordvector.strip().split() wordvector = np.asarray(wordvector, dtype='float32') return cluster_index, wordvector def retrieve_similar(clusterid,originalwordvec,q_word): sql_get_similar_words = 'SELECT word,vector FROM wordvectors WHERE cluster_id = (%s) AND word!=(%s) ' cursor.execute(sql_get_similar_words, (clusterid,q_word)) all_vals = cursor.fetchall() word_similarity_index=[] for word, wordvector in all_vals:
app = Flask(__name__) # DBHOST = os.environ.get("DBHOST") # DBPORT = os.environ.get("DBPORT") # DBPORT = int(DBPORT) # DBUSER = os.environ.get("DBUSER") # DBPWD = os.environ.get("DBPWD") # DATABASE = os.environ.get("DATABASE") bucket= custombucket region= customregion db_conn = connections.Connection( host= customhost, port=3306, user= customuser, password= custompass, db= customdb ) output = {} @app.route("/", methods=['GET', 'POST']) def home(): return render_template('AddEmp.html') @app.route("/about", methods=['POST']) def about(): return render_template('www.intellipaat.com'); @app.route("/addemp", methods=['POST']) def AddEmp():
def __init__(self, pool, **kwargs): self._pool = pool self._closed = False self.proto = connections.Connection(**kwargs) self.proto.autocommit(True)
import os # sys.path.insert(1,os.path.join(os.path.abspath('.'),'venv/Lib/site-packages')) from flask import Flask, jsonify, request from pymysql import connections, ProgrammingError, DatabaseError, MySQLError, DataError from os import environ DB_HOST = environ.get("DB_HOST") USER = environ.get("USER") PWD = environ.get("PASSWORD") DATABASE = environ.get("DATABASE") app = Flask(__name__) dbconn = connections.Connection(host=DB_HOST, user=USER, password=PWD, db=DATABASE) @app.route('/addResource', methods=['POST']) def addresource(): data = request.get_json() id = data['id'] firstName = data['firstName'] middleName = data['middleName'] lastName = data['lastName'] listOfTechWorkedOn = data['listOfTechWorkedOn'] certifications = data['certifications'] projects = data['projects']
from flask import Flask from flask import render_template from flask import request import boto3 from pymysql import connections import urllib.request from config import * app = Flask(__name__) db_conn = connections.Connection( host=databasehost, port=3306, user=duser, password=dpass, db=s3database ) @app.route("/", methods=['GET', 'POST']) def hello_world(): if request.method == 'POST': s3 = boto3.resource('s3') file_body = request.files['file_name'] count_obj = 0 for i in s3.Bucket(custombucket).objects.all(): count_obj = count_obj + 1 file_name = "file-id-" + str(count_obj + 1) try: s3.Bucket(custombucket).put_object(Key=file_name, Body=file_body) bucket_location = boto3.client('s3').get_bucket_location(Bucket=custombucket)