This is a continuation from previous post MySQL-Python - 3.
In this page:
Insert
For insert sample, we will add a record in actor table from sakila sample database. We need to insert in actor table a record for person named NICK WALKEN which have last activity December 16, 2023
First let's see how records from actor table looks like. For this opening MySQL 8.0 Command Line Client there is:
After we see record structure we can imagine how SQL insert will looks like and maybe test this in MySQL 8.0 Command Line Client.
Insert is:
INSERT INTO actor (first_name, last_name, last_update) VALUES ('NICK', 'WALKEN',STR_TO_DATE('December 16, 2023', '%M %d,%Y'));
After run above select we verify result using a SELECT like below:
SELECT * FROM actor WHERE first_name LIKE 'NICK' AND last_name LIKE 'WALKEN';
And we will obviously delete it because we will run insert once verified in python code later. To delete:
DELETE FROM actor WHERE first_name LIKE 'NICK' AND last_name LIKE 'WALKEN';
Sample Code for INSERT
import datetime
import mysql.connector
connection = mysql.connector.connect(user='u2024', password='password1234', host='localhost', port=3306, database='sakila')
cursor = connection.cursor()
SQL_insert_actor = ("INSERT INTO actor (first_name, last_name, last_update)"
"VALUES (%s, %s, %s)")
values_for_actor = ('NICK','WALKEN', datetime.date(2023, 12, 16))
cursor.execute(SQL_insert_actor, values_for_actor)
# commit to the database
connection.commit()
cursor.close()
connection.close()
Below is the same code executed in PyCharm
Comments about code:
Flow is similar with select flow from "MySQL-Python - 3", i.e. there is connection object, cursor object, SQL statement this time is in string named SQL_insert_actor. In current code appear new commit() method, because having an insert that modify data, we need to commit to DB. Then follow known cursor and connection close.
Values that are used in insert are passed using notation similar with that from select with parameter, there is "VALUES (%s, %s, %s)".
To "populate" values, we put those in a tuple named values_for_actor using
values_for_actor = ('NICK','WALKEN', datetime.date(2023, 12, 16))
after that when run cursor execute:
cursor.execute(SQL_insert_actor, values_for_actor)
values from tuple i.e. 'NICK','WALKEN', datetime.date(2023, 12, 16) go one by one and will replace %s from SQL statement named SQL_insert_actor
Observation: when we checked with select statement actor table data, we see there column actor_id, this is not in VALUES because it is an AUTO_INCREMENT column.
Update
We propose to update NICK WALKEN record from actor table to change last_update to be December 19, 2023
Sample code for UPDATE:
import datetime
import mysql.connector
connection = mysql.connector.connect(user='u2024', password='password1234', host='localhost', port=3306, database='sakila')
cursor = connection.cursor()
SQL_update_actor = ("UPDATE actor SET last_update=%s WHERE first_name LIKE 'NICK' AND last_name LIKE 'WALKEN'")
tochange_last_update = (datetime.date(2023, 12, 19), )
cursor.execute(SQL_update_actor, tochange_last_update)
# commit to the database
connection.commit()
cursor.close()
connection.close()
We can check if update was ok, using select:
Comments about code:
- Code is similar with previous sample, main difference is SQL statement in this case is SQL_update_actor and the tuple tochange_last_update that we use to pass parameter to update, here tochange_last_update is a tuple with only one element, means this is why we not have (datetime.date(2023, 12, 19)) means heaving (datetime.date(2023, 12, 19), )
Comma from the end is for tuple with one element.
- Passing parameter to SQL statement, here SQL_update_actor is possible via %s, means something like replacing
SQL_update_actor = ("UPDATE actor SET last_update=%s WHERE first_name LIKE 'NICK' AND last_name LIKE 'WALKEN'")
tochange_last_update = (datetime.date(2023, 12, 19), )
cursor.execute(SQL_update_actor, tochange_last_update)
with
SQL_update_actor = ("UPDATE actor SET last_update=datetime.date(2023, 12, 19) WHERE first_name LIKE 'NICK' AND last_name LIKE 'WALKEN'")
cursor.execute(SQL_update_actor)
will not work. In this case datetime.date(2023, 12, 19) from inside SQL_update_actor will be treated like a routine from SQL point of view and _mysql_connector will give error
- parameter is expected to be tuple, list or dictionary
Means trying to use
tochange_last_update = datetime.date(2023, 12, 19)
(and not tochange_last_update = (datetime.date(2023, 12, 19), ) )
will fail with error:
mysql.connector.errors.ProgrammingError: Could not process parameters: date(2023-12-19), it must be of type list, tuple or dict