Tuesday, April 7, 2015

Connecting a Python Script to a MySQL Database

With this guide, we're going to take a hands on approach to getting a Python script to read from and write to a database using the mysql-python library.

Prerequisites: Reading up on Ansible and provisioning mysql are optional, but recommended. This article was written for Mac OSX users.

Preparing Our MySQL Database

Make sure to execute these steps before you go any further. Once you have MySQL running, we'll need to create and populate our database:

# ssh into the vm
cd vagrant-ansible-mysql-python
vagrant ssh

# create a user with priveleges to access the db
mysql -uroot

# create and populate db
CREATE DATABASE test;

USE test;
CREATE TABLE `test`.`name` (`id` INT NOT NULL AUTO_INCREMENT, `firstname` VARCHAR(45) NULL, `lastname` VARCHAR(45) NULL, PRIMARY KEY (`id`));
INSERT INTO `test`.`name`(`firstname`,`lastname`) VALUES("Solid","Snake"),("Hal","Emmerich"),("Meryl","Silverburgh");

exit

Creating Our Python Script

Prepare the Environment

# create the directory and activate virtualenv
mkdir python-mysql && cd python-mysql
virtualenv venv
source venv/bin/activate

# install dependencies
pip install mysql-python

A Basic Read Script

Let's call this file namereader.py.

Execute this script by running python namereader.py. You should receive the following output:

Name is Solid Snake
Name is Hal Emmerich
Name is Meryl Silverburgh

A Basic Write Script

Let's call this file namewriter.py. We'll be using arguments passed in as the values to insert into the database. Learn more about argparse here.

Making Requests

Run the following commands:

# substitute "FirstName LastName" with...
# ...any two strings
python namewriter.py FirstName LastName

# read the db again to see the new entry
python namereader.py

That concludes this article.

No comments:

Post a Comment