CODEWITHSUNDEEP
pythonmysqlmysql-pythonequation
Asdalud
Asdalud

Reputation: 139

Update MySQL field's row

I am using Python 2.7 and MySQLdb. I have made a database which has a table called test and in it two fields (id, firstname). The whole script is about counting and showing how many same firstnames do we have. The part of the script is like this right now but of course it doesn't work due to the 2nd line:

Value = int(input("Type your first name: "))
x.execute("UPDATE test SET id=(last id)+1 WHERE firstname=%s", (Value,))

What I am trying to do is type a Firstname from my keyboard and upload that to the database. If there is already that firstname in the table change his id which is a VARCHAR and make the new id = Last id + 1. For example if I type Doe and there is also Doe as a firstname in the database change Doe's row id number which is 1 by adding 1 and making it 2.

Upvotes: 0

Views: 830

Answers (1)

Crystallize
Crystallize

Reputation: 110

Sorry for the late answer.

I highly suggest to make the ID an INT with AUTO INCREMENT and PRIMARY KEY in order to prevent duplicates, but this would not allow you to change it freely as you want.

You need to check if there is a duplicate of the name you wrote inside the database, am i right?

  • If there is not, add a new row.
  • If there is, increment that name's id by one.

In order to check if that name is already in the database, you will need to do a SQL query, then check the results and compare the names you found with the one you wrote.

First, the query to get the names already in the database. Then, check if the name already exists. Then again, update or insert a new line into the database.

x.execute("""SELECT firstname FROM test""")
for row in x:
  result = row[0]
  if result == name:
    nameExists = True
  else:
    nameExists = False

if !nameExists:
  x.execute("""INSERT INTO test(firstname, id) VALUES (%s), (%s)""", name, id)

else:
  x.execute("""SELECT id FROM test WHERE firstname = %s""", name)
  for row in x:
    actualId = row[0]
  actualId = actualId + 1;
  x.execute("""UPDATE test SET id = %s WHERE firstname = %s""", actualId, name)

The code above may change based on your variable names or preferences. Do not take it as working, and copy-paste it directly into your code.

I hope the answer will be satysfing and complete. If not, let me know. It's the first time i answer to a question, and i may have not done it properly.

Thank you for your understanding.

Upvotes: 1

Related Questions