How to Use Python with MySQL: Install, Configure, and Perform CRUD on Windows

Introduction

This article shows how to use Python to operate a MySQL database on a Windows machine, covering installation, configuration, and basic CRUD operations.

Install MySQL (Windows)

Download mysql-5.6.33-winx64, add the bin directory to the system Path, edit my-default.ini to set basedir and datadir, then run mysqld -install and net start mysql to start the service.

Install pymysql

Open a command prompt with administrator rights and execute pip install PyMySql to install the Python MySQL driver.

Use MySQL Workbench

Download MySQL Workbench, create a new schema, and add tables via the GUI by selecting the database, right‑clicking Tables, and defining fields and data types.

Basic CRUD with Python

Example code for connecting and operating on the database:

conn = pymysql.connect(host='localhost', user='root', passwd='', db='first')
cur = conn.cursor()
# perform SQL operations with cur.execute(...)
cur.close()
conn.close()

The connection object acts as a pipeline, while the cursor is the carrier for executing SQL statements. CRUD functions simply call cur.execute() with appropriate SQL.

Conclusion

The article sets up a local MySQL database on Windows; similar steps apply to Linux or remote servers. After installation, mastering SQL and Python coding is essential for effective backend development.