If a table has columns equal to columns in a second table insert values in a third table, python - mysql

Question

I have 3 table in my database:

• Borrowers: amount of the loan, interest rate, borrower id (PK)
• Lenders: amount of the bid, interest rate and LenderID (PK)
• Contracts: ContractID PK, amount, interest rate and the two foreign key(borrowerID, lenderID)

I create a loop in python with an input command in which I can insert new request of loan and new bid offer. Every insertion is connected with mysql and it is added to the respective table.

For example:

input in python --> L 1000 1.1 output --> create an insertion in Borrowers table with an auto_incremental PK, an amount of 1000 and an interest rate of 1.1%

I need to create another function in the loop in which, for every new request/offer, if an offer is equal to a request (Borrowers.Amount, BorrowersInterestRate = Lenders.Amount, Lenders.InterestRate), it will be created an insertion in contracts table with the same amount and interest rate.

Answer 1

This is only works if you are using mysql python connector. As far as I know that is the only way to use mysql using python but I maybe wrong.

to search if request matches the offer:

def search_for_request(amount, interest_rate):
    # assuming that columns containing amount and interest rate are named as such
    database.cursor.execute("select amount_of_the_bid, interest_rate from lenders")
    result = database.cursor.fetchall()
    for amount_and_interest in result:
        # not sure if this step is necessary
        amount_and_interest = list(amount_and_interest)
        if amount_and_interst[0] == amount and amount_and_interst[1] == interest_rate:
            # execute query for entering the contract info in contract table
            database.commit()

to search if offer matches request:

def search_for_offer(amount, interest_rate):
    # assuming that columns containing amount and interest rate are named as such
    database.cursor.execute("select amount_of_the_bid, interest_rate from borrowers")
    result = database.cursor.fetchall()
    for amount_and_interest in result:
        # not sure if this step is necessary
        amount_and_interest = list(amount_and_interest)
        if amount_and_interest[0] == amount and amount_and_interest[1] == interest_rate:
            # execute query for entering the contract info in contract table
            database.commit()