2:
3: import mysql.connector
4: conn = mysql.connector.connect(user='test', password='test',
database='pytest')
5: cursor = conn.cursor()
6: new_employee = ('INSERT INTO employees '
7: '(empid, lastname, firstname, salary) '
8: 'VALUES (%s, %s, %s, %s)')
9:
10: employee1 = ('1', 'Blum', 'Barbara', '45000.00')
11: employee2 = ('2', 'Blum', 'Rich', '30000.00')
12:
13: try:
14: cursor.execute(new_employee, employee1)
15: cursor.execute(new_employee, employee2)
16: conn.commit()
17: except:
18: print('Sorry, there was a problem adding the data')
19: else:
20: print('Data values added!')
21: cursor.close()
22: conn.close()First, you must define a cursor to the table (line 5). The cursor is a pointer object; it keeps track of
where in the table the current operation will perform. You must have a valid table cursor to be able to
insert new data records. You create a cursor by using the cursor() method after you establish the
connection. You need to assign the output of the cursor() method to a variable because you need
to reference that later on in your script.
Next, you have to create an INSERT statement template that you use to add a new data record (lines 6
through 8). The template uses placeholders for any data locations in the INSERT statement. That
allows you to reuse the same template to insert multiple data records.
The new_employee template defines the data fields for the data, as well as a data value
placeholder for each of the data values. It uses the %s format for the placeholders, no matter what
data type they really are.
After creating the template, you create the tuples that contain the actual data values (lines 10 and 11).
Make sure you list the data values in the same order in which you list the data fields in the INSERT
statement.
Now you’re ready to apply the data values to the INSERT statement template. You do that with the
execute() method (lines 14 and 15).
After submitting the new data record, you must run the commit() method for the connection to
commit the changes to the database (line 16).
After you run the script2101.py script, you can use the mysql command-line program to verify
that the new data values have been entered, as shown here:
Click here to view code image
pi@raspberrypi ~ $ python3 script2101.py
Data values added!
pi@raspberrypi ~ $ mysql -u test -p
Enter password:
mysql> use pytest;