def run(self):
while True:
user_id = self.q.get()
with get_connection(self.pool) as conn:
conn.set_isolation_level(1) # READ_COMMITTED
try:
with conn.cursor(cursor_factory=psycopg2.extras.
RealDictCursor) as cursor:
cursor.execute(
"SELECT * FROM coupons WHERE code = %s FOR UPDATE",
(self.code, ))
coupon_row = cursor.fetchone()
new_amount = coupon_row['amount'] - 1
if new_amount < 0:
raise AssertionError(
"coupons should not go negative")
coupon_id = coupon_row['id']
cursor.execute(
"UPDATE coupons SET amount = %s WHERE id = %s",
(new_amount, coupon_id))
cursor.execute(
"INSERT INTO user_coupon_usage VALUES (%s, %s)",
(coupon_id, user_id))
conn.commit()
except:
conn.rollback()
self.q.task_done()
def all_user_data():
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(
'SELECT id, name, role, email, hashed_password FROM users ORDER BY name',
[])
yield from cur
def add_clinic(clinic):
update_language_string(clinic.name)
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(
'INSERT INTO clinics (id, name, edited_at) VALUES (%s, %s, %s)',
[clinic.id, to_id(clinic.name), clinic.edited_at])
def create_token(user_id):
with get_connection() as conn:
with conn.cursor() as cur:
token = str(uuid.uuid4())
cur.execute('INSERT INTO tokens (user_id, token) VALUES (%s, %s)',
[user_id, token])
return token
def search_patients(given_name: str, surname: str, country: str,
hometown: str):
where_clauses = []
params = []
if given_name is not None:
where_clauses.append("UPPER(get_string(given_name, 'sp')) LIKE %s")
params.append(f'%{given_name.upper()}%')
if surname is not None:
where_clauses.append("UPPER(get_string(surname, 'sp')) LIKE %s")
params.append(f'%{surname.upper()}%')
if country is not None:
where_clauses.append("UPPER(get_string(country, 'sp')) LIKE %s")
params.append(f'%{country.upper()}%')
if hometown is not None:
where_clauses.append("UPPER(get_string(hometown, 'sp')) LIKE %s")
params.append(f'%{hometown.upper()}%')
where_clause = ' AND '.join(where_clauses)
query = f"SELECT id, given_name, surname, date_of_birth, sex, country, hometown, phone, edited_at FROM patients WHERE {where_clause};"
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(query, params)
yield from cur
def photo_filename_by_patient(patient_id):
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute('SELECT filename FROM photos WHERE patient_id = %s',
[patient_id])
result = cur.fetchone()
return result[0] if result else None
def update_password(user_id, new_password):
with get_connection() as conn:
with conn.cursor() as cur:
new_password_hashed = bcrypt.hashpw(new_password.encode(),
bcrypt.gensalt()).decode()
cur.execute('UPDATE users SET hashed_password = %s WHERE id = %s',
[new_password_hashed, user_id])
def set_patient_filename(patient_id, base_filename):
query = '''
INSERT INTO photos (patient_id, filename) VALUES (%s, %s)
ON CONFLICT (patient_id) DO UPDATE SET filename=EXCLUDED.filename
'''
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(query, [patient_id, base_filename])
def user_name_by_id(user_id):
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute('SELECT name FROM users WHERE id = %s', [user_id])
row = cur.fetchone()
if not row:
return None
return LanguageString.from_id(row)
def all_patient_data():
query = """
SELECT id, given_name, surname, date_of_birth, sex, country, hometown, phone, edited_at FROM patients ORDER BY edited_at DESC LIMIT 25
"""
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(query, [])
yield from cur
def get_one():
query = 'SELECT 1;'
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(query, [])
[result] = cur.fetchone() # Literally, haha
return result
def user_data_by_email(email):
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(
'SELECT id, name, role, email, hashed_password FROM users WHERE email = %s',
[email])
row = cur.fetchone()
if not row:
raise WebError("email not found", status_code=404)
return row
def add_visit(visit: Visit):
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(
'INSERT INTO visits (id, patient_id, clinic_id, provider_id, check_in_timestamp, edited_at) VALUES (%s, %s, %s, %s, %s, %s)',
[
visit.id, visit.patient_id, visit.clinic_id,
visit.provider_id, visit.check_in_timestamp,
visit.edited_at
])
def user_id_by_token(token):
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(
'SELECT user_id FROM tokens WHERE token = %s AND expiry > now()',
[token])
result = cur.fetchone()
if result:
return result[0]
else:
return None
def add_event(event: Event):
query = """
INSERT INTO events (id, patient_id, visit_id, event_type, event_timestamp, event_metadata, edited_at)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(query, [
event.id, event.patient_id, event.visit_id, event.event_type,
event.event_timestamp, event.event_metadata, event.edited_at
])
def first_visit_by_patient_and_date(
patient_id: str,
date: datetime.date) -> Tuple[Optional[str], Optional[str]]:
query = "SELECT id, check_in_timestamp FROM visits WHERE patient_id = %s AND date(check_in_timestamp) = %s;"
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(query, [patient_id, date])
row = cur.fetchone()
if row is None:
return None, None
else:
return row[0], row[1]
def add_user(user):
update_language_string(user.name)
with get_connection() as conn:
with conn.cursor() as cur:
query = '''
INSERT INTO users (id, name, role, email, hashed_password, edited_at) VALUES (%s, %s, %s, %s, %s, %s);
'''
cur.execute(query, [
user.id, user.name.id, user.role, user.email,
user.hashed_password,
datetime.now()
])
def update_language_string(language_string):
if language_string is None:
return
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute("INSERT INTO string_ids (id) VALUES (%s) ON CONFLICT (id) DO NOTHING",
[language_string.id])
for language, content in language_string.content_by_language.items():
cur.execute("INSERT INTO string_content (id, language, content, edited_at) " +
"VALUES (%s, %s, %s, %s) " +
"ON CONFLICT (id, language) DO UPDATE " +
"SET content = EXCLUDED.content, edited_at = EXCLUDED.edited_at",
[language_string.id, language, content, datetime.now()])
def patient_visits(patient_id: str):
query = "SELECT id, patient_id, clinic_id, provider_id, check_in_timestamp, edited_at, deleted FROM visits WHERE patient_id = %s AND not deleted ORDER BY check_in_timestamp DESC;"
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(query, [patient_id])
for row in cur:
yield Visit(id=row[0],
patient_id=row[1],
clinic_id=row[2],
provider_id=row[3],
check_in_timestamp=row[4],
edited_at=row[5],
deleted=row[6])
def get_most_common_clinic():
primary = """
select clinic_id, count(*) from visits where clinic_id is not null group by clinic_id order by count desc limit 1;
"""
secondary = "SELECT id from clinics;"
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(primary)
result = cur.fetchone()
if not result:
cur.execute(secondary)
result = cur.fetchone()
return result[0]
def get_table_rows(object_type, ids):
table_name = object_type.table_name()
columns, constructors = zip(*object_type.db_columns_from_server())
column_select_str = ', '.join(columns)
with get_connection() as conn:
with conn.cursor() as cur:
for id in ids:
cur.execute(
f'SELECT {column_select_str} FROM {table_name} WHERE id = %s',
[id])
row = cur.fetchone()
if row:
values = [c(r) for c, r in zip(constructors, row)]
yield object_type(**dict(zip(columns, values)))
def add_patient(patient: Patient):
update_language_string(patient.given_name)
update_language_string(patient.surname)
update_language_string(patient.country)
update_language_string(patient.hometown)
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(
'INSERT INTO patients (id, given_name, surname, date_of_birth, sex, country, hometown, phone, edited_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)',
[
patient.id,
to_id(patient.given_name),
to_id(patient.surname), patient.date_of_birth, patient.sex,
to_id(patient.country),
to_id(patient.hometown), patient.phone, patient.edited_at
])
def events_by_visit(visit_id: str):
query = """
SELECT id, patient_id, event_type, event_timestamp, event_metadata, edited_at FROM events
WHERE visit_id = %s
ORDER BY event_timestamp
"""
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(query, [visit_id])
for row in cur:
id, patient_id, event_type, event_timestamp, event_metadata, edited_at = row
yield Event(id=id,
patient_id=patient_id,
visit_id=visit_id,
event_type=event_type,
event_timestamp=event_timestamp,
event_metadata=event_metadata,
edited_at=edited_at)
def patient_from_id(patient_id):
query = """
SELECT given_name, surname, date_of_birth, sex, country, hometown, phone, edited_at FROM patients WHERE id = %s
"""
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(query, [patient_id])
row = cur.fetchone()
if row is None:
return None
given_name, surname, date_of_birth, sex, country, hometown, phone, edited_at = row
return Patient(id=patient_id,
given_name=LanguageString.from_id(given_name),
surname=LanguageString.from_id(surname),
date_of_birth=date_of_birth,
sex=sex,
country=LanguageString.from_id(country),
hometown=LanguageString.from_id(hometown),
phone=phone,
edited_at=edited_at)
def run(self):
while True:
user_id = self.q.get()
with get_connection(self.pool) as conn:
conn.autocommit = True
try:
with conn.cursor() as cursor:
cursor.execute("""
WITH cte AS (
UPDATE coupons
SET amount=amount-1
WHERE code = %s
RETURNING id
)
INSERT INTO user_coupon_usage
SELECT id, %s
FROM cte
""", (self.code, user_id))
except:
pass
self.q.task_done()
def patient_details(patient_id: str):
query = """
SELECT id, visit_id, event_type, event_timestamp, event_metadata, edited_at FROM events
WHERE patient_id = %s AND event_type = 'Patient Details'
ORDER BY event_timestamp
LIMIT 1
"""
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(query, [patient_id])
row = cur.fetchone()
if row is None:
return None
id, visit_id, event_type, event_timestamp, event_metadata, edited_at = row
return Event(id=id,
patient_id=patient_id,
visit_id=visit_id,
event_type=event_type,
event_timestamp=event_timestamp,
event_metadata=event_metadata,
edited_at=edited_at)
def patient_from_key_data(given_name: str, surname: str, country: str,
sex: str):
where_clauses = []
params = []
if given_name is not None:
where_clauses.append("get_string(given_name, 'en') = %s")
params.append(given_name)
else:
where_clauses.append("get_string(given_name, 'en') is null")
if surname is not None:
where_clauses.append("get_string(surname, 'en') = %s")
params.append(surname)
else:
where_clauses.append("get_string(surname, 'en') is null")
if country is not None:
where_clauses.append("get_string(country, 'en') = %s")
params.append(country)
else:
where_clauses.append("get_string(country, 'en') is null")
if sex is not None:
where_clauses.append("sex = %s")
params.append(sex)
else:
where_clauses.append('sex is null')
where_clause = ' AND '.join(where_clauses)
query = f"SELECT id FROM patients WHERE {where_clause};"
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(query, params)
row = cur.fetchone()
if row is None:
return None
return row[0]
def execute_sql(sql, rows):
with get_connection() as conn:
with conn.cursor() as cur:
for row in rows:
cur.execute(sql, row)
def get_ids_and_edit_timestamps(table_name):
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(f'SELECT id, edited_at FROM {table_name}')
return {k.replace('-', ''): ts for k, ts in cur}
def get_string_ids_and_edit_timestamps():
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute("SELECT id, language, edited_at FROM string_content)")
return {(id, lang): ts for id, lang, ts in cur}
import logging
student_info = {
"major":"cs",
"grade":"1",
"target":"1",
"data":{
"gpa":{"score":"3.2", "trend":"2", "school":"华中科技大学|湖北|985"},
"gmat":{"total":"300", "writing":"3", "verbal":"12", "maths":"12"},
"gre":{"total":"300", "writing":"4", "verbal":"12", "maths":"12"},
"toefl":{"total":"110", "writing":"3", "reading":"12", "listening":"12", "speaking":"12"},
"ielts":{"total":"7", "writing":"3", "reading":"12","listening":"12", "speaking":"12"},
"research":{"duration":"1", "level":"1", "achievement":"1", "recommendation":"1"},
"work":{"duration":"1", "level":"1", "recommendation":"1"},
"internship":{"duration":"5", "level":"3", "recommendation":"2"},
"reletter":{"level":["1","2","3"]},
"activity":{"duration":"1", "type":"1"},
"credential":{"level":"2"},
"competition":{"level":"2"},
"scholarship":{"level":"2"}
}
}
#assess_student.init()
#path_planning.init()
#print(json.dumps(path_planning.schedule(student_info), ensure_ascii=False, indent=4))
#print(json.dumps(assess_student.assess(student_info), ensure_ascii=False, indent=4))
tt = db_util.get_connection()
print(tt)
