0

I am running 3 consecutive and dependent SQL queries and I am wondering if my code could be more efficient. I had to create 3 separate cursors to execute my method. What can I do to make it more efficient?

What I am doing in that method is:

  1. Insert a new contributor in my contributors table based on the values send on the form
  2. Get the primary key of that new contribution which is it's contributor_id
  3. Insert a new question on the questions table and the foreign key of that table is the contributor_id from the contributors table

I don't want to use an ORM such as SQLAlchemy.

   conn = pymysql.connect(
        host = 'localhost',
        user = 'root',
        passwd = 'xxx!',
        db = 'xxx'
    )

    @app.route('/add_contributor',methods = ['POST', 'GET'])
    def add_contributor():
        name = request.form.get('contrib_name')
        question = request.form.get('question')
        
        sql_1 = "INSERT INTO contributors (name) VALUES (%s)"
        sql_2 = "SELECT contributor_id from contributors WHERE name=(%s)"
        sql_3 = "INSERT INTO questions (contributor_id, question_text) VALUES (%s, %s)"
            
        cursor = conn.cursor()
        cursor.execute(sql_1, name)
        cursor.fetchall()
        conn.commit()
        
        cursor_2 = conn.cursor()
        cursor_2.execute(sql_2, name)
        contrib_val = cursor_2.fetchall()
        contrib_id = contrib_val[0][0]
    
        cursor_3 = conn.cursor()
        cursor_3.execute(sql_3, (contrib_id,question))    
        cursor_3.fetchall()
        conn.commit()
Pierre-Alexandre
  • 543
  • 1
  • 10
  • 31
  • 1
    Is the `contributor_id` column an autoincrementing primary key? Then use `SELECT LAST_INSERT_ID()` to get it. Consider using a stored procedure to insert both a contributor and the question. Consider using SQLAlchemy to give you OO models you can create and let SQLAlchemy worry about insert statements. – Martijn Pieters Feb 06 '21 at 13:00
  • Thanks Martijn, yes the `contributor_id` column an autoincrementing primary key. I am going to update my post actually because I really don't want to use SQLAlchemy – Pierre-Alexandre Feb 06 '21 at 13:02
  • 1
    Also see [How to return the last primary key after INSERT in pymysql (python3.5)?](https://stackoverflow.com/q/37524512), I suspect `cursor.lastrowid` will already give you the result for the second query, and much more reliably unless the `name` column is guaranteed to be unique. – Martijn Pieters Feb 06 '21 at 13:02

0 Answers0