def get_login_by_id(self, table, data):
"""Select user or admin login information based on id
Arguments:
self
table: string. 'users' or 'admin'
data: dictionary. Contains one of the following keys:
data['user_id']: int
data['admin_id]: int
Returns: Parsed dictionary result of select query in the form of { 'password': '******' }
"""
# Create query based on table
if table == 'users':
query = sqlalchemy.text(
'select password from users where user_id = :id;')
key = 'user_id'
elif table == 'admins':
query = sqlalchemy.text(
'select password from admins where admin_id = :id;')
key = 'admin_id'
logging.debug('QueryTool.get_login_by_id(): query is {}'.format(
str(query)))
# Execute query & return parsed dictionary result
result = self.connxn.execute(query, id=data[key])
return self.build_json_select(result, key, False, True)
def get_by_id(self, table, data):
"""Select users, admins, or awards information (excluding login information) based on id
Arguments:
self
table: string. 'users', 'admins', or 'awards'
data: dictionary. Contains one of the following keys:
data['user_id']: int
data['admin_id']: int
data['award_id']: int
Returns: Parsed dictionary result of select query in the format { 'column': 'value', 'column': 'value', 'column': 'value' }
"""
# Create SELECT query based on table
if table == 'users':
query = sqlalchemy.text(
'select user_id, first_name, last_name, email_address, created_timestamp, signature_path from users where user_id = :id;'
)
key = 'user_id'
elif table == 'admins':
query = sqlalchemy.text(
'select admin_id, first_name, last_name, email_address, created_timestamp from admins where admin_id = :id;'
)
key = 'admin_id'
elif table == 'awards':
query = sqlalchemy.text(
'select * from awards where award_id = :id;')
key = 'award_id'
logging.debug('QueryTool.get_by_id(): query is {}'.format(str(query)))
# Execute query & return parsed dictionary result
result = self.connxn.execute(query, id=data[key])
return self.build_json_select(result, key, False, True)
def get(self, table):
"""Select all users, admins, or awards information (excluding login information)
Arguments:
self
table: string. 'users', 'admins', or 'awards'
Returns: Parsed dictionary result of select query in the format { 'key': [ ] }
"""
# Create SELECT query based on table
if table == 'users':
query = sqlalchemy.text(
'select user_id, first_name, last_name, created_timestamp, email_address, signature_path from users;'
)
key = 'user_id'
elif table == 'admins':
query = sqlalchemy.text(
'select admin_id, first_name, last_name, created_timestamp, email_address from admins;'
)
key = 'admin_id'
elif table == 'awards':
query = sqlalchemy.text('select * from awards;')
key = 'award_id'
logging.debug('QueryTool.get(): query is {}'.format(str(query)))
# Execute SELECT query & return parsed dictionary result
result = self.connxn.execute(query)
return self.build_json_select(result, key, True, False)
def delete_by_id(self, table, data):
"""Delete from users, admins, or awards table based on user_id
Arguments:
self
table: string. 'users', 'admins', or 'awards'
data: dictionary. Contains one of the following keys
data['user_id']: int
data['admin_id]: int
data['award_id]: int
Returns: Parsed dictionary result of delete query in the format { 'key': int(id) }
"""
# Create query based on table
key = None
if table == 'users':
query = sqlalchemy.text('delete from users where user_id = :id;')
key = 'user_id'
elif table == 'admins':
query = sqlalchemy.text('delete from admins where admin_id = :id;')
key = 'admin_id'
elif table == 'awards':
query = sqlalchemy.text('delete from awards where award_id = :id;')
key = 'award_id'
logging.debug('QueryTool.delete_by_id(): query is {}'.format(
str(query)))
# Execute query and return parsed dictionary result
result = self.connxn.execute(query, id=int(data[key]))
return self.build_json_delete(key, result)
# References:
# [1] https://cloud.google.com/sql/docs/mysql/connect-app-engine re: create_engine()
# [2] https://flask-sqlalchemy.palletsprojects.com/en/2.x/
# [3] https://cloud.google.com/sql/docs/mysql/manage-connections re: sanitizing inputs with execute(), connection variables
# [4] https://docs.sqlalchemy.org/en/13/core/connections.html re: execute(), close(), parsing result variable
# [5] https://www.tutorialspoint.com/sql/sql-update-query.htm re: update sql
# [6] https://www.tutorialspoint.com/sql/sql-insert-query.htm re: insert sql
# [7] https://docs.python-guide.org/scenarios/json/ re: building json strings
# [8] https://github.com/GoogleCloudPlatform/python-docs-samples/blob/master/cloud-sql/mysql/sqlalchemy/main.py re: how to import sqlalchemy, string URL for connection
# [9] https://stackoverflow.com/questions/27766794/switching-from-sqlite-to-mysql-with-flask-sqlalchemy
# [10] https://docs.sqlalchemy.org/en/13/core/pooling.html#pool-disconnects re: engine.dispose()
# [11] https://cloud.google.com/sql/docs/mysql/connect-external-app#python re: connection via sqlalchemy & tcp to localhost
# [12] https://kite.com/python/docs/sqlalchemy.engine.result.ResultProxy re: parsing result
# [13] https://stackoverflow.com/questions/40854861/typeerror-instancemethod-object-is-not-iterable-python re: use of .keys() rather than .keys for iteration
# [14] https://docs.python.org/2/library/json.html re: use of json.dumps <--> python dict
# [15] https://docs.python.org/2/tutorial/datastructures.html re: use of append
# [16] https://stackoverflow.com/questions/19288842/programmingerror-1064-you-have-an-error-in-your-sql-syntax-check-the-manual re: use of single quotations
# [17] https://kite.com/python/docs/sqlalchemy.text re: sanitizing input, use of text()
# [18] https://kite.com/python/docs/sqlalchemy.engine.result.RowProxy re: parsing RowProxy
# [19] https://docs.sqlalchemy.org/en/13/core/tutorial.html re: how to write textual SQL
def put_login_by_id(self, table, data):
"""Update users or admins password based on id
Arguments:
self
table: string. 'users' or 'admins'
data: dictionary. Containing query information based on the table:
users
data['user_id']
data['password']
admins
data['admin_id']
data['password']
Returns: Parsed dictionary result of update query in format { 'key': int(id) }
"""
# Create UPDATE and SELECT query based on table, execute and return parsed dictionary result
# SELECT query effectively verifies that the entry still exists in the table, instead of relying on
# request data to make the response
if table == 'users':
query = sqlalchemy.text(
'update users set password = :password where user_id = :user_id;'
)
result = self.connxn.execute(query,
password=data['password'],
user_id=int(data['user_id']))
verify_query = sqlalchemy.text(
'select user_id from users where user_id = :id and password = :password;'
)
key = 'user_id'
elif table == 'admins':
query = sqlalchemy.text(
'update admins set password = :password where admin_id = :admin_id;'
)
result = self.connxn.execute(query,
password=data['password'],
admin_id=int(data['admin_id']))
verify_query = sqlalchemy.text(
'select admin_id from admins where admin_id = :id and password = :password;'
)
key = 'admin_id'
logging.debug('QueryTool.put(): update query is {}'.format(str(query)))
logging.debug('QueryTool.put(): select query is {}'.format(
str(verify_query)))
result = self.connxn.execute(verify_query,
id=int(data[key]),
password=data['password'])
return self.build_json_select(result, key, False, True)
def get_awards_by_filter(self, filter, data, between=False):
"""Select awards information based on filter
Arguments:
self
filter: string. 'authorizing_user_id', 'receiving_user_id', 'type', 'awarded_datetime', or 'distributed'
between: bool. Indication of if awarded_datetime is treated as a range or not.
data: dictionary. Contains one key matching filter.
data['authorizing_user_id']: int
data['receiving_user_id]: int
data['type']: string
if between == True:
data['awarded_datetime']['lesser']: string
data['awarded_datetime]['greater']: string
else:
data['awarded_datetime']: string
data['distributed']: string
Returns: Parsed dictionary result of select query in the form of { 'award_ids': [] }
"""
# Create & execute SELECT query, return parsed dictionary result
if filter == 'authorizing_user_id':
query = sqlalchemy.text(
'select * from awards where authorizing_user_id = :key;')
result = self.connxn.execute(query, key=int(data[filter]))
elif filter == 'receiving_user_id':
query = sqlalchemy.text(
'select * from awards where receiving_user_id = :key;')
result = self.connxn.execute(query, key=int(data[filter]))
elif filter == 'type':
query = sqlalchemy.text('select * from awards where type = :key;')
result = self.connxn.execute(query, key=data[filter])
elif filter == 'awarded_datetime' and between == False:
query = sqlalchemy.text(
'select * from awards where awarded_datetime >= :key;')
result = self.connxn.execute(query, key=data[filter])
elif filter == 'awarded_datetime' and between == True:
# This is only used internally to determine if too many awards for a given week/month
query = sqlalchemy.text(
'select * from awards where type = :key3 and awarded_datetime >= :key1 and awarded_datetime < :key2;'
)
result = self.connxn.execute(query,
key1=data[filter]['greater'],
key2=data[filter]['lesser'],
key3=data['type'])
elif filter == 'distributed':
query = sqlalchemy.text(
'select * from awards where distributed = :key;')
result = self.connxn.execute(query, key=bool(data[filter]))
logging.debug('QueryTool.get_awards_by_filter(): query is {}'.format(
str(query)))
return self.build_json_select(result, 'award_id', True, False)
class User(db.Model):
"""用户基础表"""
__tablename__ = 't_user'
__table_args__ = ({'comment': '用户基础表'})
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
email = db.Column(db.String(50), unique=True, index=True)
username = db.Column(db.String(50))
password = db.Column(db.String(200))
nickname = db.Column(db.String(100))
department = db.Column(db.String(50))
avatar = db.Column(db.String(256))
phone = db.Column(db.String(11))
status = db.Column(db.String(32))
last_login_ip = db.Column(db.String(20))
last_login_time = db.Column(db.DATETIME())
created_time = db.Column(db.DATETIME(),
server_default=sqlalchemy.sql.func.now())
updated_time = db.Column(
db.DATETIME(),
server_default=sqlalchemy.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
class Pet(db.Model):
"""Pet."""
__tablename__ = "pets"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.Text, nullable=False)
species = db.Column(db.Text, nullable=False)
photo_url = db.Column(db.Text)
photo_upload = db.Column(db.Text)
age = db.Column(db.Integer)
notes = db.Column(db.Text)
available = db.Column(db.Boolean, server_default=sqlalchemy.text("True"))
def get_photo_url(self):
"""return the photo url of the pet"""
if self.photo_url:
return self.photo_url
if self.photo_upload:
return os.path.join("/static/upload/", self.photo_upload)
return None
def put_by_id(self, table, data):
"""Update users or admins table based on id
Arguments:
self
table: string. 'users' or 'admins'
data: dictionary. Containing query information based on the table:
users
data['user_id']: int
data['first_name']: string
data['last_name']: string
data['email_address']: string
data['signature_path']: string
admins
data['admin_id']: int
data['first_name']: string
data['last_name']: string
data['email_address']: string
awards
data['award_id']: int
Returns: Parsed dictionary result of update query in format { 'key': int(id) }
"""
# Create UPDATE and SELECT query based on table, execute and return parsed dictionary result
# SELECT query effectively verifies that the entry still exists in the table, instead of relying on
# request data to make the response
if table == 'users':
query = sqlalchemy.text(
'update users set first_name = :first_name, last_name = :last_name, email_address = :email_address, signature_path = :signature_path where user_id = :user_id;'
)
result = self.connxn.execute(query,
first_name=data['first_name'],
last_name=data['last_name'],
email_address=data['email_address'],
signature_path=data['signature_path'],
user_id=int(data['user_id']))
verify_query = sqlalchemy.text(
'select user_id from users where user_id = :id;')
key = 'user_id'
elif table == 'admins':
query = sqlalchemy.text(
'update admins set first_name = :first_name, last_name = :last_name, email_address = :email_address where admin_id = :admin_id;'
)
result = self.connxn.execute(query,
first_name=data['first_name'],
last_name=data['last_name'],
email_address=data['email_address'],
admin_id=int(data['admin_id']))
verify_query = sqlalchemy.text(
'select admin_id from admins where admin_id = :id;')
key = 'admin_id'
elif table == 'awards':
query = sqlalchemy.text(
'update awards set distributed = true where award_id = :award_id;'
)
result = self.connxn.execute(query, award_id=int(data['award_id']))
verify_query = sqlalchemy.text(
'select award_id from awards where award_id = :id;')
key = 'award_id'
logging.debug('QueryTool.put(): update query is {}'.format(str(query)))
logging.debug('QueryTool.put(): select query is {}'.format(
str(verify_query)))
result = self.connxn.execute(verify_query, id=int(data[key]))
return self.build_json_select(result, key, False, True)
def post(self, table, data):
"""Insert into users, admins, or awards table
Arguments:
self
table: string. 'users', 'admins' or 'awards'
data: dictionary. Containing query information based on the table:
users
data['first_name']: string
data['last_name']: string
data['password']: string
data['email_address']: string
data['created_timestamp']: string
data['signature_path']: string
admins
data['first_name']: string
data['last_name']: string
data['password']: string
data['email_address']: string
data['created_timestamp']: string
awards
data['authorizing_user_id']: int
data['receiving_user_id']: int
data['type']: string
data['distributed']: string
data['awarded_datetime']: string
Returns: Parsed dictionary result of insertion query in format { 'key': int(id) }
"""
# Create INSERT query based on table, execute & return parsed response
if table == 'users':
query = sqlalchemy.text(
'insert into users (first_name, last_name, email_address, password, created_timestamp, signature_path) values (:first_name, :last_name, :email_address, :password, :created_timestamp, :signature_path);'
)
result = self.connxn.execute(
query,
first_name=data['first_name'],
last_name=data['last_name'],
email_address=data['email_address'],
password=data['password'],
created_timestamp=data['created_timestamp'],
signature_path=data['signature_path'])
key = 'user_id'
elif table == 'admins':
query = sqlalchemy.text(
'insert into admins (first_name, last_name, email_address, password, created_timestamp) values (:first_name, :last_name, :email_address, :password, :created_timestamp);'
)
result = self.connxn.execute(
query,
first_name=data['first_name'],
last_name=data['last_name'],
email_address=data['email_address'],
password=data['password'],
created_timestamp=data['created_timestamp'])
key = 'admin_id'
elif table == 'awards':
query = sqlalchemy.text(
'insert into awards (authorizing_user_id, receiving_user_id, type, distributed, awarded_datetime) values (:authorizing_user_id, :receiving_user_id, :type, :distributed, :awarded_datetime);'
)
result = self.connxn.execute(
query,
authorizing_user_id=data['authorizing_user_id'],
receiving_user_id=data['receiving_user_id'],
distributed=data['distributed'],
awarded_datetime=data['awarded_datetime'],
type=data['type'])
key = 'award_id'
logging.debug('QueryTool.post(): query is {}'.format(str(query)))
return self.build_json_insert(key, result)
from flask import Flask
from flask_sqlalchemy import sqlalchemy
import os
from models import db, Scrobbles
app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
pg_username = os.environ['POSTGRES_USER']
pg_password = os.environ['POSTGRES_PASSWORD']
pg_db = os.environ['POSTGRES_DB']
database_uri = 'postgresql://%s:%s@db/%s' % (pg_username, pg_password, pg_db)
app.config['SQLALCHEMY_DATABASE_URI'] = database_uri
db.init_app(app)
from flaskfm import views
with app.app_context():
db.create_all()
if Scrobbles.query.first() is None:
print('Importing sample data')
s = sqlalchemy.text("""SELECT import_sample_data();""")
db.session.execute(s)
db.session.commit()