Inserting Records via SQL Query in Python

Now let's learn how to add new records to a table. This is done using the INSERT INTO command. It has the following syntax:

query = "INSERT INTO table (field1, field2...) VALUES (value1, value2...)"

Let's add a new user to our users table. However, in case of inserting a new record, our code should be rewritten. Instead of the fetchone or fetchall method, the commit method is applied to the CMySQLCursor object. It is needed to commit the changes made by the user:

query = "INSERT INTO users (name, age, salary) VALUES ('user', 30, 1000)"
with connection.cursor(dictionary=True) as cursor:
	cursor.execute(query)
	connection.commit()

The paste result will not be visible in the console, but can be viewed in PMA.

Please also note that when inserting, the id column and its value are not specified. And this is correct, since the value of this column will be set automatically by the database.

Add new user 'user7', 26 years old, salary 300.

Add new user 'user8', 32 years old, salary 1100.

Add new user 'user9', 22 years old, salary 350.