This brief tutorial assumes you have set up your virtual environment and have either MySQL or an exiting WAMP install on your Windows PC.

About This Free Tutorial

This tutorial explains how to use the PyMySQL Python library. We’re made this tutorial because there is a paucity of definitive information online about integrating MySQL and Python on Windows 10.

  1. CD your project dev folder and activate your virtual environment:
    venv\scripts\activate
  2. Install PyMySQL like so:
    $ python -m pip install pymysql
  1. Create test database and table using MySQL Workbench:
    DROP DATABASE IF EXISTS `db_mysql_python`;
    CREATE DATABASE `db_mysql_python`
      DEFAULT CHARACTER SET utf8   #https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
      DEFAULT COLLATE utf8_general_ci;
    
    USE `db_mysql_python`;
    
    CREATE TABLE `tbl_users` (
      `userID`  int(11) UNSIGNED NOT NULL auto_increment,
      `email` varchar(255)  NOT NULL,
      `password` varchar(255) NOT NULL,
       
      PRIMARY KEY (`userID`)
    ) ENGINE=InnoDB;
    
  2. Open Windows PowerShell and create the following folder and file:
    $ mkdir Mysql-python-test
    
    $ cd Mysql-python-test
    
    $ touch mysql_python_test.py
    
  3. Finally, create the below script and execute:
    """
    Description:
    PyMySQL. It's pure python, so it supports all OSes equally, it's almost a
    drop-in replacement for mysqldb, and it also works with python 3. The best 
    way to install it is using pip. 
    link: http://stackoverflow.com/a/847577/946957
    
    Meta:
    C:\wamp\www\projects\python\python-course\Mysql-test\mysql_python_test.py
    link: https://github.com/PyMySQL/PyMySQL#test-suite
    """
    
    import pymysql.cursors
    
    # Connect to the database
    connection = pymysql.connect(host='localhost',
                                 user='root',
                                 password='',
                                 db='db_mysql_python',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    
    try:
        with connection.cursor() as cursor:
            # Create a new record
            sql = "INSERT INTO `tbl_users` (`email`, `password`) VALUES (%s, %s)"
            cursor.execute(sql, ('webmaster@example.com', 'very-secret'))
    
        # connection is not autocommit by default. So you must commit to save
        # your changes.
        connection.commit()
    
        with connection.cursor() as cursor:
            # Read a single record
            sql = "SELECT `id`, `password` FROM `tbl_users` WHERE `email`=%s"
            cursor.execute(sql, ('webmaster@example.com'))
            result = cursor.fetchone()
            print(result)
    finally:
        connection.close()