Insert value to MySQL Database if not exist using Python(PyMySQL)

Question

I am trying to Fetch value from an existing database and also insert the value into the database if not exist.

This code works fine to fetch the value the value but I am not able to insert the value into database..

    import pymysql
    connection = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='chatbotqad',
    )
    user_input=input('Enter :')
    print(user_input)
    try:
    with connection.cursor() as cursor:
    sql = "SELECT * FROM `qans` WHERE `Questions` = ('%s') " % user_input
    try:
        cursor.execute(sql)
        result = cursor.fetchall()

        if not cursor.fetchone():
            sqli = "INSERT INTO `qans` VALUES (%s)" % user_input
            cursor.execute(sqli) 

        print("Que\t\t Answer")            
        print("-------------------------")            
        for row in result:
            print(str(row[0]) + "\t\t" + row[1])


    except:
        print("Oops! Something wrong")
    connection.commit()
    finally:
    connection.close()**strong text**

This code is prone to race conditions, ideally you should also add a unique key in your table and also you should do this in one query more or less like INSERT INTO <table> <columns> SELECT <columns> FROM <table> WHERE <condition> .. See the MySQL manual how to use INSERT INTO .. SELECT ... syntax — Raymond Nijland
– Raymond Nijland, Commented Apr 16, 2019 at 10:43
Can you see whether the insert statement is running - e.g. place a print() statement before cursor.execute(sqli) — Will Keeling
– Will Keeling, Commented Apr 16, 2019 at 15:03

score 2 · Accepted Answer · 2019-04-25 14:05:01Z

I finally found this solution.

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    db='chatbotqad',
)

user_input=input('Enter input:')

try:
    with connection.cursor() as cursor:
        sql = "SELECT * FROM `qans` WHERE `Questions` = ('%s') " % user_input

        try:
            if (cursor.execute(sql)==0):
                result = cursor.fetchall()

                Ans_input=input('Please enter Answer:-')
                sql = "INSERT INTO `qans` (Questions, Answers) VALUES (%s,%s)"

                try:
                    cursor.execute(sql, (user_input,Ans_input))
                    print("Task added successfully")
                except:
                    print("Oops! Something wrong")

            else:
                cursor.execute(sql)
                result = cursor.fetchall()
                print("Que\t\t Answer")
                print("-------------------------")
                for row in result:
                     print(str(row[0]) + "\t\t" + row[1])
        except:
            print("Oops! Something wrong")

    connection.commit()
finally:
    connection.close()

Collectives™ on Stack Overflow

Insert value to MySQL Database if not exist using Python(PyMySQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related