Inserting a SQL Query from Python

Question

I am trying to create a def within a class which stores an employees information. I am having trouble adding the data to the table. I am not too sure where I am going wrong and I can't seem to find the solution after looking at other posts.

    def create_db(self):
        name = input('Enter first name: ')
        surname = input('Enter last name: ')
        age = int(input('Enter age: '))
        email = input('Enter e-mail address: ')
        number = int(input('Enter number: '))
        country = input('Enter country: ')
        city = input('Enter city: ')
        id = random.randint(0,9999)

        query = "INSERT INTO employees (id, name, surname, age, email, number, country, city) VALUES (%s, %s)"
        value = f"'{id}', '{name}', '{surname}', '{age}', '{email}', '{number}', '{country}', '{city}'"
        self.mycursor.execute(query, value,)
        self.db.commit()

I believe it is to do with the last 4 lines but may be incorrect. Thanks for any help in advance.

Is this homework? Is there any stacktrace, error logs, anything? Need a little bit more than just the code. — ltd9938, Jan 20 '22 at 13:42
It would be helpful if you explain what kind of library you used to connect database, what database you are using, and what are the error msg. — Park, Jan 20 '22 at 13:43
`value` should be a `tuple` or `list` of values to be inserted. In the SQL statement, the number of `%s` placeholders should be equal to the number of values. See the linked duplicate for further information about this. — snakecharmerb, Jan 20 '22 at 14:07
Sorry, I am using the MySQL connector, the problem I have now is it runs but doesn't commit to the local host server. — SkiesLearns, Jan 20 '22 at 14:08

score 0 · Accepted Answer · answered Jan 20 '22 at 13:43

0

how are you formatting the values ??

        query = "INSERT INTO employees (id, name, surname, age, email, number, country, city) VALUES (%s, %s)"
        value = f"'{id}', '{name}', '{surname}', '{age}', '{email}', '{number}', '{country}', '{city}'"
        self.mycursor.execute(query, value,)
        self.db.commit()

have you tried this instead ?

values = f"'{id}', '{name}', '{surname}', '{age}', '{email}', '{number}', '{country}', '{city}'"
# note the formatting
query = f"INSERT INTO employees (id, name, surname, age, email, number, country, city) VALUES ({values})" # note the f-string expression
self.mycursor.execute(query)
self.db.commit()

answered Jan 20 '22 at 13:43

alexzander

1,586
1
9
21

That works thank you, I presume it has something to do with the values part being above the query and then adding it into query? Also I have one more issue, its like the data I input doesn't commit to the SQL local host server. – SkiesLearns Jan 20 '22 at 13:46
okey? shouldnt be `self.mycursor.commit()` ? – alexzander Jan 20 '22 at 13:48
Tried that and get the error AttributeError: 'CMySQLCursor' object has no attribute 'commit' – SkiesLearns Jan 20 '22 at 14:05

Inserting a SQL Query from Python

1 Answers1