What is the below code doing? queryresult= CommonFunction.CommonFun.convertToJson(…
- User
What is the below code doing?
queryresult= Common_Function.CommonFun.convertToJson( Constant.constant.constant.getPatientDtl, session.query(Model.models.Application.M_Patient.MPID.label('ID'), Model.models.Application.M_Patient.MP_UHID.label('UHID'), Model.models.Application.M_Patient.MP_Name.label('Name'), Model.models.Application.M_Patient.MP_Email.label('Email'), Model.models.Application.M_Patient.MP_Mobile.label('Mobile'), Model.models.Application.M_Patient.MP_Address.label('Address'), sqlalchemy.func.concat(Model.models.Application.M_ReferredByDoctor.MRD_FirstName,' ',Model.models.Application.M_ReferredByDoctor.MRD_LastName).label('Referred By') ).filter(Model.models.Application.M_Patient.MP_IsActive==1,Model.models.Application.M_Patient.MP_IsDeleted==0, Model.models.Application.M_Patient.M_Branch_MBID == int(branchId) ).filter(*apply ).outerjoin(Model.models.Application.M_ReferredByDoctor, Model.models.Application.M_ReferredByDoctor.MRDID==Model.models.Application.M_Patient.MP_RefferedBy ).order_by(Model.models.Application.M_Patient.MPID.desc()).all() )
- Assistant
The code is building a SQL query to select specified columns from linked tables representing patients and their referring doctors in a database. It then executes this query to fetch results and converts these results to JSON format through a custom function. Here's a breakdown of what each part of the code is doing:
-
session.query(...): This part uses SQLAlchemy to build a query. Thesessionobject represents a connection to the database and is used to issue SQL commands. -
The
queryfunction includes a list of column expressions to be selected:label('...')is used to give an alias to the columns for readability in the result set.sqlalchemy.func.concat(...)is used to concatenate the first and last name of the referred-by-doctor with a space in between.
-
.filter(...): These method calls addWHEREclause expressions to the query to filter the result set. It's filtering patients who are active (MP_IsActive==1) and not deleted (MP_IsDeleted==0) and match the providedbranchId. -
.filter(*apply): This seems to indicate additional filter conditions that are being applied, whereapplycould be a list or tuple of conditions. -
.outerjoin(...): This method performs a left outer join on theM_ReferredByDoctortable based on a related column (MRDID) to the patient's referred-by column (MP_RefferedBy). It means that the query will include all patients, with or without a matching referred-by-doctor record. -
.order_by(...desc()): Orders the results in descending order by the patient ID. -
.all(): Executes the query and retrieves all results into a list. -
Common_Function.CommonFun.convertToJson(...): After the query results are fetched, this function is called to convert the query result set into JSON format. It might be a utility function defined within a module or classCommon_Function.CommonFun. -
The
queryresultvariable is assigned the JSON-converted result of the query.
The
Constant.constant.constant.getPatientDtlwithin theconvertToJsonfunction might be a string or configuration setting indicating the kind of data or template to use for JSON conversion, though this is unclear without more context. -
- User
Modify this below query such that it returns the value of mobile (M_Patient table contains mobile column) in corresponding to the searched MPID row. queryresult = session.query(Model.models.Application.M_Patient.MPID.label('ID'),
).filter(Model.models.Application.M_Patient.MP_IsActive==1,Model.models.Application.M_Patient.MP_IsDeleted==0) session.commit() - Assistant
To modify the query to return the value of the
Mobilecolumn from theM_Patienttable corresponding to a searchedMPIDrow where the patient is active and not deleted, you can add the mobile column to thequerymethod like this:queryresult = session.query( Model.models.Application.M_Patient.MPID.label('ID'), Model.models.Application.M_Patient.MP_Mobile.label('Mobile') # Add mobile column here ).filter( Model.models.Application.M_Patient.MP_IsActive == 1, Model.models.Application.M_Patient.MP_IsDeleted == 0 ).filter( Model.models.Application.M_Patient.MPID == searched_mpid # Add a filter for the searched MPID ) # No need for session.commit() since we're only querying, not changing dataPlease note that I added a placeholder
searched_mpidwhich you should replace with the actual patient ID you are looking for. Also, I've removedsession.commit()since we are just fetching data, not modifying it.session.commit()is typically used to commit transactions that alter the data in the database, such as insert, update, or delete operations.The complete modification would look like this:
# Variable 'searched_mpid' should be set to the MPID of the patient you're searching for searched_mpid = 123 # Replace 123 with the actual MPID queryresult = session.query( Model.models.Application.M_Patient.MPID.label('ID'), Model.models.Application.M_Patient.MP_Mobile.label('Mobile') # Add mobile column here ).filter( Model.models.Application.M_Patient.MP_IsActive == 1, Model.models.Application.M_Patient.MP_IsDeleted == 0, Model.models.Application.M_Patient.MPID == searched_mpid # Add a filter for the searched MPID ).one_or_none() # Use one_or_none() to get a single result or None if no match is foundI've used
.one_or_none()to retrieve a single result if the patient ID is found, and it would returnNoneif no such patient exists. If you expect more than one record and want to retrieve all of them, you can replace.one_or_none()with.all(). - User
Convert the below SQL query to be able to used in SQLAlchemy in python:
SELECT m_patientsdtl.MPD_TokenFCM FROM m_patient JOIN m_patientsdtl ON m_patient.MP_Mobile = m_patientsdtl.MPD_MobNoCountryCode WHERE m_patient.MPID = 14118;
- Assistant
To convert the SQL query you provided to SQLAlchemy in Python, you need to construct the corresponding SQLAlchemy query object by following these steps:
-
Define your model classes in SQLAlchemy according to your database schema (if not already defined). For this example, let's assume you have two model classes
M_PatientandM_PatientsDtlthat represent them_patientandm_patientsdtltables, respectively. -
Use the session object to build and execute your query.
Here's what your SQL query might look like in SQLAlchemy:
from sqlalchemy.orm import sessionmaker # Assuming you have an engine object already created with create_engine() Session = sessionmaker(bind=engine) session = Session() # Your custom MPID to search target_mpid = 14118 # Constructing the SQLAlchemy query object # Assuming 'M_Patient' and 'M_PatientsDtl' classes exist and map to 'm_patient' and 'm_patientsdtl' tables respectively queryresult = session.query( M_PatientsDtl.MPD_TokenFCM # Select the MPD_TokenFCM column from M_PatientsDtl ).join( M_Patient, # Specify the first table to join M_Patient.MP_Mobile == M_PatientsDtl.MPD_MobNoCountryCode # Join condition ).filter( M_Patient.MPID == target_mpid # Your WHERE clause condition ).one_or_none() # For fetching a single result or None if not found session.close() # Always close the session when donePlease ensure that:
- You import the correct sessionmaker and any other necessary components from SQLAlchemy.
- You replace
M_PatientandM_PatientsDtlwith the actual class names that map to your database tables form_patientandm_patientsdtl. These classes should already be defined in your ORM model corresponding to the schema of your tables. - The
MP_MobileandMPD_MobNoCountryCodefields are the correct field names as used in your model classes. - You replace
MPD_TokenFCMwith the correct field name as defined in theM_PatientsDtlclass.
This code snippet assumes you already have an engine object created and properly configured to connect to your database, and that your database models (
M_PatientandM_PatientsDtl) are properly mapped to your database schema using SQLAlchemy's ORM system. -
- User
Is the below code correct? PID = request_json.get('pname')
fcmToken = session.query( Model.models.Application.M_PatientsDtl.MPD_TokenFCM # Select the MPD_TokenFCM column from M_PatientsDtl ).join( Model.models.Application.M_Patient, # Specify the first table to join Model.models.Application.M_Patient.MP_Mobile == Model.models.Application.M_PatientsDtl.MPD_MobNoCountryCode # Join condition ).filter( Model.models.Application.M_Patient.MPID == PID # Your WHERE clause condition ).one_or_none() # For fetching a single result or None if not found session.close() # Always close the session when done - Assistant
The code you provided seems to be a correct implementation of a SQLAlchemy query to fetch a single
MPD_TokenFCMvalue from theM_PatientsDtltable by joining it with theM_Patienttable and using a patient ID provided in a JSON request. However, there are a few assumptions and considerations to keep in mind:-
request_jsonneeds to be a Python dictionary that represents the parsed JSON request you are getting, it should be obtained prior to this code snippet. -
The variable
PIDobtained from the JSON request should be an integer or a string representation of an integer, matching the type ofMPIDin your database. -
The query uses
one_or_none()to fetch a single result; this is correct assumingMPIDis unique and you are expecting at most one result. -
Ensure that the classes
M_PatientandM_PatientsDtl, as well as the attributesMP_Mobile,MPD_MobNoCountryCode, andMPD_TokenFCMare properly defined and accurately reflect the structure of your database. -
The
session.close()statement is good for ensuring the database connection is closed after the operation. However, if you're managing sessions with a context manager or other session lifecycle management technique (e.g., using global scoped sessions, Flask-SQLAlchemy, or dependency injection with FastAPI), the session might be closed differently. -
Verify that the
sessionobject has been properly instantiated and connected to your database.
If all the above points are correctly addressed in your code context, then the provided SQLAlchemy query should work as intended.
Here's an equivalent code snippet with improved readability and added comments:
from Model.models.Application import M_Patient, M_PatientsDtl # Assuming 'request_json' is already a Python dictionary that contains 'pname' key with PID PID = request_json.get('pname') # Perform SQLAlchemy query to get FCM token by joining M_Patient and M_PatientsDtl fcmToken = session.query( M_PatientsDtl.MPD_TokenFCM # Select the FCM token column ).join( M_Patient, # Join with the M_Patient table M_Patient.MP_Mobile == M_PatientsDtl.MPD_MobNoCountryCode # Condition for the join ).filter( M_Patient.MPID == PID # Filter by patient ID ).one_or_none() # Fetch a single result or None if not found # After the operation, close the session (assuming no other query is going to be executed afterward) session.close()Again, this snippet assumes you've set up your database model classes and session appropriately. If you're using an application framework like Flask or FastAPI, some of this setup might be handled by the framework.
-
- User
Im getting this error in below code: Error- ["(pymysql.err.OperationalError) (1054, "Unknown column 'M_PatientsDtl.MPD_TokenFCM' in 'field list'")\n[SQL: SELECT M_PatientsDtl.MPD_TokenFCM AS M_PatientsDtl_MPD_TokenFCM \nFROM M_PatientsDtl INNER JOIN m_patient ON m_patient.MP_Mobile = M_PatientsDtl.MPD_MobNoCountryCode \nWHERE m_patient.MPID = %(MPID_1)s]\n[parameters: {'MPID_1': 14118}]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)"] Code: request_json = request.get_json() PID = request_json.get('pname')
fcmToken = session.query( Model.models.Application.M_PatientsDtl.MPD_TokenFCM # Select the MPD_TokenFCM column from M_PatientsDtl ).join( Model.models.Application.M_Patient, # Specify the first table to join Model.models.Application.M_Patient.MP_Mobile == Model.models.Application.M_PatientsDtl.MPD_MobNoCountryCode # Join condition ).filter( Model.models.Application.M_Patient.MPID == PID # Your WHERE clause condition ).one_or_none() # For fetching a single result or None if not found session.close() # Always close the session when done - Assistant
The error message you're getting indicates that the column
MPD_TokenFCMin theM_PatientsDtltable is not found in the database. This could be happening due to a few reasons:-
The column
MPD_TokenFCMdoes not exist in theM_PatientsDtltable. Check your database schema to ensure that the column exists and that there is no typo in the column name. -
There might be a mismatch in case sensitivity. In some database systems, column names are case-sensitive. Ensure the column name case matches exactly with the database schema.
-
The table prefix or schema might be incorrect. Sometimes databases use a schema or a prefix for table names and if that's the case, make sure you're including it correctly.
-
Your model class
M_PatientsDtlinModel.models.Applicationmay not be defined accurately to reflect the actual structure of theM_PatientsDtltable in the database. Ensure that theMPD_TokenFCMfield is correctly defined within theM_PatientsDtlclass in your model.
Here's how you can check your model definition:
# Within your model definitions (make sure this structure is correct) class M_PatientsDtl(Base): # Inherits from the declarative base __tablename__ = 'M_PatientsDtl' # Exact table name as in the database MPD_TokenFCM = Column(String) # Replace `String` with the data type used in your database # ...other fields and configurations for the model...Once you've checked and corrected these issues, try running your code again. If the
MPD_TokenFCMcolumn does exist with the correct name and type in your database, and the model is accurately defined in SQLAlchemy, the query should work without giving you an "Unknown column" error.You may also wish to confirm you are connected to the correct database and schema where the
M_PatientsDtltable resides. -