Now that we’ve written a simple function and used variables, user input, and conditional statements, let’s connect to our MySQL database (installed previously) to store and retrieve information.
Before we start writing any code, we need to ensure that a MySQL driver is installed on our machine. We’ll do this by running the following command:
sudo apt-get install python-mysqldb
However, before we add any Python code to actually interact with MySQL, we need to create a database table. Let’s run MySQL in our terminal (you will have to enter your root MySQL password afterward):
mysql -u root -p
Now let’s create a database in MySQL named “python”:
CREATE DATABASE python;
Let’s ensure that we’re connected to our new database in the terminal:
connect python
Now let’s create a table called “users” with one field for an auto-incrementing ID and one field for a first name:
CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL
);
Your terminal should look similar to this:
Now that we have our database and tables, let’s set up our Python script so we can access and modify it.
Opening up test.py, we should add the following code to our file:
#!/usr/bin/python
import MySQLdb
# Setup MySQL Connection
db = MySQLdb.connect(host="localhost", user="root", passwd="YOURPASSWORD", db="python")
cursor = db.cursor()
# Insert a row into our table
cursor.execute("INSERT INTO users (firstname) VALUES ('Keenan')")
# Save changes to database
db.commit()
You can see that after we declare our Python path, we import the Python-MySQLdb tool that we installed earlier. This is necessary so our program can interact with our MySQL database. Our next line is creating an object named db that stores our connection to MySQL. Ensure that each field here uses your correct host, username, password, and database (although the only thing that should be changing on your end is the password).
Next, we create an object named cursor that allows us to actually execute queries in MySQL. After that, we use the cursor object we just created and run a MySQL command to insert a row into the users field. In this case, I’m inserting my first name into the firstname field. We then call db.commit() to ensure that all changes we make to our database are saved.
Now if we save our file and run the following command inside of MySQL in our terminal, we should see:
connect python
SELECT * FROM users;
This means that we have successfully inserted a row into our users table in our database. We now know that our Python application is talking to MySQL.