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)