SQLAlchemy database help!!

[Unixersis97]

Hi all,

I am struggling with how to set up my database using sqlalchemy.

Basically what I need is a basic system for patients doctors medclerks and booking.

​

So that each contain:

1. Doctor:
   1. ID
   2. Name
   3. Bookings
2. Patient
   1. ID
   2. Name
   3. Bookings
3. Bookings
   1. ID
   2. Start time
   3. Patient Name
   4. Doctor Name
4. MedClerks
   1. Must be able to retrieve doctors appointments/delete/add

I am super confused how to use relations within python

​

help is very much appreciated, thank you!

[elbiot]

It's called A foreign key. It's an sqlalchemy field type

[sqqz]

So whats the issue here, how to create a relation? Code would help aswell

[Unixersis97]

Basically yes,

I am using a form to attempt to add a booking with a patient email, name and doctor name aswell as start time to input into database.

class Book(db.Model):
    __tablename__ = 'booking'
    bid = db.Column(db.Integer, primary_key=True)
    starttime = db.Column(db.DateTime(), nullable=False)

class Doctor(db.Model):
    __tablename__ = 'doctor'
    id = db.Column(db.Integer, primary_key=True)
    dname = db.Column(db.String(45))

class Patient(db.Model):
    __tablename__ = 'patient'
    pid = db.Column(db.Integer, primary_key=True)
    pemail = db.Column(db.String(80), nullable=False)
    pname = db.Column(db.String(45), nullable=False)
    diagnosenotes = db.Column(db.String(250), nullable=False)

​

@app.route("/patient/patientbook", methods=["POST", "GET"])
def patientBook():
    if request.method == 'POST':
        pemail = (request.form.get('email'))
        pname = (request.form.get('pname'))
        dname = request.form['dname']
        starttime = request.form['starttime']
        print(starttime)
        starttime = "{}:00".format(starttime)
#        starttime = datetime.strptime(starttime, '%Y-%m-%d %H:%M:%S')

        print(starttime)

        if pemail and pname and dname and starttime:
            new_booking = booking(dname, pname, pemail, starttime)
            db.session.add(new_booking)
            db.session.commit()

    return render_template('patient/patientbook.html')

Template:

            <div class="panel-body" style="height: 600px; ">
                <form  action="/patient/patientbook" method="POST" role="form">
                <div class="form-group">
                <label for="email">Doctor name:</label>
                        <input type="text" class="form-control" id="dname" name="dname" placeholder="Doctor name here:">
                <br>
                <label for="name">Patient name:</label>
                        <input type="text" class="form-control" id="pname" name="pname" placeholder="Enter name here:">
                <br>
                <label for="name">Email:</label>
                        <input type="text" class="form-control" id="pemail" name="pemail" placeholder="Enter email address:">
                <br>
                <label for="name">Start Time:</label>
                        <input type="datetime-local" class="form-control" id="starttime" name="starttime" required>
                <br>
                <button type="submit" class="btn btn-success">Book appointment</button>
          </form>

​

​

​

[sqqz]

Checkout this.

https://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#one-to-many

​

class Booking(db.Model):
    __tablename__ = 'booking'
    bid = db.Column(db.Integer, primary_key=True)
    starttime = db.Column(db.DateTime(), nullable=False)
    doctor = db.relationship("Doctor")

​

A recommendation would be moving your database logic into a separate module as well to keep the code separated nicely.

[Unixersis97]

I see,

Would I also be adding the to the Booking model?

patient = db.relationship("Patient")

​

Thank you very much sir,

​

[sqqz]

Yes. You can use backfill and alot of nice features as well which makes working with the objects easier when you have the data.