Connecting MySQL to Python on Windows 10

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

1. CD your project dev folder and activate your virtual environment:

   venv\scripts\activate

2. Install PyMySQL like so:

   $ python -m pip install pymysql

Test Your Install: Make Sure Python Can Talk to MySQL

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()