Is this somewhat efficient?

Question

import mysql.connector, re
mydb = mysql.connector.connect(host="localhost", user="jeremy", password="jcvcruz123", database = "logdata")

def new_user():

Username = input("Enter Username: ")
Password = input("Enter Password: ")

my_cursor.execute('select id from Input;')
id = str(my_cursor.fetchall())
if id == "[]":
idnum = 0

else:
idnum = int(re.findall(r'\d+', id)[-1]) + 1

insert = 'insert into Input values( ' + str(idnum) + ', "' + Username + '", "' + Password + '");'
my_cursor.execute(insert)
mydb.commit()
print("Addition was successful")

my_cursor = mydb.cursor()
keyword = input('"Add", "Clear", or "Display": ')
if keyword == "Clear":

my_cursor.execute("delete from Input;")
mydb.commit()
print("Successfully cleared!")
elif keyword == "Display":

my_cursor.execute("select * from Input;")
records = my_cursor.fetchall()
for i in records:
print(i)
else:
new_user()

Answer 1

Jeremy Cruz There's quite a bit of room for this code to improve before meeting best practices standards.

I'll name a few that stand out.

1. Use named parameters instead of concentrating user input to build your SQL strings. Research more on how to mitigate SQL Injections.

2. I'm really not sure what's going on with: "select id from input" and the cursor.fetchall().

First, there isn't an id from input provided by the user. Then there isn't a where clause to filter on that id.

Why would an id be provided by the user? I would have this auto generated in the database.

Also, a query for a unique id should not require a fetchall(), but rather a fetchone().

3. There is no try catch for proper handling with the database.

4. There is no cleanup code to close the database / cursor connection.

This should be done using the with statement.

There's quite a bit more I could go into, but you've got a bit to take care of with these notes already. 😉

Best of luck.

Answer 2

What is your question here?

Answer 3

You can create class for this. Also add seperate method for different db operations.

Answer 4

Are there things that could be simplified?

Answer 5

same as 100+100 can be simplified to 200

Answer 6

The “search id from Input” is going into the database table and getting the id to find the last one to store it as a variable to then add 1 for a new user. Thank you so much for the feedback. Have a good day🙂