If all goes well, you shouldn’t get any error messages about a missing module when
you run the import statement. Now you’re ready to start working with the MySQL
database!By the Way: Downloading Debian Packages
If you don’t have a graphical environment setup on your Raspberry Pi, you can
download the MySQL/Connector Debian package on a separate workstation, then use
SFTP to copy it over to your Raspberry Pi system.Creating Your Python Scripts
Now that you have a MySQL database and all the pieces for your Python script to interact with your
MySQL database, you can start some coding. The following sections walk through the main processes
that you need to use to create and retrieve data.
Connecting to the Database
The first step in interacting with the MySQL database is to establish a connection from your Python
script to the MySQL server. That’s done using the connect() method, as shown here:
Click here to view code image
>>> import mysql.connector
>>> conn = mysql.connector.connect(user='test', password='test',
database='pytest')
>>>You must first import the mysql.connector module, and then you can run the connect()
method from the library. In the connect() method, you need to specify the user account and
password to connect to the MySQL server, as well as the database name. When you’re done
interacting with the database, you should use the close() method to close the connection.
Watch Out!: Database Script Security
You may have noticed that for the connect() method, you must specify the user
account and password directly in your Python script. This can be somewhat of a
security issue, so make sure to use the proper permissions on your script to protect it
from being read by anyone else on your Linux system.Inserting Data
After connecting to the database, you can submit SQL statements to the MySQL server to insert new
data records. Inserting data into the table is a three-step process. Listing 21.1 shows the
script2101.py program, which demonstrates this process.
LISTING 21.1 The script2101.py Program
Click here to view code image
1: #!/usr/bin/python3