Connecting Python to MySQL

Darryl See Wei Shen
May 8, 2020 · 4 min read
Image for post
Image for post

Preamble

A few weeks ago, I posted a tutorial on how to implement a simple dating app bot. I made some upgrades to it! It can handle matches in real-time, reply matches and mine text data from profiles the bot encountered!

Agenda

To query MySQL using Python.

Motivation

I have been meaning to write this article for the longest time ever. Couldn’t find the motivation to.

But…

I met this other girl on OkCupid. She’s very enthusiastic about programming and of course, a prospective Computer Science student! We were having a conversation about databases (SQL / NoSQL), I gave her a short introduction on the two types of databases, (relational/non-relational) database management systems, and brought up the topic on web-automation. Long story short, she wanted to know more and I love to teach.

She was the motivation I was looking for (to write this article).

Prerequisites and Configurations

  1. Have Python (3.X and above is recommended) and MySQL installed.
  2. Install MySQL-Connector-Python.
  3. Have an IDE installed for Python and MySQL. I am using JetBrain PyCharm Community Edition and MySQL Workbench 8.0 CE (Do all the necessary setup for MySQL).
  4. Basic knowledge in Python and SQL.

Installing MySQL-Connector-Python:

Run this command in your command line.

Walkthrough

Image for post
Image for post
Overview of Pipeline of Events

Code

Database

Start by creating a new connection in MySQL. You can name it anything you like, for this tutorial I named it ‘MediumTutorial’.

Image for post
Image for post

Next, create a database and a table with the column names etc.

Image for post
Image for post

Python

Start by importing this package.

Create a Class

For this tutorial, I am only going to create a POST method.

Code Descriptions

Image for post
Image for post
Summary of Steps

Start by creating a connection between Python and MySQL.

You’re pretty much set, start querying now.

In SQL, you write this syntax to POST data into the database.

Image for post
Image for post

Similarly, for Python, you need to store the query in a variable which will eventually be used as an argument in the following steps.

Go to MySQL Workbench, refresh the table and this is what you should see! ‘Lizard’ and 21 was successfully posted to the database!

Image for post
Image for post

The problem I ran into and my solution

Image for post
Image for post

Install the correct version of mysql-connector-python!

If you’re using PyCharm by JetBrains, do this,

A quick fix to the problem and rerun the script.

Conclusion

I hope this tutorial article is sufficed to get you started with querying MySQL using Python your pet projects. I used it to store text data I mined from OkCupid and Indeed.com, two of the many projects I am working on during lockdown (in Singapore).

PS: Lizard is the pseudonym I gave to the girl behind this article.

Source Code: https://github.com/peanutsee/OKCBOT/blob/master/mediumdb.py

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data…

Sign up for Analytics Vidhya News Bytes

By Analytics Vidhya

Latest news from Analytics Vidhya on our Hackathons and some of our best articles! Take a look

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Darryl See Wei Shen

Written by

Student | Programming | Data Analysis | Wakeboard | Nerding

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Darryl See Wei Shen

Written by

Student | Programming | Data Analysis | Wakeboard | Nerding

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store