Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from employees;
+-------+----------+-----------+--------+
| empid | lastname | firstname | salary |
+-------+----------+-----------+--------+
| 1 | Blum | Barbara | 45000 |
| 2 | Blum | Rich | 30000 |
+-------+----------+-----------+--------+
2 rows in set (0.00 sec)mysql>And there they are! The next step is to write a Python script to retrieve the data that you just stored in
the table.
Watch Out!: Primary Key Data Constraint
Because you defined the empid data field as the primary key for the table, that value
must be unique for each data record. If you try to rerun the script2101.py script
without changing the data value tuples, the INSERT statements fail due to the duplicate
data values.Querying Data
The process of querying tables is similar to the process of inserting data records. Your Python script
must connect to the MySQL database, create a cursor, and then submit a SELECT SQL statement to
retrieve the data using the execute() method.
The difference from the insert process is that with the SELECT query, you need to retrieve data back
from the MySQL server. This is where the cursor object comes into play.
The cursor object contains a pointer to the query results. You must iterate through the cursor
object using a for loop to extract all the data records returned by the query.
Listing 21.2 shows the script2102.py program code, which demonstrates how to retrieve the
data records from the MySQL table.
LISTING 21.2 The script2102.py Program Code
Click here to view code image
1: #!/usr/bin/python3
2:
3: import mysql.connector
4: conn = mysql.connector.connect(user='test', password='test',
database='pytest')
5: cursor = conn.cursor()
6:
7: query = ('SELECT empid, lastname, firstname, salary FROM employees')
8: cursor.execute(query)
9: for (empid, lastname, firstname, salary) in cursor:
10: print(empid, lastname, firstname, salary)
11: cursor.close()