pythonmysqlwhile-loopforeign-keysinsertion

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


I have 3 table in my database:

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.


Solution

  • 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()