def cursor(cls):
if cls._cursor:
return cls._cursor
mysql = MySQL()
mysql.init_app(cls._app)
cls._cursor = mysql.get_db().cursor()
return cls._cursor
class DB(object):
"""Initialize mysql database """
host = "localhost"
user = "******"
password = ""
db = "lms"
table = ""
def __init__(self, app):
app.config["MYSQL_DATABASE_HOST"] = self.host
app.config["MYSQL_DATABASE_USER"] = self.user
app.config["MYSQL_DATABASE_PASSWORD"] = self.password
app.config["MYSQL_DATABASE_DB"] = self.db
self.mysql = MySQL(app, cursorclass=DictCursor)
def cur(self):
return self.mysql.get_db().cursor()
def query(self, q):
h = self.cur()
if (len(self.table) > 0):
q = q.replace("@table", self.table)
h.execute(q)
return h
def commit(self):
self.query("COMMIT;")
class baseModel(object):
__mysql = None
def __init__(self, app):
self.__mysql = MySQL( cursorclass = pymysql.cursors.DictCursor )
self.__mysql.init_app(app)
self.__mysql.connect()
def dbCoursor(self):
return self.__mysql.get_db().cursor()
class SQL:
get_base_rate = "select BaseRate from PersonalPropertyBaseRate where Territory='{0}' and ConstructionType='{1}'"
get_personal_property_factor = "select Factor from PersonalPropertyLimitFactor where LimitRange <= {0} ORDER BY LimitRange DESC LIMIT 1"
get_personal_property_deductible = "select Factor from DeductibleFactor where Deductible='{0}'"
get_liability_base_rate = "select BaseRate from LiabilityBaseRate where CustomerAgeRange <= {0} ORDER BY CustomerAgeRange DESC LIMIT 1"
get_liability_limit_factor = "select Factor from LiabilityLimitFactor where LiabilityLimit='{0}'"
get_policy_plan_factor = "select PolicyPlanFactor from PolicyPlan where PolicyPlan='{0}'"
get_quote = "select * from QuoteHistory where Id={0}"
get_all_quotes = "select * from QuoteHistory"
get_last_inserted_id = "select last_insert_id()"
insert_quote = str(
"insert into QuoteHistory(InsuranceType,PolicyStartDate,FirstName,LastName,EmailAddress,PhoneNumber,AddressLine1,AddressLine2,City,State,Zip,Plan,PPCoveragevalue,BuildingType,Deductible,LCoverageValue,Age,PersonalPremium,LiabilityPremium,TotalPremium) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}')"
)
def __init__(self, app):
self.mysql = MySQL()
self.mysql.init_app(app)
def initialize_connection(self):
self.cur = self.mysql.get_db().cursor()
def fetch_one_from_db(self, sql):
self.cur.execute(sql)
return self.cur.fetchone()
def fetch_one_as_json(self, sql):
rv = self.fetch_all_from_db(sql)
row_headers = [x[0] for x in self.cur.description]
json_data = []
for result in rv:
json_data.append(dict(zip(row_headers, result)))
return json_data[0]
def fetch_all_as_json(self, sql):
rv = self.fetch_all_from_db(sql)
row_headers = [x[0] for x in self.cur.description]
json_data = []
for result in rv:
json_data.append(dict(zip(row_headers, result)))
return json_data
def fetch_all_from_db(self, sql):
self.cur.execute(sql)
return self.cur.fetchall()
def execute(self, sql):
self.cur.execute(sql)
def commit(self):
self.cur.connection.commit()
def close(self):
self.cur.connection.close()
def get_db_connection(app):
mysql = MySQL()
# mySql Configurations
app.config['MYSQL_DATABASE_USER'] = '******'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'fashion_lens'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)
cur = mysql.get_db().cursor()
return cur
from flask import Flask
from flaskext.mysql import MySQL
mysql = MySQL()
print('Successful connection')
cursor = mysql.get_db().cursor()
def executeQuery(sqlQuery):
cursor.execute(sqlQuery)
return cursor.fetchall()
def init_db():
mysql = MySQL()
db = mysql.get_db()
with app.open_resource('schema.sql', mode='r') as f:
db.cursor().executescript(f.read())
db.commit()
from flaskext.mysql import MySQL
#init Mysql
mysql = MySQL()
app = Flask(__name__, static_url_path='/public')
app.config['MYSQL_DATABASE_USER'] = '******'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'thaothui'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)
#create cursor
#cur = mysql.connect().cursor()
cur = mysql.get_db().cursor()
#execute query
cur.execute("INSERT INTO users(name, email, username, password) VALUES(%s, %s, %s, %s);", (name, email, username, password))
#commit to db
mysql.get_db().commit()
#close db
cur.close()
from site_settings import settings
from flask import Flask
from flaskext.mysql import MySQL
from pymysql import cursors
app = Flask(__name__.split(".")[0])
app.config.update(settings)
print(app.config)
mysql = MySQL(cursorclass=cursors.DictCursor)
mysql.init_app(app)
db = mysql.get_db()
print(db)
# # check if structure is present
# result = cur.execute("SHOW TABLES")
# if result > 0:
# tables = cur.fetchall()
# print(tables)
# else:
# print("Nothing found?")
# if __name__ == "__main__":
# app.run(debug=True)
def list_accounts(self, mysql: MySQL):
cur = mysql.get_db().cursor()
cur.execute("SELECT userid, name,password,role,loginid FROM user")
result = cur.fetchall()
return self.array_to_account(result)
