How can I insert a MySQL row IF one matched does not exist already

how to find out if a record already exists in a database, if it doesn't insert a new record
mysql insert if not exists else update
mysql check if row exists before insert
mysql insert if not exists without primary key
mysql insert if not exists else do nothing
php mysql check if record exists before insert
sql conditional insert if not exists
insert if not exists sql server

My table bank has three columns: uid, nick, and balance.

I can insert new row perfectly fine, however it would be easy for duplicates of the nick to appear since the uid auto increments, making each row unique. However I don't want that, I only one want one row for each nick.

    target = input.group(2)

    cursor.execute ("INSERT INTO bank (nick, balance) VALUES('" + db.escape_string(target.lower()) + "', 5)")
    db.commit()

    bot.say('An account for ' + target + ' has been created.')

That is my code so far, however I am unsure in how I would create a SELECT query and then check if the nick already exists in the table before inserting a new row.

I would adopt a different approach. I would add a unique constraint at DB-level on the nick column:

ALTER TABLE bank ADD UNIQUE (nick);

Within your Python code, then put a try and except to handle the unique constraint violated exception appropriately.

How to INSERT If Row Does Not Exist (UPSERT) in MySQL, If the record exists, it will be overwritten; if it does not yet exist, it will be created. -condition detects if there already exists a row with the data to be inserted. query returns an empty result set if the innermost query found matching data. How to INSERT If Row Does Not Exist (UPSERT) in MySQL. Using INSERT ON DUPLICATE KEY UPDATE. MySQL provides a number of useful statements when it is necessary to INSERT rows after determining whether that row is, in fact, new or already exists.

It doesn't sound like it makes sense to have that incrementing ID. Consider instead an unique identifier for the account, e.g. an account number. The danger with adding a unique constraint on nick, as another answer suggests, is that as your population grows, you will likely have two or more people who wish to use the same nick.

Also, you should pass the values to insert as a second argument to execute() for at least two reasons*.

cursor.execute("""INSERT INTO bank (nick, balance) 
                  VALUES (%s, %s);""", (target.lower(), 5))

*Two reasons for that are:

  1. You avoid having to manually handle any quoting issues. Mysql-Python will take care of that for you.
  2. You avoid a possibility of SQL-injection attacks.

Please note: the parameter placeholders are %s for all types of parameter, not just strings. So you don't need to do anything like %d to insert an integer.

How to 'insert if not exists' in MySQL?, There is slight change in Query, Just try to select the record first which you want to insert in database, if it is not exist then you can insert in it. "if select (mysql_num_rows) >= 1 then select id else insert contact and mysql_insert_id()" I was enquiring as to whether there is a preferred MySQL practice (e.g. using stored procedures or other approach) where a single query might suffice over a single MySQL connection, or whether the dual query approach (above) is always necessary to be

I suppose you are using psycopg2

cursor.execute ("SELECT * FROM bank WHERE nick = %s",[nick])

nick = cursor.fetchall()

if nick ...

[Solved] Mysql: insert record if not exists already, If any of the rows to be inserted by a given INSERT statement do not match one of Either may be used whether there is a single values list or multiple lists, and  The INSERT statement allows you to insert one or more rows into a table. The following illustrates the syntax of the INSERT statement: INSERT INTO table (c1,c2,) VALUES (v1,v2,); In this syntax, First, specify the table name and a list of comma-separated columns inside parentheses after the INSERT INTO clause.

I'm using Python (3.6) and MySql and I wanted to check records before adding them. Here is my code:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="password",
  database='football'
)

mycursor = mydb.cursor()

def write_to_db(db_table, col_list, data_list, *supply_id):
    """
    Takes a record, checks if it already exists and if not inserts it, returning its index or None
    :param db_table: name of table to be checked / inserted into
    :param col_list: column/field names in a list eg.  ['meat', 'sides']
    :param data_list: data to be found or inserted eg ['spam', 'eggs']
    :param supply_id: Only if you supply calculated id as tuple ('table_id', table_id_val)
    :return: If lists don't match =None Else =Index of found record or Index of inserted one
    """
    if len(col_list) != len(data_list) or len(col_list) == 0: # List lengths must match
        return None     # Otherwise returned index is None
    # Build search SQL - Select - table - Where - match conditions
    find_existing_sql = 'SELECT * FROM {} '.format(db_table)    # Which table
    find_existing_sql += 'WHERE {} = {} '.format(col_list[0], data_list[0])
    sql_end = ' LIMIT 1 ;'
    if len(col_list) > 1:  # Provided record has more than 1 column
        and_sql = ' AND {} = {} '           # add extra match condition for each column
        for indx in list(range(1, len(col_list))):
            find_existing_sql += and_sql.format(col_list[indx], data_list[indx])
        find_existing_sql += sql_end        # Complete SQL to find given record
    my_cursor.execute(find_existing_sql)    # Query database with SQL
    seek_response = my_cursor.fetchall()    # Read the seek a record response
    record_exists = len(seek_response) > 0  # Length = 0 not found, > 0 found
    if record_exists:
        return seek_response[0][0]          # Return id = the first item from the first list of items
    else:
        # Build insert SQL - Insert into  - table - column names - values 
        insert_sql = 'INSERT INTO {} ('.format(db_table)    # Which table
        if supply_id is not None:       # If you supplied an index
            id_col = supply_id[0][0]    # first item in first arg = index name
            id_val = supply_id[0][1]    # second item in first arg = index value
            col_list =[id_col] + col_list       # Add index column name on the front of column list
            data_list = [id_val] + data_list    # Add index value on front of data_list
        first_col = '{}'.format(col_list[0])    # Start listing columns
        more_cols_vals = ', {}'                 # To add more coumns/values if needed
        values_sql = ') VALUES ( {} '.format(data_list[0]) # Start listing values
        end_sql = ' );'  
        insert_cols_sql = insert_sql + first_col    
        if len(col_list) > 1:       
            for indx in list(range(1, len(col_list))):
                insert_cols_sql += more_cols_vals.format(col_list[indx])
                values_sql += more_cols_vals.format(data_list[indx])
        # Put Insert SQL together
        insert_new_sql = insert_cols_sql + values_sql + end_sql
        my_cursor.execute(insert_new_sql) # Insert the new record into db
        mydb.commit()
        if supply_id is not None:   # If you supplied an index
            return id_val           # Return that
        else:                           # if not
            return my_cursor.lastrowid  # Return auto-generated index

This function takes the name of the table, a list of column names, a list of values to insert and an optional tuple if you supply your own generated index thus

    ('table_id', table_id_value)

but if you don't supply this tuple, MySQL will use the auto-generated index for the insert. The routine assumes that your table has the index as its first column.

It returns the index of the found record if it already exists, if the record doesn't exist it returns the index it uses when it inserts the record. The index is either the one MySQL generates or the one you supply. It returns None if your column names list and value list lengths don't match.

I have used Python to build the SQL statements so you can have a variable number of columns. You could pass values within SQL (though I've no idea how you do that for a variable number of columns and values) but I kept getting 'wrong number of parameters' errors and using Python's string.format() solved that.

The *supply_id (conventionally *args) parameter is a list even if you only supply one tuple so I needed two indices to access the first arg and then the 0 or 1 to access the column name and value in the tuple.

The .fetchall() itself was necessary to prevent 'unread record' errors as it clears the data from the cursor if a record is found.

MySQL 5.6 Reference Manual :: 13.2.5 INSERT Statement, Subqueries with EXISTS or NOT EXISTS INSERT INTO t1 (a,b,c) VALUES (1,2,​3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE t1 SET c=c+1 WHERE a=1;. (​The effects are not identical for an InnoDB table where a is an auto-increment column. With an If a=1 OR b=2 matches several rows, only one row is updated. To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when row exists in the table, otherwise false is returned. True is represented in the form of 1 and false is represented as 0.

MySQL 8.0 Reference Manual :: 13.2.6.2 INSERT ON , Each matching row is updated once, even if it matches the conditions multiple the case when using PARTITION with an INSERT or REPLACE statement, an there is no guarantee that assignments are carried out in any particular order. If a column to the value it currently has, MySQL notices this and does not update it. Fastest way to insert new records where one doesn’t already exist. SQL Developers come across this scenario quite often – having to insert records into a table where a record doesn’t already exist. The age-old technique and I suspect most common practice is doing a left join where the values are null from the table being inserted into.

MySQL 8.0 Reference Manual :: 13.2.13 UPDATE Statement, Sql insert if does not exist keyword after analyzing the system lists the list of For example, MySQL has the LOAD DATA INFILE, which reads rows from a text file the insert even though the record with the values does already exist: IF NOT a query as soon as any 1 matching record is found, which is more efficient than  Instead of using the “where customerId = 5” clause, you can add a subquery. That’s where NOT EXISTS works. For instance, take the following query: select * from customers where NOT EXISTS (select customerId from orders) The above statement basically says “give me all customer records where the customerId does not exist in the orders

Sql Insert If Does Not Exist, The MariaDB EXISTS condition is used in combination with a subquery and is considered to be met if the subquery returns at least one row. This website would not exist without the advertisements we display and your kind donations. If you are row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement. The database engine does not have to run the subquery entirely. If a single record is matched, the EXISTS operator returns true , and the associated other query row is selected. The inner subquery is correlated because the student_id column of the student_grade table is matched against the id column of the outer student table.

Comments
  • Depending on the application logic you might also want to add an ON DUPLICATE KEY clause to the INSERT rather than handling the exception.
  • @liquorvicar Yes, depending on app logic. Personally, the more "behavioural" logic gets, the more it is suited to Python (as opposed to more SQL). In this context UNIQUE constraint is DDL, and is not "behavioural" as such. However this is just my personal taste.
  • I'm a bit confused about this UNIQUE, how does the query know what the nick value is? I tried searching, but it all seems a bit too confusing for me.
  • @Markum Explanation of UNIQUE constraint here. Basically, if your code tries to insert a value of nick that already exists, the constraint will be violated and an exception is raised in Python code which you can handle via try and except as usual.
  • The uid pretty much is my way of uniquely identifying the account, which is why I set it to add one for each new account. And thanks for the tip, I will do that. But I still have no solution for the original question.
  • So then how are the accounts duplicates? Two accounts cannot have the same nick and balance? That seems sort of an arbitrary restriction...
  • New accounts can still be added with the same nick over and over, the uid will just keep increasing.
  • Hm. Your problem now seems slightly different than what is written in your question...