1

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.

Improve this question

4 Answers 4

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 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.
0

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.

Improve this answer

4 Comments

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...
0

I suppose you are using psycopg2

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

nick = cursor.fetchall()

if nick ...

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.